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. ContentResolver query Grammar

String phonenumberString=PhontUliti.formatPhoneNumber(number);
            String[] contactsProjection=new String[]
            Cursor contactsCursor=context.getContentResolver().query
                           contactsProjection, People.NUMBER +" = " 
                           null, People.DEFAULT_SORT_ORDER);

Why the contactsCursor is null?
The number is in   the contacts.
Is there any mistakks in the query?

2. singletask activity for games

I am completely confused with this. I am writing a game and there
should only be on instance of this game.

So far I have:
Splash screen activity
Menu activity
Game activity

The Game activity is the default activity and it will fire off an
intent with result to start the splash screen activity on the

The splash screen activity loads all the bitmaps required for the
game, once it is finished it will return the result to the game
activity which then knows all the bitmaps are finished.

The Game activity then will fire off an intent to show the Menu

Now since I only ever want one instance of my game. Should I make the
Splash screen, Menu and Game activity all singleTask?

If my Game activity is singleTask and I start a Menu activity that is
singleTask, is the Menu on a seperate task to the Game activity? How
can i make them all in one task?

I guess in my case whenever the Game activity gets an oncreate I can
safely assume I have to start from scratch i.e display splash screen
etc as I cannot assume the bitmaps are in memory.


3. How to log test results in a file? - logcat error

4. Sending calendar events via MMS

5. AudioRecord cannot capture audio on beagle board?

6. Access to opencore audio decoders from application layer.

7. Why does DDMS sometimes only show one line of logs?