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)

Reply via email to