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>
