Funny(Glitchy) behaviour querying content resolver

Question

I was trying a small experiment with androids telephony content provider so I wrote this code in my main activity to get 40 rows in descending order by _id and then use the last rows id to get 20 more rows in another cursor having _id < lastRowId

    Uri uri = Uri.parse("content://mms-sms/conversations");
    Cursor cursor = getContentResolver().query(uri, new String[]{"_id", "ct_t"}, null, null,
            "_id desc limit 40");
    Log.d(TAG, "cursor: " + String.valueOf(cursor.getCount()));

    cursor.moveToLast();

    String lastId = String.valueOf(cursor.getInt(0));

    Log.d(TAG, "Last cursor id: " + lastId);

    cursor.close();

    Cursor cursor2 = getContentResolver().query(uri, new String[]{"_id", "ct_t"}, "_id < ?", new String[] {lastId},
            "_id desc limit 20");
    Log.d(TAG, "cursor2: " + String.valueOf(cursor2.getCount()));

    cursor2.moveToLast();

    lastId = String.valueOf(cursor2.getInt(0));

    Log.d(TAG, "Last cursor2 id: " + lastId);

    cursor2.close();

which leads to 0 rows being returned for cursor2 and a crash while trying to read its 0th column (IndexOutOfBounds) but the funny part is that by replacing

Cursor cursor2 = getContentResolver().query(uri, new String[]{"_id", "ct_t"}, "_id < ?", new String[] {lastId},
        "_id desc limit 20");

with

Cursor cursor2 = getContentResolver().query(uri, new String[]{"_id", "ct_t"}, "_id < 653", null,
        "_id desc limit 20");

where 653 is stored in lastId, I get a valid cursor with a count of 20

Can anyone help me out in figuring what the problem might be?

score 2 · Accepted Answer · edited May 23 '17 at 11:50

In the API for the query method they say the parameters will be bound as String. In the resulting query, they´ll appear as '653' even when you may think they should appear as 653.

SQLite or the Java interface, looks to have that kind of weird behaviour SQLite rawQuery selectionArgs and Integers Fields when comparing different arg types. We are used to Oracle, mysql or whatever the provider/language where they cast to upper types before comparing. Looks like SQLite comparison results in false instead of castexception or cast required in runtime or compile time.

So, no results when int = str is part of the conditition.

Nice finding though.

Funny(Glitchy) behaviour querying content resolver

1 Answers1