Binding query param in LENGTH() condition doesn't work

Issue

I'm having some trouble finding any info about this problem, but it appears to be a limitation of SQLite.

Consider a simple words table with 2 fields, _id (int) and word (text). The following query works and returns the expected results (all words which are 12 characters or less):

SELECT * FROM words WHERE LENGTH(word) <= 12;

However if this character limit needs to be dynamic and made into a parameter, the query no longer works. It returns all rows of the table:

String query = "SELECT * FROM words WHERE LENGTH(word) <= ?";
Cursor cursor = database.rawQuery(query, new String[]{ Integer.toString(12) });

I also tried selecting the length as a new column, then applying the condition to that, but it gives the same results:

String query = "SELECT w.*, LENGTH(w.word) AS word_length FROM words w WHERE word_length <= ?";
Cursor cursor = database.rawQuery(query, new String[]{ Integer.toString(12) });

Is my only option to just filter through the query results afterward? Why do parameterized conditions on normal INT columns work but not on LENGTH()? (e.g. WHERE _id < ? works fine)

Solution

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.

Answered By - forpas