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

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(...);
  

  Copy

Ref: 1354