INVALID_ARRAY_INDEX_IN_ELEMENT_AT error class
The index <indexValue>
is out of bounds. The array has <arraySize>
elements. Use try_element_at
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
indexValue
is beyond the boundary of defined array elements for an element_at(arrayExpr, indexValue), or elt(arrayExpr, indexValue) expression.
The value must be between -arraySize
and arraySize
, excluding 0
.
Mitigation
The mitigation for this error depends on the cause:
Is the cardinality of the array smaller than expected?
Fix the input array and re-run the query.
Has
indexValue
been computed incorrectly?Adjust
indexValue
and re-run the query.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) to tolerate references out of bound.
If you cannot change the expression, as a last resort, temporarily set the
ansiConfig
tofalse
to tolerate references out of bound.
Examples
-- An INVALID_ARRAY_INDEX_IN_ELEMENT_AT error because of mismatched indexing
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (4) AS T(index);
[INVALID_ARRAY_INDEX_IN_ELEMENT_AT] The index 4 is out of bounds. The array has 3 elements. If necessary set "ANSI_MODE" to false to bypass this error.
-- Increase the aray size to cover the index
> SELECT element_at(array('a', 'b', 'c', 'd'), index) FROM VALUES(1), (4) AS T(index);
a
d
-- Adjusting the index to match the array
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (3) AS T(index);
a
c
-- Tolerating out of bound array index with adjustment to 1-based indexing
> SELECT try_element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (4) AS T(index);
a
NULL
-- Tolerating out of bound by setting ansiConfig in Databricks SQL
> SET ANSI_MODE = false;
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (4) AS T(index);
a
NULL
> SET ANSI_MODE = true;
-- Tolerating out of bound by setting ansiConfig in Databricks Runtime
> SET spark.sql.ansi.enabled = false;
> SELECT element_at(array('a', 'b', 'c'), index) FROM VALUES(1), (4) AS T(index);
a
NULL
> SET spark.sql.ansi.enabled = true;
Related
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru