Ramin Gharib created FLINK-41405:
------------------------------------
Summary: Comparing a VARIANT expression with itself bypasses the
no-equality check
Key: FLINK-41405
URL: https://issues.apache.org/jira/browse/FLINK-41405
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.4.0
Reporter: Ramin Gharib
Assignee: Ramin Gharib
FLINK-40910 rejects VARIANT where a query needs equality or order. A comparison
of an expression with itself slips through and returns a result instead:
{code:sql}
SELECT PARSE_JSON('1') = PARSE_JSON('1'); -- TRUE
SELECT PARSE_JSON('1') = PARSE_JSON('2'); -- rejected: the type has no equality
{code}
With {{CREATE VIEW t AS SELECT PARSE_JSON(s) AS v FROM (VALUES ('1'),
(CAST(NULL AS STRING))) AS x(s)}}:
||Query||Expected||Actual||
|{{SELECT PARSE_JSON('1') = PARSE_JSON('1')}}|"has no equality" error|{{TRUE}}|
|{{SELECT v <> v FROM t}}|"has no equality" error|{{FALSE}}, {{NULL}}|
|{{SELECT v < v FROM t}}|"has no order" error|{{FALSE}}, {{NULL}}|
|{{SELECT v IS NOT DISTINCT FROM v FROM t}}|"has no equality" error|{{TRUE}},
{{TRUE}}|
|{{SELECT v IN (v) FROM t}}|"has no equality" error|{{TRUE}}, {{NULL}}|
|{{SELECT v BETWEEN v AND v FROM t}}|"has no order" error|{{TRUE}}, {{NULL}}|
|{{SELECT CASE v WHEN v THEN 1 ELSE 0 END FROM t}}|"has no equality"
error|{{1}}, {{0}}|
|{{SELECT NULLIF(v, v) FROM t}}|"has no equality" error|{{NULL}}, {{NULL}}|
|{{SELECT COUNT(*) FROM t a JOIN t b ON a.v = a.v}}|"has no equality"
error|{{2}}|
|{{SELECT COUNT(*) FROM t WHERE v = v}}|"has no equality" error|"has no
equality" error|
*Cause*
{{ComparableTypeValidator}} runs on the logical plan before optimization.
{{SqlToRelConverter}} builds projections and join conditions with
{{RelBuilder}}, which simplifies them with {{RexSimplify}}. {{RexSimplify}}
rewrites a deterministic {{x = x}} to {{NULL OR x IS NOT NULL}}, and {{x <>
x}}, {{x < x}} and {{x > x}} to {{NULL AND x IS NULL}}. The comparison is gone
before the check runs:
{noformat}
EXPLAIN SELECT v = v, v <> v FROM t
LogicalProject(EXPR$0=[OR(null, IS NOT NULL($0))], EXPR$1=[AND(null, IS
NULL($0))])
{noformat}
WHERE is not affected, because {{SqlToRelConverter}} creates the filter without
{{RelBuilder}}. The Table API is not affected, because the input type strategy
of {{isEqual}} and the other comparisons already rejects VARIANT.
The results are what any reflexive equality would return, but the same
comparison is accepted or rejected depending only on how it is written.
*Proposal*
Also check comparison operators ({{=}}, {{<>}}, {{<}}, {{<=}}, {{>}}, {{>=}},
{{IS [NOT] DISTINCT FROM}}, {{[NOT] BETWEEN}}, {{[NOT] IN}}) on the validated
SQL call in {{FlinkCalciteSqlValidator}}, before the conversion, with the same
rule and message as {{ComparableTypeValidator}}. No Calcite change is needed. A
VARIANT comparison in a computed column, a watermark or a view then fails when
it is created instead of when it is queried.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
