MAX/MIN, Scalar vs Aggregate function and NULLs #db2fori #ibmi

This is a note on a surprising difference and kind of a follow-up on a RPGPGM.COM article (so go read it first and put the site in your favorite!). I already commented on it there, but thought it would be worth a small post.

Try to guess the result for the following statement, then let it run and observe

/*
 * (max_row of max_col) = (max_col of max_row) ?
 */

SELECT
    max(max(a),max(b),max(c)) as max_colmax
    , max(max(a,b,c)) max_rowmax
FROM (
    VALUES (1,2,0), (3,0,1), (1,8,3)
    ) as inpx( a, b, c )
;

/*
 * funky NULLs ...
 * (max_row of max_col) <> (max_col of max_row)
 */

SELECT
    max(max(a),max(b),max(c)) as max_colmax
    , max(max(a,b,c)) max_rowmax
FROM (
    VALUES (1,NULL,0), (3,4,1), (NULL,8,3)
    ) as inpx( a, b, c )
;

I went with MAX() in this example, but the same goes for MIN(). More info in the doc: https://www.ibm.com/docs/en/i/7.4?topic=sf-max and https://www.ibm.com/docs/en/i/7.4?topic=functions-max.

Now you may want to write your query with GREATEST() and LEAST()for the scalar function instead, a bit less misleading IMHO.