[jira] [Created] (CALCITE-1401) SqlFunctions.arrayItem doesn't respect SQL standard when accessing array with index out of bound

["Jungtaek Lim (JIRA)" <ji...@apache.org>]

Jungtaek Lim created CALCITE-1401:
-------------------------------------

             Summary: SqlFunctions.arrayItem doesn't respect SQL standard when accessing array with index out of bound
                 Key: CALCITE-1401
                 URL: https://issues.apache.org/jira/browse/CALCITE-1401
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.9.0
            Reporter: Jungtaek Lim
            Assignee: Julian Hyde


Below is the comment from Julian:
https://github.com/apache/calcite/pull/283#issuecomment-250353662

{quote}
I have a question about testNestedArrayOutOfBoundAccess. You go with PostgreSQL's semantics (return null) rather than standard SQL (throw an exception). I'd prefer to go with the standard behavior, and return null only if enabled by a Conformance setting. Do you agree?
{quote}

and related SQL standard which Julian shared:

{code}
From SQL:2014...

6.24 <array element reference> Function

...

General Rules

1)  If the value of <array value expression> or <numeric value expression> is the null value, then the result of <array element reference> is the null value.

2)  Let i be the value of <numeric value expression>. Case:

a)  If i is greater than zero and less than or equal to the cardinality of <array value expression>, then the result of <array element reference> is the value of the i-th element of the value of <array value expression>.

b)  Otherwise, an exception condition is raised: data exception — array element error.
{code}

Based on above standard, exception condition is raised when we access the index which is out of bound. But current implementation of SqlFunctions.arrayItem() does bound check and return null when index is out of bound.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)