नोट
इस पृष्ठ तक पहुंच के लिए प्राधिकरण की आवश्यकता होती है। आप साइन इन करने या निर्देशिकाएँ बदलने का प्रयास कर सकते हैं।
इस पृष्ठ तक पहुंच के लिए प्राधिकरण की आवश्यकता होती है। आप निर्देशिकाएँ बदलने का प्रयास कर सकते हैं।
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
indexValueassume 1-based indexing?Use element_at(arrayExpr, indexValue), elt(arrayExpr, indexValue)`, or arrayExpr[indexValue - 1] to resolve the correct array element.
Is the
indexValuenegative 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
NULLvalue 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
ansiConfigtofalseto 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;