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)
