Issue found on page 'ORDER BY Clause'

[gropaul]

Hi Dear DuckDB Team,

I have a question concerning the sort order: The documentation says: This is identical to the default sort order of PostgreSQL. However, I think this is not true concerning null values. The DuckDB default is always having the nulls last, while postgres18 has the nulls first on DESC and last on ASC.

I am not sure if this is an issue, but it took me some time when I was crosschecking results from DuckDB and Postgres so figure that out.

Example: I have the following schema:

CREATE TABLE test_nulls (
    name VARCHAR,
    flag VARCHAR
);

INSERT INTO test_nulls VALUES
    ('Alice', 'Y'),
    ('Bob', 'N'),
    ('Carol', NULL),
    ('Dave', 'Y'),
    ('Eve', NULL);

With DuckDB v1.4.3 :

D SELECT * FROM test_nulls ORDER BY flag DESC;
┌─────────┬─────────┐
│  name   │  flag   │
│ varchar │ varchar │
├─────────┼─────────┤
│ Alice   │ Y       │
│ Dave    │ Y       │
│ Bob     │ N       │
│ Carol   │ NULL    │
│ Eve     │ NULL    │
└─────────┴─────────┘
D SELECT * FROM test_nulls ORDER BY flag ASC;
┌─────────┬─────────┐
│  name   │  flag   │
│ varchar │ varchar │
├─────────┼─────────┤
│ Bob     │ N       │
│ Alice   │ Y       │
│ Dave    │ Y       │
│ Carol   │ NULL    │
│ Eve     │ NULL    │
└─────────┴─────────┘

With PostgreSQL v18

Query #1

SELECT * FROM test_nulls ORDER BY flag DESC;

name	flag
Carol
Eve
Alice	Y
Dave	Y
Bob	N

---

Query #2

SELECT * FROM test_nulls ORDER BY flag ASC;

name	flag
Bob	N
Alice	Y
Dave	Y
Carol
Eve

---

View on DB Fiddle