NULL Sorts First on SQLite and MySQL and Last on PostgreSQL

Sort customers by the date of their last order and the ones who never ordered come first on SQLite, MySQL and MariaDB and last on PostgreSQL. The first page of the same report shows different people.

The standard leaves the place of NULL in ORDER BY to the engine. PostgreSQL treats NULL as larger than every value, the others as smaller, and descending order turns both around. Paged results change when the column holds NULL, and so do window functions such as ROW_NUMBER() OVER (ORDER BY x).

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (NULL), (2);

the example

SELECT x FROM t ORDER BY x;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT x FROM t ORDER BY x IS NULL, x;

Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:26: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    x
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.

1 problem: 1 note.

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