Hello,
I have posted a while ago a bug (at least I think that it is a bug)
but only in a very abstract form. Now, I have written a C-program
showing the bug.
Brief description:
An FTS related trigger combination leads to a wrong determination of
the last inserted row id. Triggers that are not related to FTS are
working fine.
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
void OnErrorExit(sqlite3* database, int rc, char* errorMessage)
{
if (rc != SQLITE_OK)
{
if (errorMessage != NULL)
{
fprintf(stderr,"SQL error: %s\n",errorMessage);
sqlite3_free(errorMessage);
} /* if */
sqlite3_close(database);
exit(1);
} /* if */
}
void ShowDatabaseContents(sqlite3* database)
{
char* errorMessage;
char** result;
char* sqlStatement;
int noOfColumns, noOfRows;
int rc;
sqlite3_int64 lastInsertedID;
lastInsertedID = sqlite3_last_insert_rowid(database);
printf("Last inserted ID: %d\n",(int) lastInsertedID);
sqlStatement = "SELECT * FROM Simple;";
rc =
sqlite3_get_table
(database,sqlStatement,&result,&noOfRows,&noOfColumns,&errorMessage);
OnErrorExit(database,rc,errorMessage);
printf("Number of rows: %d\n",noOfRows);
printf("Number of columns: %d\n",noOfColumns);
for (int i=0; i<noOfRows+1; ++i)
{
printf("Row: %2d ",i);
for (int j=0; j<noOfColumns; ++j)
printf(" Column[%d]: %s",j,result[i*noOfColumns+j]);
printf("\n");
} /* for */
}
int main (int argc, const char * argv[])
{
char* errorMessage;
char* sqlStatement;
int rc;
sqlite3* database;
// prepare database
rc = sqlite3_open(NULL,&database);
if (rc != SQLITE_OK)
{
fprintf(stderr,"Can't open database in memory: %s
\n",sqlite3_errmsg(database));
exit(1);
} /* if */
sqlStatement = "CREATE TABLE Simple (ID integer primary key, Name
text);";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
sqlStatement = "CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
sqlStatement = "CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple
FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
sqlStatement = "CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple
FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES
(NEW.ID,NEW.Name); END;";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
// insert and delete items
sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
sqlStatement = "INSERT INTO Simple (Name) VALUES('two');";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
ShowDatabaseContents(database);
sqlStatement = "DELETE FROM Simple WHERE (ID = 1);";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
ShowDatabaseContents(database);
sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
ShowDatabaseContents(database);
sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
OnErrorExit(database,rc,errorMessage);
ShowDatabaseContents(database);
sqlite3_close(database);
printf("Done!\n");
return 0;
}
This is the output after the last insert:
Last inserted ID: 5
Number of rows: 3
Number of columns: 2
Row: 0 Column[0]: ID Column[1]: Name
Row: 1 Column[0]: 2 Column[1]: two
Row: 2 Column[0]: 3 Column[1]: one
Row: 3 Column[0]: 4 Column[1]: one
Actually, I would expect this:
Last inserted ID: 4
Number of rows: 3
Number of columns: 2
Row: 0 Column[0]: ID Column[1]: Name
Row: 1 Column[0]: 2 Column[1]: two
Row: 2 Column[0]: 3 Column[1]: one
Row: 3 Column[0]: 4 Column[1]: one
Interestingly the output is like a like to have it but then I have to
use any trigger but not an FTS related trigger!
Hartwig
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
