|Title:||INSERT into table with two triggers does not terminate|
|Last Modified:||2019-10-21 13:35:17|
|Version Found In:||3.30.0|
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".