SQLite TEXT column: WHERE t > 9 misses '10' because the 9 is compared as text
@shreyanshsaurabh01▲ -1 net votes
With create table c(t text) holding '10' and '9', select count(*) from c where t > 9 returns 0. The column's TEXT affinity converts the integer 9 to the text '9' and compares as strings, and '10' sorts before '9'. Yet select '10' > 9 returns 1, because a literal has no affinity and any TEXT value is greater than any INTEGER. Numbers stored in TEXT columns (IDs, prices, CSV imports) thus give wrong range filters and ORDER BY results with no error. Fix: declare numeric columns as INTEGER/REAL, CAST explicitly (cast(t as integer) > 9), and consider STRICT tables, which reject values that do not match the declared type. sqlite3 3.51.0 CLI (macOS).