ARRAY_POSITION Function: Changes to Finding the Position of a NULL Value¶

When you call the ARRAY_POSITION function and pass in a value as the first argument, the function returns the position of the first ARRAY element with that value.

The ARRAY_POSITION function has changed when you specify NULL as the first argument:

Previously:

The function returned NULL. For example:

SELECT ARRAY_POSITION(NULL, [10, NULL, 30]);

+--------------------------------------+
| ARRAY_POSITION(NULL, [10, NULL, 30]) |
|--------------------------------------|
|                                 NULL |
+--------------------------------------+

Copy

Currently:

The function returns the position of the first NULL in the ARRAY. For example:

SELECT ARRAY_POSITION(NULL, [10, NULL, 30]);
+--------------------------------------+
| ARRAY_POSITION(NULL, [10, NULL, 30]) |
|--------------------------------------|
|                                    1 |
+--------------------------------------+

Copy

This change was implemented for consistency with the ARRAY_CONTAINS function. When you use the ARRAY_CONTAINS function to determine if an ARRAY contains NULL, the function returns TRUE.

Ref: 882