How to use `GREATEST()` in Snowflake with null values?

Question

I'm trying to use GREATEST() in Snowflake, but whenever I have null values, I get null instead of the desired result:

select greatest(1,2,null);

-- null

This behavior has confused many, and it begins with the behavior of GREATEST() in Oracle, which Snowflake matches:

• Handling Null in Greatest function in Oracle

It has also being discussed in the Snowflake forums:

• https://community.snowflake.com/s/question/0D50Z00009LHFw1SAH/greatest-and-null-values

Sample data:

create or replace table some_nulls
as (
    select $1 a, $2 b, $3 c
    from values(1.1, 2.3, null::float), (null, 2, 3.5), (1, null, 3), (null, null, null)
);

select greatest(a, b)
from some_nulls;

Asking here to get the best available solution.

Answer 1

It is possible to handle nullable columns with ARRAYs:

SELECT a,b,c, GREATEST([a],[b],[c])[0]::INT
FROM some_nulls;

---

For sample data:

CREATE OR REPLACE TABLE some_nulls(a INT, b INT, c INT)
AS
SELECT 1, 2, NULL UNION
SELECT NULL, 2, 3 UNION
SELECT 1, NULL, 3 UNION
SELECT NULL, NULL, NULL;

Output:

---

How it works:

NULL becomes [undefined] which is the lowest element, therefore this approach could be used for GREATEST but NOT for LEAST function.

EDIT: As per Denis' comment: -(GREATEST( [-a] ,[-b], [-c] )[0]::INT) finds LEAST value

---

EDIT: Much cleaner approach is possible with recently introduced ARRAY_MIN/MAX functions: https://stackoverflow.com/a/76689938/5070879

Answer 2

GREATEST and LEAST with IGNORE NULLS could be emulated by ARRAY_MIN and ARRAY_MAX functions:

SELECT GREATEST(1,2,NULL), LEAST(1,2,NULL), 
       ARRAY_MAX([1,2,NULL]), ARRAY_MIN([1,2,NULL]);
-- NULL NULL 2 1

Output:

Returned output has VARIANT data type and it should be casted explicitly: ARRAY_MAX([...])::NUMBER.

Function	Behavior equivalent
GREATEST(...)	GREATEST(...) RESPECT NULLS
LEAST(...)	LEAST(...) RESPECT NULLS
ARRAY_MAX(...)	GREATEST(...) IGNORE NULLS
ARRAY_MIN(...)	LEAST(...) IGNORE NULLS

---

This approach works also for columns:

CREATE OR REPLACE TABLE tab(col1, col2, col3) AS
SELECT 1 ,2, NULL
UNION ALL SELECT 10, 20, 30;

SELECT *,
       GREATEST(col1, col2, col3),
       ARRAY_MAX([col1, col2, col3])::NUMBER
FROM tab;

---

These functions are present but not enabled by default:

SHOW FUNCTIONS LIKE 'ARRAY_M%';

ARRAY_MAX(ARRAY) RETURN VARIANT the maximum defined element in the array, or NULL
ARRAY_MIN(ARRAY) RETURN VARIANT the minimum defined element in the array, or NULL

2023_05 Bundle

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2023_05');

Status changed in 7.29 release (August 22-23) to Enabled by Default; account admins can disable for opt-out.

Answer 3

One solution could be to create a UDF that picks the greatest() or the first non null:

create or replace function greatest2(x1 float, x2 float)
returns float
as $$
    coalesce(greatest(x1, x2), x1, x2)
$$;

select greatest2(a, b)
from some_nulls;

However things get more complex if you need to compare multiple values. For example, if you want to compare 3 columns, then you have to create a custom UDF with 3 arguments and check each for null:

create or replace function greatest3(x1 float, x2 float, x3 float)
returns float
as $$
    select iff(x='-inf', null, x)
    from (
        select greatest(nvl(x1, '-inf'), nvl(x2, '-inf'), nvl(x3, '-inf')) x
    )
$$;

select greatest3(a, b, c)
from some_nulls;

Answer 4

Documenting here an approach that doesn't work (to save others time, or an opportunity to fix): Arrays in a SQL UDF.

create or replace function greatest_a(arr array)
returns float
immutable
as $$
    select max(value::float)
    from table(flatten(arr))
$$;

select greatest_a([null,2,3.3])
from some_nulls;

This works until you try to create the array with values from the table.

select greatest_a([a, b, c])
from some_nulls;

-- Unsupported subquery type cannot be evaluated

A similar approach with a JS UDF would work, but it will be slower than a pure SQL UDF.

Answer 5

Welcome user: Felipe

The doc's seem to describe the results exactly:

Returns the largest value from a list of expressions. If any of the argument values is NULL, the result is NULL. GREATEST supports all data types, including VARIANT.

If you ORDER BY data, NULL is last, GREATEST is returning the LAST value..

Snowflake is rather consistent

select 
    a,b,c
    ,greatest(a, b) as g_a_b
    ,greatest(a, c) as g_a_c
    ,greatest(b, c) as g_b_c
from values
    (1.1, 2.3, null::float), 
    (null, 2, 3.5), 
    (1, null, 3), 
    (null, null, null)
    t(a,b,c)

gives:

A	B	C	G_A_B	G_A_C	G_B_C
1.1	2.3	null	2.3	null	null
null	2	3.5	null	null	3.5
1	null	3	null	3	null
null	null	null	null	null	null

so your min value -inf solution is interesting/gross, I mean it's correct.

But what GREATEST is doing is handling a fixed number of SQL declared columns, so the NVL soltuion works: But to make a generic solution, building the fix arrays, and then flattening, and the maxing as max handles NULL in the implicit way the problem implies which "them not being selected"

select a,b,c, max(f.value)
from (
    select 
        a,b,c
        ,array_construct_compact(a, b, c) as aa
    from values
        (1.1, 2.3, null), 
        (null, 2, 3.5), 
        (1, null, 3), 
        (null, null, null)
        t(a,b,c)
), table(flatten(input=>aa)) as f
group by 1,2,3,f.seq

gives:

A	B	C	MAX(F.VALUE)
1	null	3	3
null	2	3.5	3.5
1.1	2.3	null	2.3

Answer 6

Here's a yucky approach, but at least:

• It works for both GREATEST & LEAST.
• It doesn't require any synthetic values, like -inf (i.e. it can only return values from the fields themselves, and will never convert any valid values to NULL).
• It scales (uglily) to any number of parameters (it just gets harder/uglier to write with more parameters).
• The result will only be NULL if all the fields are NULL.

WITH test AS (
  SELECT
    column1 AS a,
    column2 AS b,
    column3 AS c,
    column4 AS d
  FROM VALUES
    ('aaa', 'bbb', 'ccc', NULL),
    ('aaa', 'bbb', NULL, 'ccc'),
    ('aaa', NULL, 'bbb', 'ccc'),
    (NULL, 'aaa', 'bbb', 'ccc'),
    (NULL, NULL, NULL, NULL)
)
SELECT
  GREATEST(COALESCE(a, b, c, d), COALESCE(b, c, d, a), COALESCE(c, d, a, b), COALESCE(d, a, b, c)) AS _greatest,
  LEAST(COALESCE(a, b, c, d), COALESCE(b, c, d, a), COALESCE(c, d, a, b), COALESCE(d, a, b, c)) AS _least
FROM test;

We have to COALESCE all the values N times, each time starting with a different column (in revolving order, like a circular list), so that:

• If all the columns do have a value, all the values are compared (i.e. the first column in the COALESCE) to find the legitimate GREATEST/LEAST.
• If any of the columns contain NULL, it never surfaces (unless all of the values are NULL).

Result:

We could put the code in SQL UDFs (which support overloading for various number of parameters):

• But then we'd also have to deal with specifying types, or handling automatic type conversion (unless everything is passed as VARIANT).
• And it can hurt performance.

I wish we didn't have to do this, even if that meant we'd have to call GREATEST2(...) or LEAST2(...). Hopefully, Snowflake will make this a reality someday!