Ramin Gharib created FLINK-41406:
------------------------------------
Summary: VARIANT mixed with another type in UNION ALL, CASE or
VALUES is cast implicitly
Key: FLINK-41406
URL: https://issues.apache.org/jira/browse/FLINK-41406
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.4.0
Reporter: Ramin Gharib
Assignee: Ramin Gharib
A {{VARIANT}} is never cast implicitly, so it should have no common type with
any other type. On master, a {{VARIANT}} that meets another type in {{UNION
ALL}}, {{CASE}} or {{VALUES}} gets one anyway. The result type depends on the
order of the inputs, and the other inputs are cast to it without an explicit
{{CAST}}.
h3. Reproducer
{{t}} stands for {{(VALUES (PARSE_JSON('1')), (PARSE_JSON('"x"'))) AS t(v)}}.
{code:sql}
SELECT v FROM t UNION ALL SELECT 'abc';
SELECT 'abc' UNION ALL SELECT v FROM t;
SELECT CASE WHEN v IS NOT NULL THEN v ELSE 1 END FROM t;
SELECT x FROM (VALUES (PARSE_JSON('1')), ('abc')) AS u(x);
SELECT ARRAY[v, 'abc'] FROM t;
{code}
||Statement||Actual||Expected||
|{{v UNION ALL 'abc'}}|type {{CHAR(3)}}, rows {{'1 '}}, {{'x '}},
{{'abc'}}|{{Type mismatch in column 1 of UNION ALL}}|
|{{'abc' UNION ALL v}}|type {{VARIANT}}, rows {{"abc"}}, {{1}}, {{"x"}}|{{Type
mismatch in column 1 of UNION ALL}}|
|{{CASE ... THEN v ELSE 1 END}}|type {{INT}}, the job fails on {{"x"}}:
{{Cannot cast a VARIANT STRING value to INTEGER.}}|{{Illegal mixing of types in
CASE or COALESCE statement}}|
|{{CASE ... THEN 1 ELSE v END}}|type {{VARIANT}}, rows {{1}}, {{"x"}}|{{Illegal
mixing of types in CASE or COALESCE statement}}|
|{{VALUES (PARSE_JSON('1')), ('abc')}}|type {{CHAR(3)}}, rows {{'1 '}},
{{'abc'}}|{{Values passed to VALUES operator must have compatible types}}|
|{{ARRAY\[v, 'abc'\]}}|{{AssertionError: Conversion to relational algebra
failed to preserve datatypes}}|{{Parameters must be of the same type}}|
|{{MAP\['k', v, 'l', 2\]}}|same {{AssertionError}}|{{Parameters must be of the
same type}}|
The expected messages are the ones Flink already gives for other incompatible
types, e.g. with {{ARRAY\[TRUE\]}} in place of the {{VARIANT}}.
The same happens inside constructed types:
{{FlinkTypeFactory.leastRestrictive}} of {{ARRAY<VARIANT>}} and {{ARRAY<INT>}}
returns {{ARRAY<INT>}}, and likewise for {{MAP}} values and {{ROW}} fields.
h3. Cause
Since the Calcite upgrade in FLINK-39817, the copied
{{SqlTypeUtil.canCastFrom}} returns true whenever either side is a {{VARIANT}}.
{{SqlTypeFactoryImpl.leastRestrictiveByCast}} then keeps the last input type,
and {{FlinkTypeFactory.leastRestrictive}} returns it. For {{ARRAY}},
{{MULTISET}}, {{MAP}} and {{ROW}} the cast check recurses into the elements, so
a {{VARIANT}} element is castable there too. release-2.4 has the same code. On
2.3.0 the shortcut is missing, so the same statements most likely fail with an
internal {{AssertionError}} instead (from reading the code, not run).
h3. Proposal
{{FlinkTypeFactory.leastRestrictive}} returns {{null}} when a {{VARIANT}} meets
any type other than {{VARIANT}} or an untyped {{NULL}}. Calcite then reports
its usual type mismatch errors. The check also looks inside {{ARRAY}},
{{MULTISET}}, {{MAP}} and {{ROW}}, because Calcite falls back to the cast check
for the whole type when the elements have no common type. {{VARIANT}} with
{{VARIANT}} and with {{NULL}} keep working. A query that mixes types casts
explicitly, e.g. {{CAST(s AS VARIANT)}} or {{CAST(v AS STRING)}}.
2.4.0 is not released yet, so a backport keeps the implicit cast out of every
release.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
