Android sqlLite ON CONFLICT IGNORE is ignored in ICS

Question

I have a simple address table with the following create statement:

"CREATE TABLE " + ADDRESSES_TABLE + " (" +
                KEY_ADDRESS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_ADDRESS_COUNTRY + " TEXT, " +
                KEY_ADDRESS_CITY + " TEXT, " +
                KEY_ADDRESS_STREET + " TEXT, " +
                KEY_ADDRESS_HOUSE + " TEXT, " +
                KEY_ADDRESS_POSTAL_CODE + " TEXT," +
                "UNIQUE("+KEY_ADDRESS_COUNTRY+","+KEY_ADDRESS_CITY+","+KEY_ADDRESS_STREET+","+KEY_ADDRESS_HOUSE+","+KEY_ADDRESS_POSTAL_CODE +") ON CONFLICT IGNORE)"

When I add duplicate records, the insert() metod returns -1 and not the id of the existing row.

The problem is reproducible only on 4.0+. The method works as expected on 2.2 and 2.3.3.

Has anyone faced the same issue?

I tried both, but I think insertWithOnConflict does not make sense if I specified ON CONFLICT IGNORE in TABLE CREATE. — k_shil, Jul 05 '12 at 07:43

score 9 · Accepted Answer · answered Jul 11 '12 at 01:04

9

Unfortunately, the Android documentation is wrong. (And thus your code does not actually work as expected on prior versions of Android, e.g., 2.2 and 2.3.)

In 2.2 and 2.3, insertWithOnConflict() will return the value of the SQLite C API function sqlite3_last_insert_rowid(), whose documentation clearly states that failed inserts (e.g., where the ON CONFLICT resolution, like IGNORE, is applied) do not affect the return value. Thus, if no prior insert for the connection was performed and a duplicate insert is attempted, insertWithOnConflict() will return 0. If a prior insert added a row to any table, the method will return the row id of that insert---of course this is grossly incorrect.

In 4.0, insertWithOnConflict() will return the value of the same SQLite C API function, except returning -1 instead of 0.

This change is why you are now observing the error. But if you check the result closely in 2.2 and 2.3, you'll see that the row ids returned when the OR IGNORE clause is exercised are not actually the correct row ids (except coincidentally).

answered Jul 11 '12 at 01:04

David B.

5,700
5
31
52

if this is indeed the correct behavior, you should [submit it as a bug](http://source.android.com/source/report-bugs.html)... couldn't hurt :D – Alex Lockwood Jul 11 '12 at 01:19
1

It's in there as Issue 13045: https://code.google.com/p/android/issues/detail?id=13045 – David B. Jul 11 '12 at 01:52
1

Thanks for the clarification. I really relied on this method. So the best workaround is: try{ insertOrThrow(/*All required params*/) } catch(SQLException e){ //Select the required record and get primary key from it } Isn't it too heavy for the DB in terms of time? – k_shil Jul 11 '12 at 10:34
Unfortunately, that's the best method that I know of. The running time shouldn't be any worse than double just the insert time, because the SELECT will use the same indices (if any) that were used to determine if the insert was a duplicate. – David B. Jul 11 '12 at 12:57

Android sqlLite ON CONFLICT IGNORE is ignored in ICS

1 Answers1

Linked