Hi,
actually, I also tested it with 3.6.16 and the bug still exists.
Hartwig
Am 29.06.2009 um 21:54 schrieb Wanadoo Hartwig:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
