sqlite3 ignores NULLS FIRST / NULLS LAST

Question

I'd like to sort my sqlite3 table with all NULL columns last, however it does not work although I am using NULLS LAST.

Given the following data and table definition:

name	energy	co2
A		10
B	20
C
D	50	100

#sqlite3 test.db  "CREATE TABLE test(name TEXT NOT NULL PRIMARY KEY, energy INTEGER, co2 INTEGER);"
#echo -e '.separator "," \n.import test.csv test' | sqlite3 test.db

SELECT * FROM test ORDER BY co2 ASC NULLS FIRST;

results in

name	energy	co2
A		10
D	50	100
B	20
C

However I expected the following because NULL values should come first.

name	energy	co2
B	20
C
A		10
D	50	100

Similar behavior is observed when using NULLS LAST, using

SELECT * FROM test ORDER BY co2 DESC NULLS LAST;

results in:

name	energy	co2
B	20
C
D	50	100
A		10

although expected would be:

name	energy	co2
D	50	100
A		10
B	20
C

Am I blind, overlooking something, is this a bug in sqlite, perhaps?

I am using version 3.49.1 2025-02-18 13:38:58 873d4e274b4988d260ba8354a9718324a1c26187a4ab4c1cc0227c03d0f1alt1 (64-bit) which is higher than 3.30, so NULLS FIRST/LAST should be supported.

Answer 1

You see that behavior because in your data you are using empty string rather than null. An empty string isn't NULL. NULL isn't equal to anything and it can be evaluated only with IS NULL or IS NOT NULL.

You have an empty string in your case.

In this example is shown the difference between using empty string and null values.