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
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.