Yaoxuan Wu created FLINK-41563:
----------------------------------
Summary: NULL <> MAP / ARRAY / ROW is NULL when projected but TRUE
in a WHERE condition and in CASE WHEN (wrapExpressionIfNonEq keeps the null
term)
Key: FLINK-41563
URL: https://issues.apache.org/jira/browse/FLINK-41563
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Yaoxuan Wu
When a MAP, ARRAY or ROW operand is NULL, {{a <> b}} is NULL when it is
projected, but it counts as TRUE in a WHERE condition and in CASE WHEN. Rows
with a NULL collection therefore pass a {{<>}} filter. No error is raised.
{code:sql}
-- t(id INT, m MAP<STRING, STRING>, a ARRAY<INT>, r ROW<f INT>, i INT)
-- (1, {'x': 'y'}, [1], (1), 5)
-- (2, NULL, NULL, NULL, NULL)
-- filesystem / json source; a VALUES table is reduced at plan time and comes
out right
SELECT id, m <> MAP['a', 'b'] FROM t;
-- (1, TRUE), (2, NULL) correct
SELECT id FROM t WHERE m <> MAP['a', 'b'];
-- expected: 1 actual: 1, 2
SELECT id FROM t WHERE (m <> MAP['a', 'b']) IS TRUE;
-- expected: 1 actual: 1, 2
SELECT id FROM t WHERE NOT (m = MAP['a', 'b']);
-- expected: 1 actual: 1, 2
SELECT id FROM t WHERE a <> ARRAY[2];
-- expected: 1 actual: 1, 2
SELECT id FROM t WHERE r <> ROW(2);
-- expected: 1 actual: 1, 2
SELECT id, CASE WHEN m <> MAP['a', 'b'] THEN 'T' ELSE 'F' END FROM t;
-- expected: (1, 'T'), (2, 'F') actual: (1, 'T'), (2, 'T')
SELECT id FROM t WHERE i <> 3; -- INT: correct (1)
SELECT id FROM t WHERE m <> MAP['a', 'b'] AND id > 0; -- inside AND: correct
(1)
{code}
* Same on 2.0.1, 2.2.1, 2.3.0 and master (2.4-SNAPSHOT), in batch and streaming
mode.
* The same with the Table API ({{col('m') != map_(...)}}) and with a column on
both sides.
Cause (ScalarOperatorGens.scala):
* {{generateEqualAndNonEqual}} builds {{<>}} for ARRAY, MAP, MULTISET and ROW
operands as {{wrapExpressionIfNonEq(nonEq, generateArrayComparison /
generateMapComparison / generateRowComparison(...))}}.
* The comparisons use {{generateCallWithStmtIfArgsNotNull}}: with a NULL
operand, the null term is true and the result term keeps its default value
{{false}}.
* {{wrapExpressionIfNonEq}} returns
{{GeneratedExpression(s"(!${equalsExpr.resultTerm})", equalsExpr.nullTerm,
...)}}, so the result term becomes {{true}} while the null term says NULL.
* Code that reads only the result term of a boolean and relies on a NULL
boolean's result term being false (the Calc filter condition, CASE WHEN) then
treats {{NULL <> x}} as TRUE. A projection reads the null term and prints NULL;
AND / OR check the null terms, so the condition is right inside them. {{IS
TRUE}} and {{NOT (a = b)}} are simplified to the bare {{<>}} first.
A possible fix: {{s"(!${equalsExpr.nullTerm} && !${equalsExpr.resultTerm})"}}
in {{wrapExpressionIfNonEq}}.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)