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
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για