[
https://issues.apache.org/jira/browse/CALCITE-7812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18118935#comment-18118935
]
Lino Rosa commented on CALCITE-7812:
------------------------------------
{quote}But I don't understand why an implicit cast is different from an
explicit cast – if you are allowed to write the explicit cast. What if the user
writes an explicit cast that the target dialect does not support?
{quote}
[~mbudiu] Ahh this question sent me spinning for a few days this week. In
principle I think you're right, but let me expand to how I arrived at my
suggestion:
h4. Regarding explicit user-written casts that compile on Calcite but not on
the target dialect:
If we were to solve that problem then I'm afraid the feature would be to allow
customizing the type inference of all sql operators to be dialect-specific.
That's a pretty large redesign. Currently type inference is dialect-agnostic.
Threading dialects into it feels like a massive task.
h4. So are we just giving up by dropping implicit casts?
Kind of... I feel validated, though, because it's something Calcite already
does - though in a much lower capacity. I'm thinking of
{{SqlImplementor#stripCastFromString}} which removes some simple implicit casts
depending on {{{}SqlDialect#supportsImplicitTypeCoercion{}}}. So in a way what
I'm suggesting is around making that feature more generalized for all implicit
casts.
So, to answer your question of ??What if the user writes an explicit cast that
the target dialect does not support??, well Calcite would compile fine and it
would fail when attempting to run it on the target dialect. Not much can be
done there. But at least the user can fix an explicit cast. They can't do
anything about an implicit cast.
h4. Arguing against myself - and maybe killing off my suggestion
Calcite will interpret {{1 + '1'}} as {{{}1 + 1{}}}, returning {{{}2{}}}. We
could imagine some dialect going the other way around, and coercing towards
{{'1' + '1'}} and returning a concatenation, {{{}11{}}}. Currently, Calcite's
explicit coercion cast is what prevents this from happening, making the
semantics coherent across dialects. Without those casts, Calcite has little
control over final semantics. The query might not even run on the dialect
because the result of that expression might not align to how it's used
elsewhere in the query...
Although it's still salvageable by the user by having them write up an explicit
CAST to what they want, it requires evaluating the query on the target engine
first and at that point damage may have been done...
---
So with all that said, ideally I think the right solution is not to drop those
implicit casts, but to make type inference dialect-aware. Like I said, I'm
afraid that feels a huge undertaking, but maybe that's the discussion we should
be having?
> Implicit type coercion emits CASTs in unparsed SQL that the target dialect
> rejects
> ----------------------------------------------------------------------------------
>
> Key: CALCITE-7812
> URL: https://issues.apache.org/jira/browse/CALCITE-7812
> Project: Calcite
> Issue Type: Bug
> Reporter: Lino Rosa
> Assignee: krooswu
> Priority: Major
>
> h2. Problem
> When type coercion is on, {{TypeCoercionImpl}} rewrites the validated
> {{SqlNode}} tree. It wraps operands and select items in {{{}CAST(x AS T){}}},
> where {{T}} is the type Calcite's own rules chose. These casts become
> ordinary {{RexCall(CAST)}} nodes after {{{}SqlToRelConverter{}}}.
> {{RelToSqlConverter}} then unparses them into the SQL sent to the target
> engine.
> That is fine only when Calcite and the engine type the expression in a
> compatible way. Otherwise it generates invalid queries.
> h4. Example: struct fields and {{SUM}} nullability on Spark
> {code:java}
> -- target: t2 (s ROW(a DOUBLE))
> -- source: t1 (k INT NOT NULL, x BIGINT NOT NULL)
> INSERT INTO t2 (s)
> SELECT ROW(SUM(x)) FROM t1 GROUP BY k {code}
> # Calcite types {{SUM}} as {{{}BIGINT NOT NULL{}}}, because {{x}} is {{NOT
> NULL}} and the query is grouped.
> # {{coerceColumnType}} wraps the row in a {{CAST}} to the target struct type.
> # {{SqlTypeUtil.convertTypeToSpec}} loses per-field nullability
> (CALCITE-6932). The unparsed cast spells the field {{{}NOT NULL{}}}.
> # Spark types {{SUM}} as nullable. It refuses to cast a struct with a
> nullable field to one with a {{NOT NULL}} field, so the query fails at
> analysis. Fixing CALCITE-6932 alone would not settle this. The cast still
> carries Calcite's view of nullability, which is correct for Calcite and wrong
> for Spark. The engine would have accepted the uncast expression.
> h4. Existing workaround is not enough
> {{SqlDialect.supportsImplicitTypeCoercion}} together with
> {{SqlImplementor.stripCastFromString}} already recognizes that engines coerce
> for themselves. Its scope is limited but its existing is promising and may
> lead to a fix.
> h3. Proposals
> h4. 1. Carry the coerced type outside the tree
> h4. The validator would record the coerced type without rewriting the tree,
> and conversion would pick it up from there. This is the cleanest option but
> hard today, because the tree is the only place coercion is stored. Much of
> the downstream logic (e.g. {{FamilyOperandTypeChecker, }}{{SqlToRelConverter)
> rely on the existence of this CAST.}}
> h4. 2. Mark coercion casts, then let the dialect decide (preferred)
> h4. Keep inserting a cast, but make it distinguishable from a user cast. Two
> ways to do that:
> * A dedicated operator of kind {{{}CAST{}}}, for example
> {{{}SqlStdOperatorTable.IMPLICIT_CAST{}}}.
> * A flag on the cast.
> The marker has to survive {{{{{}SqlToRelConverter{}}}}} and appear on the
> {{{}RexCall{}}}. Because its kind stays {{{}CAST{}}}, {{RexSimplify}} and the
> rules keep treating it as a cast.
> And here we go back to {{SqlDialect.supportsImplicitTypeCoercion.}} We can
> now simply unparse this marker as either a \{{CAST }}or not unparse anything
> depending on that flag.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
