New SQL functions: GREATEST_IGNORE_NULLS and LEAST_IGNORE_NULLS may conflict with similarly named UDFs

Attention

This behavior change is in the 2024_03 bundle.

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

Snowflake is introducing two new built-in functions:

  • GREATEST_IGNORE_NULLS: Returns the largest non-NULL value from a list of expressions. If all of the argument values are NULLs, the result is NULL.

    GREATEST_IGNORE_NULLS supports arguments of all data types, including VARIANT.

  • LEAST_IGNORE_NULLS: Returns the smallest non-NULL value from a list of expressions. If all of the argument values are NULLs, the result is NULL.

    LEAST_IGNORE_NULLS supports arguments of all data types, including VARIANT.

If you have a user-defined function (UDF) named GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS, calls to your function have the following effect:

Before the change:

A call to your UDF named GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS resolves to your UDF.

After the change:

A call to your UDF named GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS resolves to the new built-in GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS function. The built-in GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS function might work differently than your UDF.

If the documented semantics of the new built-in GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS function does not match the semantics of your UDF, you can either:

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

SELECT my_database.my_schema.least_ignore_nulls(...);

Ref: 1354