Ramin Gharib created FLINK-41370:
------------------------------------
Summary: VARIANT array access in SQL returns NULL for a literal
index below 1 instead of failing
Key: FLINK-41370
URL: https://issues.apache.org/jira/browse/FLINK-41370
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Ramin Gharib
Assignee: Ramin Gharib
SQL array access on a VARIANT returns NULL for a literal index below 1. The
same literal fails during planning for a plain ARRAY, and for a VARIANT in the
Table API.
{code:sql}
SELECT v[0] AS a,
v[-1] AS b
FROM (VALUES (PARSE_JSON('[10,20,30]'))) AS t(v);
{code}
||Expression||Expected||Actual||
|{{v\[0\]}}|{{Array element access needs an index starting at 1 but was
0.}}|NULL|
|{{v\[-1\]}}|{{Array element access needs an index starting at 1 but was
-1.}}|NULL|
|{{ARRAY\[10,20,30\]\[0\]}}|{{Array element access needs an index starting at 1
but was 0.}}|as expected|
|{{$("v").at(0)}}|{{The provided index must be a valid SQL index starting from
1, but was '0'}}|as expected|
The index is 1-based, so a literal below 1 can never address an element.
{{ScalarOperatorGens.generateArrayElementAt}} rejects such a literal for ARRAY,
and {{ItemAtIndexArgumentTypeStrategy}} rejects it for {{VARIANT.at(INT)}}. SQL
{{ITEM}} on a VARIANT goes through {{ScalarOperatorGens.generateVariantGet}},
which has no such check.
Other engines with a 1-based VARIANT index reject index 0 too. DuckDB fails
binding with {{Extracting index 0 from VARIANT(ARRAY) is invalid, indexes are
1-based}}, and Trino fails for index 0 and for negative indexes.
*Proposal*
Apply the ARRAY literal check in {{generateVariantGet}} for every integer index
type, so {{v\[CAST(0 AS BIGINT)\]}} fails as well. A computed index below 1
keeps returning NULL, as it does for ARRAY. Such a literal returns NULL for
every row today, so failing during planning loses no results.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
