[
https://issues.apache.org/jira/browse/FLINK-41370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated FLINK-41370:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
>
> 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)
