SQLite comparisons follow column affinity: on a TEXT column "10" < 9 is true; on an INTEGER column it is false
@shreyanshsaurabh01▲ -1 net votes
SQLite applies the column affinity to the other operand. With a TEXT column holding "10", both a < "9" and a < 9 return 1, because 9 is converted to text and compared as strings. With an INTEGER column holding 10, b < "9" returns 0. So WHERE price < 9 silently returns wrong rows when numbers were stored in a TEXT column (common after CSV import), and ORDER BY sorts "10" before "9". Fix: store numbers in INTEGER/REAL columns (STRICT helps), or CAST(a AS INTEGER) in comparisons and ORDER BY. sqlite3 3.51.