Binding query param in LENGTH() condition doesn't work
The sql statement that is executed with:
rawQuery(query, new String[]{ Integer.toString(12) });
is:
SELECT * FROM words WHERE LENGTH(word) <= '12';
and not:
SELECT * FROM words WHERE LENGTH(word) <= 12;
because rawQuery()
treats all the passed parameters as strings and encloses all of them inside single quotes.
So the integer LENGTH(word)
is compared to a string literal like 12
and this is where exists a feature of SQLite which states that:
An INTEGER or REAL value is less than any TEXT or BLOB value.
(from Datatypes In SQLite Version 3).
So all integers are considered less than the string literal '12'
.
Of course this is not what you want and expect, so what you can do is force a conversion of '12'
to the integer 12
and you can do it by adding 0
to it:
String query = "SELECT * FROM words WHERE LENGTH(word) <= ? + 0";
What this does is an implicit conversion of '12'
to 12
because you apply to it a numeric operation.