Android database strangeness listing columns

I am getting inconsistent results between two methods of reading the columns in an Android SQLite database.

First, this is part of a database upgrade routine as per the accepted answer here: Upgrade SQLite database from one version to another?

The technique involves moving the current table away with a temporary name, creating a new table with the new schema, and then copying relevant data from the old table into the new one before deleting the old temporary table.

The particular problem I have is when I remove a column from the schema. So, a particular column exists in the old version of the table, but not the new one.

That answer suggests using a method like this to list the columns in the table:

/**
 * Returns a list of the table's column names.
 */
private List<String> getColumns(SQLiteDatabase db, final String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

That works fine on the old table, before I move it away with a temporary name and replace it. When I run the same query again later, on the newly-created empty table, it still lists the old table schema with the name of the column which no longer exists. It looks as if it's reusing stale cached results for that query.

If I read the columns a different way, using this instead, then it returns the new column list as expected:

private void listColumns(SQLiteDatabase db, final String tableName) {

    final String query = "PRAGMA table_info(" + tableName + ");";
    Cursor c = db.rawQuery(query, null);
    while (c.moveToNext()) {
        Log.v("MyApp", "Column: " + c.getString(1));
    }
    c.close();
}

The complete sequence is:

final String tempTableName = "temp_" + tableName;

table.addToDb(db); // ensure it exists to start with

// get column names of existing table
final List<String> columns = getColumns(db, tableName);

// backup table
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + 开发者_如何学PythontempTableName);

// create new table
table.addToDb(db);

// delete old columns which aren't in the new schema
columns.retainAll(getColumns(db, tableName));

// restore data from old into new table
String columnList = TextUtils.join(",", columns);
db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s from %s", tableName, columnList, columnList,
                 tempTableName));

// remove backup
db.execSQL(DROP_TABLE + tempTableName);

What's the reason for the different results?

I assume you have done something similar to this:

ALTER TABLE "main"."mytable" RENAME TO "newtable"; 
CREATE TABLE "main"."mytable" ("key1" text PRIMARY KEY,"key2" text,"key3" text);
INSERT INTO "main"."mytable" SELECT "key1","key2","key3" FROM "main"."newtable"; 
DROP TABLE "main"."newtable";

If you have, please share the equivalent code, just to rule out any errors with this part.

I never got to the bottom of this. I just ended up using the second method I mentioned, which doesn't exhibit the problem.