SQLite

View Ticket
Login
Ticket Hash: b41031ea2b5372378cb3d2d43cf9fe2a4a5c2510
Title: ALTER TABLE returns an error on certain schemas
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-09-17 15:29:43
Version Found In: 3.25.0
User Comments:
drh added on 2018-09-16 20:03:33: (text/x-fossil-wiki)
The newly enhanced ALTER TABLE in SQLite 3.25.0 hits an assertion fault when
there are operations on an rtree virtual table in the body of the trigger.

<blockquote><verbatim>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
CREATE VIRTUAL TABLE t2 USING rtree(id,x0,x1);
CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN
   DELETE FROM t2 WHERE id = OLD.a;
END;
ALTER TABLE t1 RENAME TO t3;
</verbatim></blockquote>

drh added on 2018-09-16 20:33:15: (text/x-fossil-wiki)
Further information:

The assert() seems harmless.  But after removing it the ALTER TABLE statement
returns an error.

In version 3.25.0, the ALTER TABLE statement has been enhanced to run a
test-parse of the whole schema after it alters the schema, to make sure that
the edits it performed on the schema didn't break anything.  There is a bug
in this test-parse logic that gives a false positive when a virtual table is
used inside the body of a trigger.

So, the ALTER TABLE statement seemed to work correctly, but the extra sanity
checking logic that runs at the end found a false-positive which causes the
ALTER TABLE to rollback.  This makes ALTER TABLE unusable for schemas that
have triggers in which virtual tables are used in the body of the trigger.
Because the failed ALTER TABLE is run inside a nested transaction which
rollbacks after the failure, the failure does not cause corruption or otherwise
harm the database file in any way.  The ALTER TABLE simply fails to work.