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

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();
        }
    }
}