Ramin Gharib created FLINK-41438:
------------------------------------
Summary: ARRAY, MAP and ROW element access returns -1 instead of
NULL when the element type is NOT NULL
Key: FLINK-41438
URL: https://issues.apache.org/jira/browse/FLINK-41438
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Ramin Gharib
Assignee: Ramin Gharib
Reading an element that doesn't exist returns a made-up value instead of NULL
when the element type is NOT NULL. This covers an ARRAY index past the end, a
missing MAP key, ELEMENT on an empty array, and any of these on a NULL
container. Reproduced on master, identical in batch and streaming mode:
{code:sql}
SELECT ARRAY[10,20,30][4], ARRAY[10,20,30][4] IS NULL;
SELECT x FROM (VALUES (2), (4)) AS t(x) WHERE ARRAY[10,20,30][x] IS NULL;
SELECT MAP['a', 1]['b'];
SELECT ELEMENT(ARRAY_REMOVE(ARRAY[1], 1));
CREATE TABLE src (r ROW<f INT NOT NULL>) WITH (
'connector' = 'datagen', 'number-of-rows' = '1', 'fields.r.null-rate' =
'1.0');
SELECT r['f'], r.f FROM src;
{code}
||Expression||Expected||Actual||
|{{ARRAY\[10,20,30\]\[4\]}}|NULL|-1|
|{{ARRAY\[10,20,30\]\[4\] IS NULL}}|TRUE|FALSE|
|{{WHERE ARRAY\[10,20,30\]\[x\] IS NULL}} over x = 2, 4|row 4|no rows|
|{{MAP\['a', 1\]\['b'\]}}|NULL|-1|
|{{ELEMENT(ARRAY_REMOVE(ARRAY\[1\], 1))}}|NULL|-1|
|{{r\['f'\]}} on a NULL {{ROW<f INT NOT NULL>}}|NULL|-1|
|{{r.f}} on the same row|NULL|NULL|
h3. Cause
The planner types all of these results as nullable, so
{{TYPEOF(ARRAY\[10,20,30\]\[4\])}} is {{INT}}. {{generateArrayElementAt}},
{{generateArrayElement}}, {{generateMapGet}} and {{generateDot}} in
{{ScalarOperatorGens}} set the null term correctly but return the element or
field type, here {{INT NOT NULL}}. Consumers that trust the type drop the null
term: {{CodeGenUtils.rowSetField}} skips {{setNullAt}} for a NOT NULL field of
a {{BoxedWrapperRowData}} or {{GenericRowData}}, which covers the Calc output
and constant folding, and {{generateIsNull}} folds to {{false}}. The value
written is the type's placeholder from {{primitiveDefaultValue}}, {{-1}} for
INT and an empty string for STRING.
FLINK-34656 reports the NULL-array case of the same cause.
{{ArrayTypeTest#testReturnNullWhenArrayIndexOutOfBounds}} asserts NULL for
{{f2\[4\]}} on an {{ARRAY<INT NOT NULL>}} and passes, because
{{ExpressionTestBase}} casts every expression to VARCHAR and writes a
{{BinaryRowData}}, and both read the null term.
PostgreSQL, Calcite, Spark with ANSI off, DuckDB, Hive, CockroachDB and
StarRocks all return NULL. The SQL:1999 standard raises an error.
h3. Proposal
Pass the call's result type into the four generators, as
{{generateArrayCardinality}} already does, so the generated expression carries
the type the planner inferred. Queries that return the placeholder today return
NULL afterwards.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
