Why does insertWithOnConflict(..., CONFLICT_IGNORE) return -1 (error)?

Question

I have an SQLite table:

CREATE TABLE regions (_id INTEGER PRIMARY KEY, name TEXT, UNIQUE(name)); 

And some Android code:

Validate.notBlank(region); ContentValues cv = new ContentValues(); cv.put(Columns.REGION_NAME, region); long regionId =      db.insertWithOnConflict("regions", null, cv, SQLiteDatabase.CONFLICT_IGNORE); Validate.isTrue(regionId > -1,     "INSERT ON CONFLICT IGNORE returned -1 for region name '%s'", region); 

On duplicate rows insertWithOnConflict() is returning -1, indicating an error, and Validate then throws with:

INSERT ON CONFLICT IGNORE returned -1 for region name 'Overseas' 

The SQLite ON CONFLICT documentation (emphasis mine) states:

When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.

The Android insertWithOnConflict() documentation states:

Returns the row ID of the newly inserted row OR the primary key of the existing row if the input param 'conflictAlgorithm' = CONFLICT_IGNORE OR -1 if any error

CONFLICT_REPLACE isn't an option, because replacing rows will change their primary key instead of just returning the existing key:

sqlite> INSERT INTO regions (name) VALUES ("Southern"); sqlite> INSERT INTO regions (name) VALUES ("Overseas"); sqlite> SELECT * FROM regions; 1|Southern 2|Overseas sqlite> INSERT OR REPLACE INTO regions (name) VALUES ("Overseas"); sqlite> SELECT * FROM regions; 1|Southern 3|Overseas sqlite> INSERT OR REPLACE INTO regions (name) VALUES ("Overseas"); sqlite> SELECT * FROM regions; 1|Southern 4|Overseas 

I think that insertWithOnConflict() should, on duplicate rows, return me the primary key (_id column) of the duplicate row — so I should never receive an error for this insert. Why is insertWithOnConflict() throwing an error? What function do I need to call so that I always get a valid row ID back?

Answer 1

While your expectations for the behavior of insertWithOnConflict seems entirely reasonable (you should get the pk for the colliding row), that's just not how it works. What actually happens is that you: attempt the insert, it fails to insert a row but signals no error, the framework counts the number of rows inserted, discovers that the number is 0, and, explicitly, returns -1.

Edited to add:

Btw, this answer is based on the code that, eventually, implements insertWithOnConflict:

int err = executeNonQuery(env, connection, statement); return err == SQLITE_DONE && sqlite3_changes(connection->db) > 0         ? sqlite3_last_insert_rowid(connection->db) : -1; 

SQLITE_DONE is good status; sqlite3_changes is the number of inserts in the last call, and sqlite3_last_insert_rowid is the rowid for the newly inserted row, if any.

Edited to answer 2nd question:

After re-reading the question, I think that what you are looking for is a method that does this:

• inserts a new row into the db, if that is possible
• if it cannot insert the row, fails and returns the rowid for the existing row that conflicted (without changing that rowid)

The whole discussion of replace seems like red herring.

The answer to your 2nd question, then, is that there is no such function.

Answer 2

The answer to your question, unfortunately, is that the docs are simply wrong and there is no such functionality.

There is an open bug from 2010 that addresses precisely this issue and even though 80+ people have starred it, there is no official response from the Android team.

The issue is also discussed on SO here.

If your use case is conflict-heavy (i.e. most of the time you expect to find an existing record and want to return that ID) your suggested workaround seems the way to go. If, on the other hand, your use case is such that most of the time you expect for there to be no existing record, then the following workaround might be more appropriate:

try {   insertOrThrow(...) } catch(SQLException e) {   // Select the required record and get primary key from it }  

Here is a self-contained implementation of this workaround:

public static long insertIgnoringConflict(SQLiteDatabase db,                                           String table,                                           String idColumn,                                           ContentValues values) {     try {         return db.insertOrThrow(table, null, values);     } catch (SQLException e) {         StringBuilder sql = new StringBuilder();         sql.append("SELECT ");         sql.append(idColumn);         sql.append(" FROM ");         sql.append(table);         sql.append(" WHERE ");          Object[] bindArgs = new Object[values.size()];         int i = 0;         for (Map.Entry<String, Object> entry: values.valueSet()) {             sql.append((i > 0) ? " AND " : "");             sql.append(entry.getKey());             sql.append(" = ?");             bindArgs[i++] = entry.getValue();         }          SQLiteStatement stmt = db.compileStatement(sql.toString());         for (i = 0; i < bindArgs.length; i++) {             DatabaseUtils.bindObjectToProgram(stmt, i + 1, bindArgs[i]);         }          try {             return stmt.simpleQueryForLong();         } finally {             stmt.close();         }     } }