|15:29||• Fixed ticket [b41031ea]: ALTER TABLE returns an error on certain schemas plus 3 other changes (artifact: fea593b6 user: drh)|
|14:18||Fix a false-positive in the post-ALTER-TABLE schema verification logic that prevents ALTER TABLE from working if the table being altered has a trigger that references any column of a virtual table. Ticket [b41031ea2b537237]. (check-in: 5ce568c0 user: drh tags: branch-3.25)|
|14:13||Fix a false-positive in the post-ALTER-TABLE schema verification logic that prevents ALTER TABLE from working if the table being altered has a trigger that references any column of a virtual table. Ticket [b41031ea2b537237]. (check-in: 84105ea4 user: drh tags: trunk)|
|23:27||First proposed fix for the ALTER TABLE problem described by ticket [b41031ea2b5372378cb3d2d] (check-in: 05a9d129 user: drh tags: tkt-b41031ea)|
|20:33||• Ticket [b41031ea] ALTER TABLE returns an error on certain schemas status still Open with 3 other changes (artifact: 90eeaef7 user: drh)|
|20:33||• Ticket [b41031ea]: 6 changes (artifact: e331b118 user: drh)|
|20:03||• New ticket [b41031ea]. (artifact: 65efc0dd user: drh)|
|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.