Sqlite spec: 4.2. Type Conversions Prior To Comparison
Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especially this statement:
If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.
I would expect the following query:
CREATE TABLE t (id integer primary key, str varchar(20)); INSERT INTO t (id, str) VALUES (1, '5'), (2, '5u');
SELECT id, 5 = str, 5 >= str, CAST(5 AS NUMERIC) >= str, CAST(str AS NUMERIC) FROM t;
5 >= str=
1for both rows as the left side operand has NUMERIC affinity.
I think you may be missing https://www.sqlite.org/lang_expr.html#castexpr :
with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a value if the change is lossless and reversible