GREATEST / LEAST (scalar MAX / MIN) give order-dependent results when an argument is NULL #2583

GREATEST and LEAST (and the scalar forms MAX(a, b) and MIN(a, b), which are the same functions) give a different answer depending on where the NULL is in the argument list, and on whether the NULL is a SQL NULL literal or a JavaScript null parameter.

Measured on v4.20.0:

alasql('SELECT VALUE GREATEST(1, NULL)');             // undefined
alasql('SELECT VALUE GREATEST(NULL, 1)');             // 1
alasql('SELECT VALUE LEAST(1, NULL)');                // undefined
alasql('SELECT VALUE LEAST(NULL, 1)');                // 1

alasql('SELECT VALUE GREATEST(?, ?)', [-1, null]);    // null
alasql('SELECT VALUE GREATEST(?, ?)', [1, null]);     // 1
alasql('SELECT VALUE LEAST(?, ?)', [1, null]);        // null
alasql('SELECT VALUE LEAST(?, ?)', [null, 1]);        // null

No SQL engine behaves like this. Engines differ on what the answer should be, but each one is consistent:

• NULL if any argument is NULL: SQL:2023, MySQL, MariaDB, Oracle, Db2, and SQLite's scalar max() / min().
• NULLs are skipped, NULL only if every argument is NULL: PostgreSQL, SQL Server 2022, DuckDB.

Cause

src/55functions.js compiles the call to a plain reduce:

[a, b, …].reduce(function (a, b) { return a > b ? a : b; })

• A SQL NULL literal is undefined in the generated code. Every comparison with undefined is false, so the reduce keeps the right-hand argument: a NULL in the last position wins, a NULL in the first position is dropped.
• A JavaScript null is coerced to 0 by > and <, so it wins or loses against the other argument as if it were the number zero.

History

The scalar form was added in March 2015 (57ee5fb, "GREATEST and LEAST"). The current body is from January 2020 (#1156). The only test is test/test415.js case 5, which uses numbers and strings and no NULL, so the suite has never exercised this.

Expected

One rule, the same for every argument order and for null and undefined alike. Which of the two rules is the default is a v5 decision (it is tied to the dialect work); the other rule should be available as a module. Tests for both orders and both kinds of NULL.

Related

• Issues with aggregate functions, date handling, null handling: GREATEST, LEAST, MAX, MIN, VAR, STDEV, VARP, QUART/2/3 #2481 lists this among several aggregate and NULL problems.
• Refactor MAX and MIN functions to handle null values #2480 and Fix MIN() to handle date comparison correctly #2479 attempt a fix. They route every comparison through new Date(x).getTime(), which fixes the NULL case but breaks ordinary values: with that body GREATEST('a', 'b') returns 'a' and GREATEST(0.4, 0.9) returns 0.4. The NULL check from those pull requests is the part to keep.
• The aggregate MAX(col) / MIN(col) is separate code (src/423groupby.js) and already skips NULLs. It has its own gap with strings, see MAX() on strings #609.