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