[ 
https://issues.apache.org/jira/browse/FLINK-41563?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated FLINK-41563:
-----------------------------------
    Labels: correctness pull-request-available  (was: correctness)

> NULL <> MAP / ARRAY / ROW counts as TRUE in WHERE and CASE WHEN
> ---------------------------------------------------------------
>
>                 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
>            Assignee: Ramin Gharib
>            Priority: Major
>              Labels: correctness, pull-request-available
>
> 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