Apparent sqlite bug

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

Apparent sqlite bug

richard parkins
An INSERT statement which fails with no explicit conflict clause appears to throw away a pending SAVEPOINT.
The following sequence demonstrates this behaviour
SAVEPOINT demonstration;
CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
"last name" "TEXT",
"address",
PRIMARY KEY ( "first name", "last name" )
) WITHOUT ROWID;
INSERT INTO "PK" default values;
ROLLBACK TO demonstration;

As expected, the insert fails with Error: NOT NULL constraint, but the ROLLBACK statement then also fails with Error: no such savepoint.
If INSERT is replaced by INSERT OR ABORT (which is supposed to be the default), the ROLLBACK statement does not fail.
Environment:- sqlite 3.9.2 built from the amalgamation, running on UBUNTU Linux 14.04.3 LTS. SQL statements run with sqliteman built from https://github.com/rparkins999/sqliteman.git.

I originally saw this problem running the INSERT inside sqliiteman. If you run sqliteman, create the PK table as shown but omitting the WITHOUT ROWID clause, insert a row of all nulls, and then try using sqliteman's Alter Table function (accessible by right click on the table name) and try to change it to a WITHOUT ROWID table, you'll see no such savepoint: ALTER_TABLE Unable to fetch row. This is caused by sqliteman's internal cleanup algorithm trying to roll back after failing to insert the data from the old table into its newly created WITHOUT ROWID table, and not being able to do so because the savepoint has vanished. Changing INSERT in my code to INSERT OR ABORT makes the rollback work properly.
Richard Parkins
_______________________________________________
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: Apparent sqlite bug

Luuk


On 03-01-16 00:11, richard parkins wrote:

> An INSERT statement which fails with no explicit conflict clause appears to throw away a pending SAVEPOINT.
> The following sequence demonstrates this behaviour
> SAVEPOINT demonstration;
> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
> "last name" "TEXT",
> "address",
> PRIMARY KEY ( "first name", "last name" )
> ) WITHOUT ROWID;
> INSERT INTO "PK" default values;
> ROLLBACK TO demonstration;
>
> As expected, the insert fails with Error: NOT NULL constraint, but the ROLLBACK statement then also fails with Error: no such savepoint.
> If INSERT is replaced by INSERT OR ABORT (which is supposed to be the default), the ROLLBACK statement does not fail.
> Environment:- sqlite 3.9.2 built from the amalgamation, running on UBUNTU Linux 14.04.3 LTS. SQL statements run with sqliteman built from https://github.com/rparkins999/sqliteman.git.
>
> I originally saw this problem running the INSERT inside sqliiteman. If you run sqliteman, create the PK table as shown but omitting the WITHOUT ROWID clause, insert a row of all nulls, and then try using sqliteman's Alter Table function (accessible by right click on the table name) and try to change it to a WITHOUT ROWID table, you'll see no such savepoint: ALTER_TABLE Unable to fetch row. This is caused by sqliteman's internal cleanup algorithm trying to roll back after failing to insert the data from the old table into its newly created WITHOUT ROWID table, and not being able to do so because the savepoint has vanished. Changing INSERT in my code to INSERT OR ABORT makes the rollback work properly.
> Richard Parkins
>


a ROLLBACK was done because of the Error...

compare your code with this example:
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
    ...> "last name" "TEXT",
    ...> "address",
    ...> PRIMARY KEY ( "first name", "last name" )
    ...> ) WITHOUT ROWID;
sqlite> SAVEPOINT demonstration;
sqlite> INSERT INTO "PK" values ("1","1","1");
sqlite> SELECT * FROM "PK";
1|1|1
sqlite> INSERT INTO "PK" default values;
Error: NOT NULL constraint failed: PK.first name
sqlite> SELECT * FROM "PK";
sqlite>
sqlite>



_______________________________________________
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: Apparent sqlite bug

Richard Hipp-3
On 1/3/16, Luuk <[hidden email]> wrote:

>
>
> On 03-01-16 00:11, richard parkins wrote:
>> An INSERT statement which fails with no explicit conflict clause appears
>> to throw away a pending SAVEPOINT.
>> The following sequence demonstrates this behaviour
>> SAVEPOINT demonstration;
>> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
>> "last name" "TEXT",
>> "address",
>> PRIMARY KEY ( "first name", "last name" )
>> ) WITHOUT ROWID;
>> INSERT INTO "PK" default values;
>> ROLLBACK TO demonstration;
>>
>> As expected, the insert fails with Error: NOT NULL constraint, but the
>> ROLLBACK statement then also fails with Error: no such savepoint.
>
> a ROLLBACK was done because of the Error...
>

Yeah, but a constraint failure should only terminate the one statement
that encountered the problem and should *not* rollback the
transaction.

The problem is that the NOT NULL constraint on the PRIMARY KEY of a
WITHOUT ROWID table was defaulting to give ON CONFLICT ROLLBACK
behavior instead of ON CONFLICT ABORT, as it should.  I'm currently
testing the following fix:

Index: src/build.c
==================================================================
--- src/build.c
+++ src/build.c
@@ -1772,11 +1772,11 @@

   /* Make sure every column of the PRIMARY KEY is NOT NULL.  (Except,
   ** do not enforce this for imposter tables.) */
   if( !db->init.imposterTable ){
     for(i=0; i<nPk; i++){
-      pTab->aCol[pPk->aiColumn[i]].notNull = 1;
+      pTab->aCol[pPk->aiColumn[i]].notNull = OE_Abort;
     }
     pPk->uniqNotNull = 1;
   }

   /* The root page of the PRIMARY KEY is the table root page */

Assuming the patch above works, I'll check in the fix in a few minutes...
--
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
|

Re: Apparent sqlite bug

Simon Slavin-3

On 3 Jan 2016, at 5:49pm, Richard Hipp <[hidden email]> wrote:

>   /* Make sure every column of the PRIMARY KEY is NOT NULL.  (Except,
>   ** do not enforce this for imposter tables.) */

I've seen references to imposter tables in the SQLite comments.  What is an imposter table ?

Simon.
_______________________________________________
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: Apparent sqlite bug

Richard Hipp-3
On 1/3/16, Simon Slavin <[hidden email]> wrote:
>
> On 3 Jan 2016, at 5:49pm, Richard Hipp <[hidden email]> wrote:
>
>>   /* Make sure every column of the PRIMARY KEY is NOT NULL.  (Except,
>>   ** do not enforce this for imposter tables.) */
>
> I've seen references to imposter tables in the SQLite comments.  What is an
> imposter table ?
>

An undocumented and unsupported feature that allows two or tables in
the schema to refer to the same b-tree.  This is used by the RBU
extension in order to update indexes and table independently of one
another.
--
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
|

Re: Apparent sqlite bug

Simon Slavin-3

> On 3 Jan 2016, at 6:52pm, Richard Hipp <[hidden email]> wrote:
>
> On 1/3/16, Simon Slavin <[hidden email]> wrote:
>>
>> I've seen references to imposter tables in the SQLite comments.  What is an
>> imposter table ?
>
> An undocumented and unsupported feature that allows two or tables in
> the schema to refer to the same b-tree.  This is used by the RBU
> extension in order to update indexes and table independently of one
> another.

Thanks very much.  I liked the idea suggested by the name.  I can see how that would be useful.

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