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". |