title: Incorrect RowId with SQLite using FTS3 on Android 2.2
body: <p>I am experiencing an incorrect rowid value with sqlite and fts3.</p>
  
  <p>Basically, the first two rows are inserted and the rowId is 1 and 2
  respectively. The third insert however returns 4 instead of 3.</p>
  
  <p>I dropped the following log message in my contentprovider when
  inserting a row:</p>
  
  <pre><code>SQLiteDatabase database =
  databaseHelper.getWritableDatabase();
  long rowId = database.insert(NOTES_TABLE, Note.NOTE, values);
  Log.d("NoteProvider", "RowId inserted was " + rowId);
  </code></pre>
  
  <p>The output of that log message is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So what happened to 3?</p>
  
  <p>Also, I exported the sqlite database so I could look at in a SqLite
  browser and the content table shows 1, 2 and 3.</p>
  
  <p>So 3 was created but 4 was returned.</p>
  
  <p>I ran my testing up to 10, and the rowId was sequential afterwards,
  but still off by 1.</p>
  
  <p>The database create script is:</p>
  
  <pre><code>database.execSQL("CREATE VIRTUAL TABLE " + NOTES_TABLE + "
  USING fts3 ("
             + Note.TITLE + ", "
             + Note.NOTE + ", "
             + Note.CREATED_DATE + ", "
             + Note.MODIFIED_DATE + ");");
  </code></pre>
  
  <p>I am assuming I have done something horribly wrong, but cannot figure
  out what it is.
  Can anyone help me out?</p>
  
  <p><strong>Edit 1:</strong><br>
  Did some more testing and have the same results on 2.3.1 and 2.1.<br>
  Although in 2.1 the ids when 1, 3 (skipped 2)</p>
  
  <p><strong>Edit 2:</strong><br>
  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:</p>
  
  <pre><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);
  </code></pre>
  
  <p><strong>Edit 3:</strong><br>
  Looks like another fellow was/is having this problem.
  <a href="https://stackoverflow.com/questions/5011405/android-full-text-search-and-listadapter">Android Full Text Search and ListAdapter</a></p>