SQLite

View Ticket
Login
Ticket Hash: 7fc8e5ff25945c1950c1523e9eeab286aa6ea0f2
Title: INSERT into table with two triggers does not terminate
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Not_A_Bug
Last Modified: 2019-10-21 13:35:17
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-10-21 11:26:03:

Consider the following test case:

CREATE TABLE t0(c0, c1, PRIMARY KEY (c0, c1));
INSERT INTO t0(c0) VALUES (0), (1), (2), (3), (4), (5);
CREATE TRIGGER tr1 BEFORE DELETE ON t0 FOR EACH ROW BEGIN
	DELETE FROM t0 WHERE t0.c1;
	INSERT INTO t0(c0) VALUES (6), (7), (8), (9), (10);
END;
CREATE TRIGGER tr0 BEFORE INSERT ON t0 BEGIN
	DELETE FROM t0;
	DELETE FROM t0;
	DELETE FROM t0;
END;
INSERT INTO t0(c1) VALUES (0), (1), (2); -- unexpected: does not terminate

Unexpectedly, the INSERT does not terminate (at least in reasonable time).


dan added on 2019-10-21 13:35:17:

This is a very long-running query, but everything is working as designed. For the first row inserted at the top level, the BEFORE INSERT trigger executes 3 DELETE statements. The first deletes 6 rows, but causes 30 to be inserted. The next deletes 30, but inserts 150. Then the third deletes 150, but inserts 750. Then the next two rows inserted at the top-level cause even more deletes and inserts. The "DELETE FROM t0 WHERE t0.c1" in the BEFORE DELETE trigger doesn't hit any rows, but slows the trigger down (as it requires a linear scan).

There are other ways to cause such combinatorics complexity a 64-way join on tables that each contain more than one row for example.

So, closing this one as "not a bug".