Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Daniel Kraft
Hi!

It seems to me that the session extension is broken in a situation that
involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but
not if only one of those is used).  Note that I'm using SQLite version
3.26.0 (> 3.17.0), so the session extension should work also for my
WITHOUT ROWID table.

My test code is attached (it uses the Google logging library in addition
to SQLite3 with session extension).  This code creates a WITHOUT ROWID
table with an INTEGER PRIMARY KEY and puts in some initial data.  Then
it uses an "INSERT OR REPLACE" statement to update the initial row and
insert a new one, recording this change into a changeset.  Finally, it
inverts and applies the changeset, so that we should get back to the
initial data.

However, when I build and run the code as it is, then the table will be
*empty* in the end (instead of containing the initial data pair (100,
1)).  It works as expected if I make one of the following two changes:

a) Commenting out the "WITHOUT ROWID" line, or
b) using "UPDATE" and "INSERT" statements (as in the comment in the
code) instead of one "INSERT OR REPLACE" statement.

Am I doing something wrong here, or is this a bug?  Is there something I
can do to make this work as expected?

Thanks!  Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou

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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Daniel Kraft
Since it seems that the mailing list swallowed my attached example code,
I've put it on Github as well:

https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1

On 06.01.19 14:08, Daniel Kraft wrote:

> Hi!
>
> It seems to me that the session extension is broken in a situation that
> involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but
> not if only one of those is used).  Note that I'm using SQLite version
> 3.26.0 (> 3.17.0), so the session extension should work also for my
> WITHOUT ROWID table.
>
> My test code is attached (it uses the Google logging library in addition
> to SQLite3 with session extension).  This code creates a WITHOUT ROWID
> table with an INTEGER PRIMARY KEY and puts in some initial data.  Then
> it uses an "INSERT OR REPLACE" statement to update the initial row and
> insert a new one, recording this change into a changeset.  Finally, it
> inverts and applies the changeset, so that we should get back to the
> initial data.
>
> However, when I build and run the code as it is, then the table will be
> *empty* in the end (instead of containing the initial data pair (100,
> 1)).  It works as expected if I make one of the following two changes:
>
> a) Commenting out the "WITHOUT ROWID" line, or
> b) using "UPDATE" and "INSERT" statements (as in the comment in the
> code) instead of one "INSERT OR REPLACE" statement.
>
> Am I doing something wrong here, or is this a bug?  Is there something I
> can do to make this work as expected?
>
> Thanks!  Yours,
> Daniel
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Tim Streater-3
On 06 Jan 2019, at 13:11, Daniel Kraft <[hidden email]> wrote:

> Since it seems that the mailing list swallowed my attached example code,
> I've put it on Github as well:

Attachments are explicitly disallowed on this list.


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

Re: Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Luuk
In reply to this post by Daniel Kraft

On 6-1-2019 14:11, Daniel Kraft wrote:
> Since it seems that the mailing list swallowed my attached example code,
> I've put it on Github as well:
>
> https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1
>
>
The example code (from github.com), so no-one has to go-to github:

#include <sqlite3.h>

#include <glog/logging.h>

#include <cstdlib>
#include <iostream>

int
printResults (void*, int numColumns, char** values, char** columns)
{
   for (int i = 0; i < numColumns; ++i)
     std::cout << "  " << columns[i] << " = " << values[i];
   std::cout << std::endl;

   return 0;
}

int
abortOnConflict (void* ctx, int conflict, sqlite3_changeset_iter* it)
{
   LOG (ERROR) << "Changeset application has a conflict of type " << conflict;
   return SQLITE_CHANGESET_ABORT;
}

int
main (int argc, char** argv)
{
   LOG (INFO)
       << "Using SQLite version " << SQLITE_VERSION
       << " (library version: " << sqlite3_libversion () << ")";
   CHECK_EQ (SQLITE_VERSION_NUMBER, sqlite3_libversion_number ())
       << "Mismatch between header and library SQLite versions";

   sqlite3* db;
   CHECK_EQ (sqlite3_open (":memory:", &db), SQLITE_OK);

   /* Create schema (table WITHOUT ROWID) and insert some initial data.  */
   CHECK_EQ (sqlite3_exec (db, R"(
     CREATE TABLE `test` (
       `id` INTEGER PRIMARY KEY,
       `value` INTEGER
     )
     WITHOUT ROWID
     ;
     INSERT INTO `test` (`id`, `value`) VALUES (100, 1);
   )", nullptr, nullptr, nullptr), SQLITE_OK);

   /* Make a modification and record a changeset for it.  */
   sqlite3_session* session;
   CHECK_EQ (sqlite3session_create (db, "main", &session), SQLITE_OK);
   CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK);
   CHECK_EQ (sqlite3_exec (db, R"(
     -- UPDATE `test` SET `value` = 2 WHERE `id` = 100;
     -- INSERT INTO `test` (`id`, `value`) VALUES (200, 3);
     INSERT OR REPLACE INTO `test`
       (`id`, `value`) VALUES (100, 2), (200, 3)
   )", nullptr, nullptr, nullptr), SQLITE_OK);
   int changeSize;
   void* changeBytes;
   CHECK_EQ (sqlite3session_changeset (session, &changeSize, &changeBytes),
             SQLITE_OK);
   sqlite3session_delete (session);

   /* Apply the inverted changeset to revert the change.  */
   int invertedSize;
   void* invertedBytes;
   CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes,
                                      &invertedSize, &invertedBytes),
             SQLITE_OK);
   CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes, nullptr,
                                     &abortOnConflict, nullptr),
             SQLITE_OK);
   sqlite3_free (changeBytes);
   sqlite3_free (invertedBytes);

   /* Print contents of table for debugging.  */
   std::cout << "Final values of table:" << std::endl;
   CHECK_EQ (sqlite3_exec (db, R"(
     SELECT * FROM `test`
   )", &printResults, nullptr, nullptr), SQLITE_OK);

   sqlite3_close (db);
   return EXIT_SUCCESS;
}

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

Re: Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Dan Kennedy-4
In reply to this post by Daniel Kraft
On 01/06/2019 08:08 PM, Daniel Kraft wrote:

> Hi!
>
> It seems to me that the session extension is broken in a situation that
> involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but
> not if only one of those is used).  Note that I'm using SQLite version
> 3.26.0 (> 3.17.0), so the session extension should work also for my
> WITHOUT ROWID table.
>
> My test code is attached (it uses the Google logging library in addition
> to SQLite3 with session extension).  This code creates a WITHOUT ROWID
> table with an INTEGER PRIMARY KEY and puts in some initial data.  Then
> it uses an "INSERT OR REPLACE" statement to update the initial row and
> insert a new one, recording this change into a changeset.  Finally, it
> inverts and applies the changeset, so that we should get back to the
> initial data.
>
> However, when I build and run the code as it is, then the table will be
> *empty* in the end (instead of containing the initial data pair (100,
> 1)).  It works as expected if I make one of the following two changes:
>
> a) Commenting out the "WITHOUT ROWID" line, or
> b) using "UPDATE" and "INSERT" statements (as in the comment in the
> code) instead of one "INSERT OR REPLACE" statement.
>
> Am I doing something wrong here, or is this a bug?


I think it was a bug. Thanks for reporting it. Now fixed here:

   https://www.sqlite.org/src/info/6281ef974c0ac7a7

Dan.




  Is there something I

> can do to make this work as expected?
>
> Thanks!  Yours,
> Daniel
>
>
>
> _______________________________________________
> 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
|

Re: Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Daniel Kraft
Hi Dan!

On 07.01.19 16:58, Dan Kennedy wrote:
> I think it was a bug. Thanks for reporting it. Now fixed here:
>
>   https://www.sqlite.org/src/info/6281ef974c0ac7a7

Cool, thanks for confirming it was indeed a bug (and not my stupidity),
and the quick fix!

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


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

signature.asc (849 bytes) Download Attachment