|Title:||ALTER TABLE returns an error on certain schemas|
|Last Modified:||2018-09-17 15:29:43|
|Version Found In:||3.25.0|
drh added on 2018-09-16 20:03:33:
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.
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;
drh added on 2018-09-16 20:33:15:
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.