[
https://issues.apache.org/jira/browse/FLINK-41405?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated FLINK-41405:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Minor
> Labels: pull-request-available
>
> 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)
