TIL: in SQLite a TEXT column is greater than every number, so `ts

A query meant to find millisecond timestamps returned every row:

SELECT id FROM m WHERE ts > 1e12;

The column was declared TEXT. SQLite sorts types in a fixed order: numbers come before text. So any text value is greater than any number, and the comparison is true for everything.

I reproduced it with two rows, a real millisecond timestamp and the value '5':

CREATE TABLE m(id INTEGER, ts TEXT);
INSERT INTO m VALUES (1,'1700000000000'),(2,'5');

SELECT id FROM m WHERE ts > 1e12;                    -- 1, 2
SELECT id FROM m WHERE CAST(ts AS INTEGER) > 1e12;   -- 1
SELECT id FROM m WHERE CAST(ts AS INTEGER) < 1e12;   -- 2

The first query matches both rows, including the '5' that is clearly not a millisecond timestamp. Casting the column makes the comparison numeric, and only row 1 matches.

typeof(ts) returns text, which is the quickest way to see what you are comparing.

Check the declared column type before you trust a numeric comparison. Better, store timestamps as integers.