GREATEST Skips NULL on PostgreSQL and Returns NULL on MySQL

GREATEST(sale_price, list_price) picks the larger of two prices. When one of them is NULL, PostgreSQL still picks the other price, and no other engine does.

GREATEST(1, NULL) is 1 on PostgreSQL and NULL on MySQL and MariaDB. SQLite has the same idea under the names MAX(a, b) and MIN(a, b) with two or more arguments; they return NULL when any argument is NULL, and the other engines read MAX and MIN as aggregates.

What Each Engine Answered

On a table made with

CREATE TABLE t (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
INSERT INTO t VALUES (1, 1, NULL), (2, NULL, 5);

the example

SELECT GREATEST(a, b) FROM t ORDER BY id;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT CASE WHEN a IS NULL THEN b WHEN b IS NULL OR a >= b THEN a ELSE b END FROM t ORDER BY id;

Write it out with CASE and say what NULL should do. CASE WHEN a IS NULL THEN b WHEN b IS NULL OR a >= b THEN a ELSE b END skips NULL, as PostgreSQL’s GREATEST does, on all four engines. A plain CASE WHEN a >= b THEN a ELSE b END answers b whenever the comparison meets a NULL, so it gives 5 for (NULL, 5) and NULL for (1, NULL).

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:8: error: GREATEST and LEAST are missing on SQLite, skip NULL on PostgreSQL and return NULL on MySQL and MariaDB. [greatest-least]
    GREATEST(a, b)
    Write it out with CASE and say what NULL should do. CASE WHEN a IS NULL THEN b WHEN b IS NULL OR a >= b THEN a ELSE b END skips NULL, as PostgreSQL's GREATEST does, on all four engines. A plain CASE WHEN a >= b THEN a ELSE b END answers b whenever the comparison meets a NULL, so it gives 5 for (NULL, 5) and NULL for (1, NULL).

1 problem: 1 error.

baresql rules greatest-least prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.