/ View Ticket
 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".