Strange behavior of a parameterized SQLite query

Question

(Feel free to improve the title of the question if you can think of something better.)

Question Consider the following SQLite query:

SELECT COUNT(*) FROM (SELECT 1 AS value UNION SELECT 2 AS value) WHERE value <= ? 

When I use 1 as a parameter, I expect the query to give 1 , but it will give 2 . Why is this happening?

---

Additional information :

This is a minimal working example for reproducing a problem (Android):

 Cursor c = db.rawQuery( "SELECT COUNT(*) " + " FROM (SELECT 1 AS value UNION SELECT 2 AS value) " + " WHERE value <= ? ", new String[] {String.valueOf(1)}); c.moveToFirst(); Log.i("", "Result: " + String.valueOf(c.getInt(0))); 

Perhaps this is due to the fact that the parameter is passed as a string, but, alas, there is no other way to pass the SQLite API parameters, so I think that I am not doing anything “wrong” here and this is a SQLite task to convert the value accordingly. I also noticed the following when using a non-parameterized version of the request (this may or may not be relevant to the problem):

 SELECT COUNT(*) FROM (SELECT 1 AS value UNION SELECT 2 AS value) WHERE value <= 1 -- yields 1 SELECT COUNT(*) FROM (SELECT 1 AS value UNION SELECT 2 AS value) WHERE value <= '1' -- yields 2 

Answer 1

The rawQuery documentation states:

[...] Can you turn it on? s in the where clause in the query, which will be replaced by the values ​​from selectionArgs. Values ​​will be bound as strings.

And, quoting the SQLite doc :

The comparison results depend on the storage classes of the operands in accordance with the following rules [...]

• An INTEGER or REAL value is less than any TEXT or BLOB value.

Since both 1 and 2 are integers, they are less than 1 (TEXT value). This is why this statement:

 SELECT 2 <= '1' 

... returns 1 in SQLite.

You should probably use ...

 WHERE value <= CAST('1' AS INTEGER) 

... instead of this. Or can you use the fact that all mathematical operators apply both operands to the NUMERIC storage class with WHERE value <= + ? but it's less clean, imo.

Please note that in this request:

 SELECT * FROM myTable WHERE _id < ? 

... meaning ? gets its affinity, adjusted for the affinity of the _id column - therefore, they will be compared as numbers, if _id is NUMBER.