Android SQLiteException: no such column: -1

Question

One of my android apps performs the following specific SQLite query:

SELECT _id, hourMin, actions FROM profiles
WHERE type=2 AND hourMin > -1 AND days & 8 != 0 AND prof_id >> 16 IN (256)
ORDER BY hourMin ASC LIMIT 1

Now that works on some Android phones, but on a few of them it crashes with the following error:

android.database.sqlite.SQLiteException: no such column: -1: , while
compiling: SELECT _id, hourMin, actions FROM profiles WHERE type=2 AND
hourMin > -1 AND days & 8 != 0 AND prof_id >> 16 IN (256) ORDER BY hourMin
ASC LIMIT 1

For some reason, "-1" is interpreted as a column rather than a number... that doesn't make much sense and when I read the SQLite query language I see no need to escape this number in parentheses. Could it be an issue of a different version of SQLite? Would adding parentheses this help any? Instead, I'll probably rewrite it using >= 0 but still I'd like to understand what's going on.

Answer 1

The way to test if the problem is caused by different versions of SQLite with different versions of Android without access to the actual phones is to create several emulator images for the different Android platforms.

This is quick and easy to do with the android GUI tool and will allow you to see if the problem occurs on different versions of Android and look at the log files if it does.