[
https://issues.apache.org/jira/browse/CALCITE-7812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18118123#comment-18118123
]
Lino Rosa commented on CALCITE-7812:
------------------------------------
Sure, let's start with the unvalidated query `String`:
{code:java}
INSERT INTO target_table ("s")
SELECT NAMED_STRUCT('a', SUM("x")) AS "s"
FROM source_table{code}
Let go and assume Calcite has to coerce the type of "s", which we know can
happen for a number of reasons. We'd then have a query plan somewhat like this:
{code:java}
LogicalTableModify(table=[[company_data, _narrative_modifiable_target]],
operation=[INSERT], flattened=[false])
LogicalProject(s=[CAST(NAMED_STRUCT('a', $1)):RecordType(DOUBLE NOT NULL a)])
{code}
When unparsed to Spark, it would be:
{code:java}
INSERT INTO ... (`s`)
SELECT CAST(NAMED_STRUCT('a', SUM(`x`)) AS STRUCT<`a`: DOUBLE NOT NULL>) `s`
FROM ...{code}
... which doesn't work for the reasons explained before (`SUM` is nullable in
Spark).
h4. What I'd like to happen instead
First, that somehow that `CAST` was marked as a type coercion cast (as opposed
to a cast a user typed in):
{code:java}
LogicalTableModify(table=[[company_data, _narrative_modifiable_target]],
operation=[INSERT], flattened=[false])
LogicalProject(s=[TYPE_COERCION_CAST(NAMED_STRUCT('a', $1)):RecordType(DOUBLE
NOT NULL a)]) {code}
... It doesn't have to be a new node type like above with `TYPE_COERCION_CAST`.
It could be a flag on the existing cast.
Then, because we'd set SqlDialect.supportsImplicitTypeCoercion to `true` on
Spark, the unparsed query would drop the type coercion cast and unparse:
{code:java}
INSERT INTO ... (`s`)
SELECT NAMED_STRUCT('a', SUM(`x`)) `s`
FROM ... {code}
... we'd let Spark take care of type coercion because of
`SqlDialect.supportsImplicitTypeCoercion`.
h4. How I justify this in my head
Like you said, we can't make the semantics of Calcite match that of every
dialect, but that's not what I'm suggesting. Calcite should maintain its
existing validation semantics, but just open the door for dialects to
ultimately dictate whether they want to inherit Calcite's type coercion or not.
> 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
> 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)
