Tuesday, May 24, 2011

Using Subquery Columns on Android with SQLite

Take a look at this (contrived) query, as might be passed to SQLiteDatabase.rawQuery():

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:
  1. You use a subquery (in this case, the select subquery from table t2).

  2. Your subquery has a table alias (in this case, referencing table t2 as "T2").

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.

1 comment:

  1. This is helpful article thanks for sharing with us.
    Check out this helpful link on, select query in sqlite database using android application...

    http://mindstick.com/Articles/2643e627-4f5c-45cc-b166-9a9b4c9ff3b1/?Select%20query%20in%20Android%20Application

    ReplyDelete