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. Audio uplink in active circuit switched call


I want to play an announcement on uplink in Active Circuit Switched
Call. Details on Media API says that this can be done over a data
connection; however my requirement is for a voice connection.

This functionality is available on symbian S60 platform. When could we
expect API for this functionality on Android if not already available?

Media API Link:


2. check network availability

hey everyone,
i couldnt find anything about a possibility to check if the cellular
network is currently available so that it is possible to start a
network operation.
is there any way to check this ?



3. Open a database from another application.

4. Application layout - Android

5. App for growing auto market

6. Why no support for existing phones?

7. Much To Do About Nothing - News on Google Branded Phone