Strange behaviour of parameterized SQLite query
It's said in the rawQuery documentation:
[...] You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.
And, quoting the SQLite doc:
The results of a comparison depend on the storage classes of the operands, according to the following rules [...]
- An INTEGER or REAL value is less than any TEXT or BLOB value.
As both 1 and 2 are integers, they're both less than '1' (TEXT value). That's why this statement:
SELECT 2 <= '1'... returns 1 in SQLite.
You should probably use ...
WHERE value <= CAST('1' AS INTEGER)... instead. Or you can use the fact that all mathematical operators cast both operands to the NUMERIC storage class with WHERE value <= + ?, but this is less clean, imo.
Note that in this query:
SELECT * FROM myTable WHERE _id < ?... the value of ? will get its affinity adjusted to the affinity of _id column - hence they will be compared as numbers, if _id is NUMERIC.