SQLite differences between Android 2.1 and 2.2

Question

I have an Android app, and all my testing so far has been on my Froyo phone. I've just starting testing against 1.6 and 2.1 in the emulator, and it crashes on startup. It can't find a column in one of my views.

05-17 23:31:31.446: ERROR/AndroidRuntime(198): Caused by: 
android.database.sqlite.SQLiteException: no such column: 
categoryTable.currentBal: , while compiling: 
SELECT SUM(categoryTable.currentBal) FROM catDisplayTable 
WHERE masterCategoryName != "__Hidden__"

The schema of the view is as follows:

CREATE VIEW catDisplayTable AS SELECT categoryTable._id, categoryTable.name,
categoryTable.currentBal, categoryTable.sequence, categoryTable.note,
masterCategoryTable.name AS masterCategoryName FROM categoryTable 
LEFT OUTER JOIN masterCategoryTable 
ON categoryTable.masterCategoryId = masterCategoryTable._id;

With adb shell connecting to the various emulator instances, I have confirmed that (1) the correct schema is in place in all cases, and (2) in 1.6 and 2.1, SQLite is just unable to locate the columns in this view, even with something as simple as

SELECT categoryTable.name FROM catDisplayTable;

or

SELECT name FROM catDisplayTable;

It works fine on 2.2.

My presumption therefore is that something has changed in SQLite between Android 2.1 and 2.2. This answer helpfully gives the SQLite versions which were shipped with each Android API level. It says that SQLite was updated from 3.5.9 to 3.6.22 between 2.1 and 2.2. Looking at the SQLite release history, I don't see anything particularly obvious which might explain the difference.

Can anyone identify exactly what's changed, and suggest how I can work around it so my code works on pre-Froyo devices?

Answer 1

I've run into an issue with SQLite when using DISTINCT that maybe the same issue you are running into with SUM.

On earlier OS versions when using DISTINCT, I had to alias the column names or the logic would fail with a similar if not exactly the same error (I don't remember exactly what the error was), but I guess they fixed it in later OS versions.

example

SELECT DISTINCT _id AS _id, test AS test FROM table

works for all OS versions while

SELECT DISTINCT _id, test FROM table

would fail for earlier OSes

Answer 2

I have the exact same problem in my app. The common denominator is that I also use Views and have foreign keys.

I have downloaded my database from the emulator. I have no problems running the query outside of the Android OS 2.1.

Gave mp2526's answer a go but querying with "columnx AS columnx" made no difference. I will try to isolate the issue.