[
https://issues.apache.org/jira/browse/FLINK-41563?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ramin Gharib updated FLINK-41563:
---------------------------------
Summary: NULL <> MAP / ARRAY / ROW counts as TRUE in WHERE and CASE WHEN
(was: NULL <> MAP / ARRAY / ROW is NULL when projected but TRUE in a WHERE
condition and in CASE WHEN (wrapExpressionIfNonEq keeps the null term))
> 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
>
> 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)