On 02/27/2017 05:03 AM, Cezary H. Noweta wrote:
Hello,
While working on the Perl DBD:SQLite driver, I found the following bug
in sqlite : the last_insert_rowid() method (or SQL function) returns the
constant value 10 after any insert into a fts5 virtual table. This bug
is new in fts5 : previous versions fts4 and fts3 returned the correct
rowid value.
This is caused by xSync/xCommit, which updates
f5_data/FTS5_STRUCTURE_ROWID row at the very end of xCommit instead of
at the end of xUpdate. I think, this is due to a performance. As a
temporary solution I'd suggest to disable autocommit. For example, try
to enclose your SQL commands in BEGIN/COMMIT. Until you exec COMMIT,
f5_data is not updated and last_insert_rowid() returns a value set by
``VUpdate'' opcode -- not overwritten by xSync/xCommit updates.
Looks like fts3 has a similar problem:
CREATE VIRTUAL TABLE f USING fts3(x);
BEGIN;
INSERT INTO f VALUES('one');
INSERT INTO f VALUES('two');
INSERT INTO f VALUES('three');
INSERT INTO f VALUES('four');
COMMIT;
INSERT INTO f VALUES('five');
SELECT last_insert_rowid();
The last SELECT statement returns integer value 2, not 5 as you would
expect.
Not sure if this is something we can fix or not.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users