SQLite cursor and table alias bug in android database

Suppose you have two table to join, like these:

ARTICLE
ID NAME ID_CATEGORY
1 Printer 1
2 CPU 2
3 RAM 2
4 Mouse 1

 

CATEGORY
ID NAME
1 Device
2 PC Component

 

and you want to join them together to get a list of articles with their category name to put in a list handled by a SimpleCursorAdapter. You then make a query like this:

select a.ID, a.NAME, c.NAME from ARTICLE a join CATEGORY c on a.ID_CATEGORY = c.ID

and put the result inside a SimpleCursorAdapter like this:

SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, 
        android.R.layout.simple_list_item_2, 
        cursor, 
        new String[] { "a.NAME", "c.NAME" }, 
        new int[] { android.R.id.text1, android.R.id.text2 }
);

You expect a result like this:

ListView
Printer Device
CPU PC Component
RAM PC Component
Mouse Device

instead you get:

ListView
Device Device
PC Component PC Component
PC Component PC Component
Device Device

Why?

To undestand why, we refers to this issue: Issue 7201: AbstructCursor does not allow to use column name with period.

Let's see the full code here (row 156):

@Override
public int getColumnIndex(String columnName) {
    // Create mColumnNameMap on demand
    if (mColumnNameMap == null) {
        String[] columns = mColumns;
        int columnCount = columns.length;
        HashMap<String, Integer> map = new HashMap<String, Integer>(columnCount, 1);
        for (int i = 0; i < columnCount; i++) {
            map.put(columns[i], i);
        }
        mColumnNameMap = map;
    }

    // Hack according to bug 903852
    final int periodIndex = columnName.lastIndexOf('.');
    if (periodIndex != -1) {
        Exception e = new Exception();
        Log.e(TAG, "requesting column name with table name -- " + columnName, e);
        columnName = columnName.substring(periodIndex + 1);
    }

    Integer i = mColumnNameMap.get(columnName);
    if (i != null) {
        return i.intValue();
    } else {
        return -1;
    }
}

As you can see, the hack relative to bug 903852 check if a dot exists in the column name definition (like a.NAME or c.NAME). If exists (periodIndex != -1) throws a random exception reporting a not-so-useful error log and simply ignore whatever stands before the dot, using only what's next as column name. Then put that string in the map of columns. So, because our select use a.NAME and c.NAME as column name, they become NAME and NAME thanks to the hack. But the second NAME overwrite the first one, so what you get is that the last occurrence of NAME became the only one written in the adapter. That's it, the category name became the only name displayed in our list. This hack is quite annoying, especially if you plan to build your dynamic queries using table aliases to avoid the dreaded "ambiguous column name" exception.

So how can we address this issue? The first link posted in this article points us in the right direction. Instead of using table aliases, we use column aliases. Our query then became something like this:

select ARTICLE.ID as id, ARTICLE.NAME as articleName, CATEGORY.NAME as categoryName from ARTICLE join CATEGORY on ARTICLE.ID_CATEGORY = CATEGORY.ID

and in the adapter we put the alias as our from[] string array:

SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, 
        android.R.layout.simple_list_item_2, 
        cursor, 
        new String[] { "articleName", "categoryName" }, 
        new int[] { android.R.id.text1, android.R.id.text2 }
);

Hope they fix this problem soon...

Tags: