android

Using Subquery Columns on Android with SQLite

Dan Lew

May 24, 2011 • 1 min read

Take a look at this (contrived) query, as might be passed to [SQLiteDatabase.rawQuery()](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[])):

SELECT *
FROM t1 A, (SELECT T2.id FROM t2 T2) B
WHERE A.id = B.id

On Android 2.1 and below, this query will cause your app to crash. You'll get an error like this:

Caused by: android.database.sqlite.SQLiteException: no such column: B.id: , while compiling: SELECT * FROM t1 A, (SELECT T2.id FROM t2 T2) B WHERE A.id = B.id
    at android.database.sqlite.SQLiteProgram.native_compile(Native Method)
    at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1220)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1193)
    at com.mycompany.myapp.MyActivity.onCreate(QuickTestActivity.java:22)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2459)
    ... 11 more

It turns out that SQLite throws this error if two conditions are met:

The solution is simple: alias the selected field names. This query will work:

SELECT *
FROM t1 A, (SELECT T2.id AS id FROM t2 T2) B
WHERE A.id = B.id

I'm hardly an expert on SQL or SQLite, so I don't know whether this is a bug or just me running into undefined behavior. I ran into this problem when doing JOINs on multiple tables, so as contrived as my example is, it can happen. Regardless, it is easy to work around.

Sign up for more like this.