Incorrect RowId with SQLite using FTS3 on Android 2.2

by Joe » Wed, 23 Feb 2011 07:12:05 GMT

Sponsored Links
 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 =
    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?

** This is a repost of my question on stackoverflow. 


Re: Incorrect RowId with SQLite using FTS3 on Android 2.2

by Bret Foreman » Wed, 23 Feb 2011 10:15:15 GMT

 According to my reading of the SQLite docs, the rowid is guaranteed to
be monotonically increasing but NOT sequential. And I would be
suspicious that your "export" is renumbering the rowids as part of the
export process.


Sponsored Links

Re: Incorrect RowId with SQLite using FTS3 on Android 2.2

by Joe » Thu, 24 Feb 2011 06:37:19 GMT

 Did some more testing with other versions of Android  (2.1 and 2.3.1)
and had the same results. Although with 2.1 the inserts returned 1, 3
(skipped 2).

Is  anyone using full text search on Android?  Is this a bug in

I am completely stumped and could really use some help.

I found someone else on stackoverflow that looks to be having the same



Re: Incorrect RowId with SQLite using FTS3 on Android 2.2

by Joe » Thu, 24 Feb 2011 06:37:24 GMT

 Is this thing working? I posted a couple times now and nothing has
showed up.


Re: Incorrect RowId with SQLite using FTS3 on Android 2.2

by Joe » Fri, 25 Feb 2011 12:57:21 GMT

 Yeah I've read the docs, so I don't expect them to be unique, but I do
expect them to be correct.
By them not correct, I mean that in the database 4 does not exist yet
4 was returned.

Not using FTS3, just a "normal" table, everything works as expected.

I don't know what you mean by "export". The insert is being executed
with the Android SQLiteDatebase insert method. I don't have anything
that updates the id and the id is incorrect at the point it was
returned from the insert method.

I have tested this on 2.1 and 2.3.1 and the same thing occurs.

I am using the method as done in the Notepad sample where an empty row
is inserted in the onCreate() method and then updates are done in
onPause(). Curiously, I have not seen any other code that does this
with FTS3. The SearchableDictionary sample does not care about the id.
However, in this method having the correct id (so we can update the
record) is pretty critical.


Other Threads

1. Necus ternyata jagoan donlot

ginger bread bakal kaya apa ya fiturnya.
froyo aja dah mantap banget.

2010/5/21 lucky sebastian <>


2. Android on iPhone

A recent post on engadget (
apple-outs-iphone-firmware-2-2-beta-1/) stated that the new firmware
2.2 (which is now open for developers) can emulate Android on the
iPhone. Inintially i was thinking to buy an iPhone but while Android /
G1 cuming up, i thought buying G1 wud be a better option. I am in
India, an none of the devices wud be affordable, can u please suggest
me wat shud i buy, as some relatives are cumin from US next month.

3. Samsung SC, di Jaksel

4. Memory leaks???

5. Restricting Softkeyboard Kyes for EditText

6. how browser decides html size on 2.1

7. Apa yg keren di depan