INVALID_ARRAY_INDEX error class

SQLSTATE: 22003

The index <indexValue> is out of bounds. The array has <arraySize> elements. Use the SQL function get() to tolerate accessing element at invalid index and return NULL instead. If necessary set <ansiConfig> to "false" to bypass this error.

Parameters

• indexValue: The requested index into the array.
• arraySize: The cardinality of the array.
• ansiConfig: The configuration setting to alter ANSI mode.

Explanation

Unlike element_at and elt, a reference indexValue into an array using the arrayExpr[indexValue] syntax must be between 0 for the first element and arraySize - 1 for the last element.

A negative indexValue or a value greater or equal to arraySize is not allowed.

Mitigation

The mitigation for this error depends on the intent:

• Does the provided indexValue assume 1-based indexing?

  Use element_at(arrayExpr, indexValue), elt(arrayExpr, indexValue)`, or arrayExpr[indexValue - 1] to resolve the correct array element.

• Is the indexValue negative expecting to retrieve element relative to the end of the array?

  Use element_at(arrayExpr, indexValue) or elt(arrayExpr, indexValue)`. Adjust for 1-based indexing if necessary.

• Do you expect to get a NULL value to be returned for elements outside the cardinality of the index?

  If you can change the expression, use try_element_at(arrayExpr, indexValue + 1) to tolerate references out of bound. Note the 1-based indexing for try_element_at.

  If you cannot change the expression, as a last resort, temporarily set the ansiConfig to false to tolerate references out of bound.

Examples

-- An INVALID_ARRAY_INDEX error because of mismatched indexing
> SELECT array('a', 'b', 'c')[index] FROM VALUES(1), (3) AS T(index);
  [INVALID_ARRAY_INDEX] The index 3 is out of bounds. The array has 3 elements. If necessary set "ANSI_MODE" to false to bypass this error.

-- Using element_at instead for 1-based indexing
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (3) AS T(index);
  a
  c

-- Adjusting the index to be 0-based
> SELECT array('a', 'b', 'c')[index -1] FROM VALUES(1), (3) AS T(index);

-- Tolerating out of bound array index with adjustment to 1-based indexing
> SELECT try_element_at(array('a', 'b', 'c'), index + 1) FROM VALUES(1), (3) AS T(index);
  b
  NULL

-- An INVALID_ARRAY_INDEX error because of negative index
> SELECT array('a', 'b', 'c')[index] FROM VALUES(-1), (2) AS T(index);
  [INVALID_ARRAY_INDEX] The index -1 is out of bounds. The array has 3 elements. If necessary set "ANSI_MODE" to "false" to bypass this error.

-- Using element_at to index relative to the end of the array
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(-1), (2) AS T(index);
  c
  b

-- Tolerating an out of bound index by setting ansiConfig in Databricks SQL
> SET ANSI_MODE = false;
> SELECT array('a', 'b', 'c')[index] FROM VALUES(1), (3) AS T(index);
  b
  NULL
> SET ANSI_MODE = true;

-- Tolerating an out of bound index by setting ansiConfig in Databricks Runtime
> SET spark.sql.ansi.enabled = false;
> SELECT array('a', 'b', 'c')[index] FROM VALUES(1), (3) AS T(index);
  b
  NULL
> SET spark.sql.ansi.enabled = true;

Related

• [ ] (bracket sign) operator
• ANSI_MODE
• element_at function
• elt function
• try_element_at function