Bram de Jong wrote:
>
> I have found a bug which happens in both FTS2 and FTS3.
>
> The bug happens when a trigger updates an FTS table: the insert ID gets
> trashed:
>
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
> create table one
> (
> id integer not null primary key autoincrement,
> value text not null default ''
> );
>
> create virtual table search using fts2(one_id, data);
>
> create trigger sound_insert after insert on one for each row
> begin
> insert into search (one_id, data) values (new.id, new.value);
> end;
>
> create trigger one_update after update on one for each row
> begin
> update search set data = random() where search.one_id=new.id;
> end;
>
> insert into one (value) values ("hello1");
> select last_insert_rowid(); -- returns 1
> insert into one (value) values ("hello2");
> select last_insert_rowid(); -- returns 2
> update one set value="hello3" where id=1;
> select last_insert_rowid(); -- returns 3, but should return 2
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>
I think both Richard and Scott may have misread this one a little bit.
It seems to me that all the operations Bram has done are using the
normal (i.e. non-virtual table) table, one. All the accesses of the
virtual table search are done inside the trigger routines. SQLite is
supposed to be saving and restoring the last_insert_rowid value for each
trigger execution context. These operation should work as expected
regardless of what is done inside the trigger. An update of a normal
table should not be changing the last inserted rowid value.
Perhaps the problem is simply that the update trigger is not doing the
save and restore operation correctly. In any case it looks like there is
a real problem here.
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
