New Functions: ARRAY_SORT, ARRAY_MIN, and ARRAY_MAX May Conflict With Similarly Named UDFs

Attention

This behavior change is in the 2023_05 bundle.

For the current status of the bundle, refer to Bundle History.

In the current release, Snowflake introduces new built-in functions with the following names and signatures:

  • ARRAY_SORT returns the elements of the input ARRAY in sorted order. This function has the following signatures:

    • ARRAY_SORT(input_array)

      Sorts the elements in ascending order with NULLs placed at the end of the array.

    • ARRAY_SORT(input_array, sort_ascending)

      Sorts the elements in ascending order if sort_ascending is TRUE or in descending order if sort_ascending is FALSE.

      NULLs are sorted last if sort_ascending is TRUE or first if sort_ascending is FALSE.

    • ARRAY_SORT(input_array, sort_ascending, nulls_first)

      Sorts the elements in ascending order if sort_ascending is TRUE or in descending order if sort_ascending is FALSE.

      NULLs are sorted first if nulls_first is TRUE or last if nulls_first is FALSE.

    This function is not guaranteed to provide a stable sort when comparing values of two different numeric or timestamp types (or objects containing these types).

  • ARRAY_MIN returns the minimum defined element in the input array

  • ARRAY_MAX returns the maximum defined element in the input array

If you have UDFs named ARRAY_SORT, ARRAY_MIN, or ARRAY_MAX with the same signatures, this behavior change has the following effect:

Previously:

Calls to ARRAY_SORT, ARRAY_MIN, or ARRAY_MAX resolve to your UDFs.

Currently:

Calls to ARRAY_SORT, ARRAY_MIN, or ARRAY_MAX will resolve to the new built-in functions.

The built-in functions might work differently than your UDFs.

If the documented semantics of the new built-in functions do not match the semantics of your UDFs, you can either:

  • Rename your UDFs (using ALTER FUNCTION … RENAME TO …), and replace all references to the original UDF name with the new name.
  • Fully qualify all references to your UDFs by specifying the names of the database and schema containing the UDFs. For example: 
    SELECT my_database.my_schema.array_sort(...);

Ref: 1135