SQLite

View Ticket
Login
2018-09-17
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)
2018-09-16
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)

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:

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:

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.