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
