last_insert_rowid and FTS in 3.17

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

last_insert_rowid and FTS in 3.17

Hugo Beauzée-Luyssen
Hi,


Hi, I'm having some issues with last_insert_rowid starting with 3.17
Basically it seems to be returning the row inserted by a trigger,
instead of the explicitly inserted row.
As far as I understand, this contradicts the last_insert_rowid()
documentation.

I wrote a small test case to demonstrate the issue:

chouquette@nibbler cat /tmp/test.sql
CREATE TABLE IF NOT EXISTS Foo(
    id_foo INTEGER PRIMARY KEY AUTOINCREMENT,
    bar TEXT
    );

CREATE VIRTUAL TABLE IF NOT EXISTS FooFts
USING FTS4(bar);

CREATE TRIGGER IF NOT EXISTS insert_fts
AFTER INSERT ON Foo
WHEN new.bar IS NOT NULL
    BEGIN
        INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo, new.bar);
    END;


BEGIN;
    INSERT INTO Foo(id_foo) VALUES(NULL);
    SELECT last_insert_rowid();
    INSERT INTO Foo(id_foo) VALUES(NULL);
    SELECT last_insert_rowid();
COMMIT;
    SELECT last_insert_rowid();
   
    BEGIN;
    INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter");
    COMMIT;
    SELECT last_insert_rowid();

SELECT * FROM sqlite_sequence;

chouquette@nibbler ./sqlite3 --version
3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
chouquette@nibbler ./sqlite3 < /tmp/test.sql
1
2
2
3
Foo|3

chouquette@nibbler ~/dev/prefix/bin/sqlite3 --version
3.17.0 2017-02-13 16:02:40 ada05cfa86ad7f5645450ac7a2a21c9aa6e57d2c
chouquette@nibbler ~/dev/prefix/bin/sqlite3 < /tmp/test.sql
1
2
2
1
Foo|3

Has the behavior changed without being documented (or did I miss the
change?), or is this indeed a bug?

Thanks a lot in advance,

Regards,


--
  Hugo Beauzée-Luyssen
  [hidden email]
_______________________________________________
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 and FTS in 3.17

Richard Hipp-3
On 3/15/17, Hugo Beauzée-Luyssen <[hidden email]> wrote:
> Hi, I'm having some issues with last_insert_rowid starting with 3.17
> Basically it seems to be returning the row inserted by a trigger,
> instead of the explicitly inserted row.

Please try the latest pre-release snapshot at
https://www.sqlite.org/download.html and let us know whether or not it
fixes your problem.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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 and FTS in 3.17

Keith Medcalf

Head of trunk certainly fixes it ...

SQLite version 3.18.0 2017-03-15 19:11:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select sqlite_source_id();
2017-03-15 19:11:29 b1b1aa8b69aa80c83aec3380565f0b4ec0b6a6e033537becee098872da362e9a
sqlite> CREATE TABLE IF NOT EXISTS Foo(
   ...>     id_foo INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>     bar TEXT
   ...>     );
sqlite>
sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS FooFts
   ...> USING FTS4(bar);
sqlite>
sqlite> CREATE TRIGGER IF NOT EXISTS insert_fts
   ...> AFTER INSERT ON Foo
   ...> WHEN new.bar IS NOT NULL
   ...>     BEGIN
   ...>         INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo, new.bar);
   ...>     END;
sqlite>
sqlite>
sqlite> BEGIN;
sqlite>     INSERT INTO Foo(id_foo) VALUES(NULL);
sqlite>     SELECT last_insert_rowid();
1
sqlite>     INSERT INTO Foo(id_foo) VALUES(NULL);
sqlite>     SELECT last_insert_rowid();
2
sqlite> COMMIT;
sqlite>     SELECT last_insert_rowid();
2
sqlite>
sqlite>     BEGIN;
sqlite>     INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter");
sqlite>     COMMIT;
sqlite>     SELECT last_insert_rowid();
3
sqlite>
sqlite> SELECT * FROM sqlite_sequence;
Foo|3
sqlite>
sqlite>

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Richard Hipp
> Sent: Wednesday, 15 March, 2017 18:01
> To: SQLite mailing list
> Subject: Re: [sqlite] last_insert_rowid and FTS in 3.17
>
> On 3/15/17, Hugo Beauzée-Luyssen <[hidden email]> wrote:
> > Hi, I'm having some issues with last_insert_rowid starting with 3.17
> > Basically it seems to be returning the row inserted by a trigger,
> > instead of the explicitly inserted row.
>
> Please try the latest pre-release snapshot at
> https://www.sqlite.org/download.html and let us know whether or not it
> fixes your problem.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: last_insert_rowid and FTS in 3.17

Hugo Beauzée-Luyssen
On Thu, Mar 16, 2017, at 03:25 AM, Keith Medcalf wrote:

>
> Head of trunk certainly fixes it ...
>
> SQLite version 3.18.0 2017-03-15 19:11:29
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select sqlite_source_id();
> 2017-03-15 19:11:29
> b1b1aa8b69aa80c83aec3380565f0b4ec0b6a6e033537becee098872da362e9a
> sqlite> CREATE TABLE IF NOT EXISTS Foo(
>    ...>     id_foo INTEGER PRIMARY KEY AUTOINCREMENT,
>    ...>     bar TEXT
>    ...>     );
> sqlite>
> sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS FooFts
>    ...> USING FTS4(bar);
> sqlite>
> sqlite> CREATE TRIGGER IF NOT EXISTS insert_fts
>    ...> AFTER INSERT ON Foo
>    ...> WHEN new.bar IS NOT NULL
>    ...>     BEGIN
>    ...>         INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo,
>    new.bar);
>    ...>     END;
> sqlite>
> sqlite>
> sqlite> BEGIN;
> sqlite>     INSERT INTO Foo(id_foo) VALUES(NULL);
> sqlite>     SELECT last_insert_rowid();
> 1
> sqlite>     INSERT INTO Foo(id_foo) VALUES(NULL);
> sqlite>     SELECT last_insert_rowid();
> 2
> sqlite> COMMIT;
> sqlite>     SELECT last_insert_rowid();
> 2
> sqlite>
> sqlite>     BEGIN;
> sqlite>     INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter");
> sqlite>     COMMIT;
> sqlite>     SELECT last_insert_rowid();
> 3
> sqlite>
> sqlite> SELECT * FROM sqlite_sequence;
> Foo|3
> sqlite>
> sqlite>
>
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of Richard Hipp
> > Sent: Wednesday, 15 March, 2017 18:01
> > To: SQLite mailing list
> > Subject: Re: [sqlite] last_insert_rowid and FTS in 3.17
> >
> > On 3/15/17, Hugo Beauzée-Luyssen <[hidden email]> wrote:
> > > Hi, I'm having some issues with last_insert_rowid starting with 3.17
> > > Basically it seems to be returning the row inserted by a trigger,
> > > instead of the explicitly inserted row.
> >
> > Please try the latest pre-release snapshot at
> > https://www.sqlite.org/download.html and let us know whether or not it
> > fixes your problem.
> > --
> > D. Richard Hipp
> > [hidden email]
> > _______________________________________________

Indeed it does fix it!
Do you have a rough estimate on a 3.17.1 or 3.18 release?

Thanks a lot,

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