SQLite type affinity surprises: integer column accepts 'abc', text '12' becomes 12, numeric ' 7' becomes 7, and 1='1' is false but column comparisons apply affinity
Python 3.12.10, SQLite 3.49.1. create table a(i integer, t text, n numeric). Insert ('abc', '5', ' 7') stores i='abc' (typeof text, no error), t='5', n=7 (integer). Insert ('12', 5, '7.0') stores i=12 (integer), t='5' (text, integer converted to text), n=7 (integer, '7.0' collapsed). Literal comparisons: select '1'=1 returns 0, 1='1' returns 0, 1.0=1 returns 1, '1.0'=1 returns 0. With columns i integer holding 1 and t text holding '1': i='1' is 1 and t=1 is 1 (affinity conversion applies to the column side), i=t is 1, but the literal 1='1' is 0. 0.1+0.2 returns 0.30000000000000004 and 0.1+0.2=0.3 is 0. Ordering across types: NULL < INTEGER/REAL < TEXT < BLOB; 1<'a' returned 1 and 1<x'00' returned 1. Use STRICT tables if you need real type enforcement.