[CALCITE-5660] Implement BigQuery Array Subscript Operators

• Type: Task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Tanner Clary
• Reporter: Tanner Clary
• Created: 18/Apr/23 23:52
• Updated: 27/Jul/23 10:22
• Resolved: 10/May/23 01:28

Description

When indexing an array in BigQuery, you are required to wrap the index in one of four position keywords: OFFSET, ORDINAL, SAFE_OFFSET, and SAFE_ORDINAL.

OFFSET(index): The index starts at zero. Produces an error if the index is out of range.
SAFE_OFFSET(index): The index starts at zero. Returns NULL if the index is out of range.
ORDINAL(index): The index starts at one. Produces an error if the index is out of range.
SAFE_ORDINAL(index): The index starts at one. Returns NULL if the index is out of range.

These operators are very similar to the existing ITEM operator currently used to index an array in Calcite. I believe the offset and out of bounds behavior for the existing implementation could be parameterized to support the above operators.

Link to BigQuery docs

I will have a PR open for this in the next day or two, in the meantime any comments, questions, or suggestions are welcome as always.

Issue Links

• links to: GitHub Pull Request #3167