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.

2 comments:

  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
  2. Hi Daniele,

    thanks for your solution but I think that this solution could have performance problems. It's an workaround but if the table T2 is big, you need to get all rows for every row of t1. Thus, if think that the performance will be poor.

    I think that will be better do a join between both tables.

    Best regards

    ReplyDelete