On May 20, 2008, at 1:21 PM, Bram de Jong wrote:
> Hello all,
>
>
> 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
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>
> My scheme is a bit more complex (more triggers and more tables), but
> this is as far as I could reduce it.
> Notice that if the search table gets updated via a trigger NOT
> installed on the "one" table, the same problem occurs.
>
> I found the problem described here:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg30552.html
> but there was no mention of a fix, nor was there a simple example
> which reproduced the error.
I haven't actually tried to prove it, but it seems likely that this is
because the FTS implementation is doing some INSERTs into shadow
tables when you preform your UPDATE. SQLite in general and virtual
tables in particular tend to be recursive.
The original specification for sqlite3_last_insert_rowid() was that it
"returned the rowid of the most recent INSERT". We later modified the
definition to take triggers into account. So that now we say that
"sqlite3_last_insert_rowid() returns the rowid in the most recent
INSERT from the same or higher level trigger context". Do we need to
further complicate the specification to include virtual tables as
well? What about application-defined functions that invoke SQLite
recursively? Suppose you create an application-defined function named
"eval" that passes its argument back to sqltie3_exec(). Then you do
something like:
INSERT INTO t1 VALUES('INSERT INTO t2 VALUES(5)');
SELECT eval(x) FROM t1;
Should the last-insert-rowid be updated by the INSERT statement this
is invoked recursively by the SELECT statement? If not, how do we
define in a easy-to-understand sentence or two exactly how
sqlite3_last_insert_rowid() really works?
In Bram's case, he was surprised that the last-insert-rowid changed
because he is not thinking about how FTS works behind the scenes. And
this is reasonable. There is a lot of magic in FTS that programmers
are not expected to understand. So it is reasonable for INSERTs done
internally by FTS to not change the last-insert-rowid. But one can
easily imagine other virtual tables that are more transparent and in
which the programmer would expect the last-insert-rowid to be
updated. How do you specify when the last-insert-rowid is updated and
when it is not?
So it is not at all clear to me whether this behavior is a bug or a
feature. SQLite is doing what the documentation says it ought to do.
The question is, should the specification of what SQLite ought to do
change in order to be less surprising to programmers? And if so, what
would that new specification be?
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
