[SPARK-46374] Array Indexing is 1-based via ANSI SQL Standard

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0, 3.5.1, 3.4.3
• Component/s: SQL
• External issue ID: SPARK-28402
• Assignee: Unassigned
• Reporter: Kent Yao
• Created: 12/Dec/23 03:30
• Updated: 12/Apr/24 02:41

Description

SPARK-28402 was closed by the reporter, because he might think it's a postgres specific feature.

 

<element reference> ::=
    <array value expression>
       <left bracket or trigraph> <numeric value expression> <right bracket or trigraph>
  
1) If the value of  or  is the null value, then
the result of  is the null value.
2) Let the value of  be i.
Case:
a) If i is greater than zero and less than or equal to the cardinality of ,
then the result of  is the value of the i-th element of the value of .

 

The numeric value expression in the range of greater than zero and less than or equal to the cardinality a.k.a., [1, size(array)]

Who supports?(Incomplete)

• postgres
• Oracle
• trino/presto
• MySQL

Who doesn't support?(Incomplete)

• hive
• spark sql (ANSI off/on)

For ANSI compliance, we shall make array indexing 1-based when spark.sql.ansi.enabled=true

Issue Links

• is related to: SPARK-28402 Array indexing is 1-based
• is related to: SPARK-44444 Use ANSI SQL mode by default