Incorrect RowId with SQLite using FTS3 on Android

I am experiencing an incorrect rowid value with sqlite and fts3.

Basically, the first two rows are inserted and the rowId is 1 and 2 respectively. The third insert however returns 4 instead of 3.

I dropped the following log message in my contentprovider when inserting a row:

SQLiteDatabase database =
databaseHelper.getWritableDatabase();
long rowId = database.insert(NOTES_TABLE, Note.NOTE, values);
Log.d("NoteProvider", "RowId inserted was " + rowId);

The output of that log message is:

02-21 21:10:12.773: DEBUG/NoteProvider(2486): RowId inserted was 1
02-21 21:10:20.623: DEBUG/NoteProvider(2486): RowId inserted was 2
02-21 21:10:25.883: DEBUG/NoteProvider(2486): RowId inserted was 4

So what happened to 3?

Also, I exported the sqlite database so I could look at in a SqLite browser and the content table shows 1, 2 and 3.

So 3 was created but 4 was returned.

I ran my testing up to 10, and the rowId was sequential afterwards, but still off by 1.

The database create script is:

database.execSQL("CREATE VIRTUAL TABLE " + NOTES_TABLE + "
USING fts3 ("
           + Note.TITLE + ", "
           + Note.NOTE + ", "
           + Note.CREATED_DATE + ", "
           + Note.MODIFIED_DATE + ");");

I am assuming I have done something horribly wrong, but cannot figure out what it is. Can anyone help me out?

Edit 1:
Did some more testing and have the same results on 2.3.1 and 2.1.
Although in 2.1 the ids when 1, 3 (skipped 2)

Edit 2:
Finally came up with a work around. However I think this is a pretty severe bug or something. What I ended up doing was selecting the max rowid from the table bumping up by 1 and then setting that value to the rowid on insert. Its a complete suck solution. A very dirty and cheap hack, but it is the only thing I get to work reliably. Anyway here is the code:

SQLiteDatabase database = databaseHelper.getWritableDatabase();
SQLiteStatement statement = database.compileStatement("select ifnull(max(rowid), 0) as rowid from " + NOTES_TABLE);
long maxRowId = statement.simpleQueryForLong();
Log.d("NoteProvider", "Statement returned " + maxRowId);
long rowId = maxRowId + 1;
values.put("rowid", rowId);
database.insert(NOTES_TABLE, null, values);
Log.d("NoteProvider", "RowId inserted was " + rowId);

Edit 3:
Looks like another fellow was/is having this problem. Android Full Text Search and ListAdapter

回答1:

I ran into the same issue as well and came up with solution similar to yours :)

However, due to other troubles I had with FTS3 I have completely redesigned the database: pushed non text columns to separate table, text fields to other one and created view for query; as well as triggers for delete.

Therefore now I am using good old Table._ID :)

回答2:

This looks to be a bug in SQLite. The solution in "Edit 2" of the original post seems to be working ok. Have not tried it out in Honeycomb to verify it has been fixed there.