Hi Nico,
this
>> Is this really a bug? I at least wouldn't expect last_insert_rowid to be
>> constant if the database gets modified.
>
was more a question for sake of my understanding. Both in the post Simon
referred to
> 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
and in the OP's post
>
> sqlite> CREATE VIRTUAL TABLE fts USING fts4(a,TOKENIZE icu russian);
> sqlite> select last_insert_rowid();
> 0
> sqlite> insert into fts(a) values ('test');
> sqlite> insert into fts(a) values ('test');
> sqlite> select last_insert_rowid();
> 2
> sqlite> delete from fts where rowid=2;
> sqlite> select last_insert_rowid();
> 3
there is some some change to the database after the last insert, and it is this
change that apparently changes the last_insert_rowid. And as I said: neither
would I expect it to change nor would I expect it not to change; I just
wouldn't expect anything here. But there might be something in the SQL specs or
somewhere else that states otherwise.
>
> INSTEAD OF triggers that don't actually insert anything? Then there's
> the re-entrance issue we have in this case. I tend to thing that
> last_insert_rowid() is best avoided because it's an optimization (no
> need to run a query to find what your last statement did) that is not
> needed if you manage your primary keys directly (i.e., don't rely on
> the RDBMS to do autoincrement or any other form of primary key
> allocation).
I don't see the re-entrance issue: isn't last_insert_rowid defined as the ID of
the last row inserted *within the current connection*? (And, as my
understanding, only
valid directly after the INSERT). For your avoidance of last_insert_rowid at
all: do you always generate the primary key client-side? How do you guarantee
uniqueness then?
BTW: last_insert_rowid is not necessarily a query, as it is already reported to
the client by the C-interface.
/eno
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
