SQLite

View Ticket
Login
Ticket Hash: 50c09fc2cf0d91ce87a398be534d0d4b23670b37
Title: Crash on REPLACE INTO of a table with an AFTER DELETE trigger.
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-10-24 19:37:01
Version Found In: 3.30.0
User Comments:
drh added on 2019-10-24 19:29:57:

The following script leads to an assertion fault:

PRAGMA recursive_triggers = true;
CREATE TABLE t0(a, b, c UNIQUE);
CREATE UNIQUE INDEX i0 ON t0(b) WHERE a;
CREATE TRIGGER tr0 AFTERE DELETE ON t0 BEGIN
  DELETE FROM t0;
END;
INSERT INTO t0(a,b,c) VALUES(0,0,9),(1,1,1);
REPLACE INTO t0(a,b,c) VALUES(2,0,9);

The problem is that the performance optimization of check-in [bec5b6d4d083556d] (2016-11-08) is subtly incorrect. The idea is sound: it avoids unnecessary seek operations on a cursor if we know that the cursor is unperturbed, and the cursor will be unperturbed if either the INSERT is not a REPLACE or if there are no triggers - either explicit triggers are implicit triggers created by foreign key constraints. The problem is that the conditional only detects INSERT triggers that might fire, and fails to detect DELETE triggers that fire as a consequence of the REPLACE.

The assertion fault was first detected by Manuel Rigger and was reported by private email.


drh added on 2019-10-24 19:33:27:

Typo: "AFTERE" in the demo script should be "AFTER", of course.