Quantcast

last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

laurent dami
Hi,

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.

Below is a transcript from the sqlite3 shell that illustrates the
problem (but the same bug also happens when using the API).

$ ./sqlite3
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table f5 using fts5(c);
sqlite> insert into f5(c) values('foobar');
sqlite> select last_insert_rowid();
10
sqlite> select rowid, c from f5;
1|foobar

[run on a Windows7 machine]

Best regards, Laurent Dami


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Cezary H. Noweta
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.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Dan Kennedy-4
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Cezary H. Noweta
Hello,

On 2017-02-27 11:41, Dan Kennedy wrote:

>   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.

Indeed, something opposite to the original problem: works (FTS5: not so)
fine until you use transactions.

> Not sure if this is something we can fix or not.

In FTS5 ``*_data'' is updated by ``REPLACE ...''. It is impossible to
disable updating of lastRowid. There are commands OP_Insert with P5 |=
OPFLAG_LASTROWID hardcoded. Such late update is bypassing (or
overwriting) virtual table xUpdate's ``pRowid'' feature.

IMHO, the problem requires redesigning of FTSes or SQLite core (for
example by allowing to disable lastRowid's updating temporarily). I'm
not as fluent in SQLite as to see an ``append-one-line'' solution.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Dan Kennedy-4
On 02/28/2017 12:15 AM, Cezary H. Noweta wrote:

> Hello,
>
> On 2017-02-27 11:41, Dan Kennedy wrote:
>
>>   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.
>
> Indeed, something opposite to the original problem: works (FTS5: not
> so) fine until you use transactions.

I think that might have just been luck. Fts3 writes the database as part
of committing a transaction as well - it just happens to choose the same
rowids for simple examples so it looks like it works.

In any case, should work in 3.18:

   http://www.sqlite.org/src/info/952a3906b30a818e

Dan.





>
>> Not sure if this is something we can fix or not.
>
> In FTS5 ``*_data'' is updated by ``REPLACE ...''. It is impossible to
> disable updating of lastRowid. There are commands OP_Insert with P5 |=
> OPFLAG_LASTROWID hardcoded. Such late update is bypassing (or
> overwriting) virtual table xUpdate's ``pRowid'' feature.
>
> IMHO, the problem requires redesigning of FTSes or SQLite core (for
> example by allowing to disable lastRowid's updating temporarily). I'm
> not as fluent in SQLite as to see an ``append-one-line'' solution.
>
> -- best regards
>
> Cezary H. Noweta
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...