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)