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.