(Feel free to improve the question title 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 the parameter, I expect the query to yield 1
, yet it yields 2
. Why does this happen?
Additional information:
This is a minimal working example to reproduce the issue (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)));
It might have to do with the fact that the parameter is passed as a string, but, alas, there is no other way to pass parameters to the SQLite API, so I guess I'm not doing anything "wrong" here and it's SQLite's job to convert the value appropriately. I've also observed the following when using a non-parameterized version of the query (this might or might not be relevant for the issue):
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