`after update` triggers that reference updated/triggered table use rows from table pre-update
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
`after update` triggers that reference updated/triggered table use rows from table pre-update, even though we _want_ to be using rows from the table after the update.
The following queries in MySQL have these respective results:
```
mysql> create table t1 (i int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)
mysql> create table t2 (i int);
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger trig after update on t1 for each row insert into t2 select i from t1;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set i = 10 * i;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from t1;
+----+
| i |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+
| i |
+------+
| 2 |
| 3 |
| 10 |
| 3 |
| 10 |
| 20 |
| 10 |
| 20 |
| 30 |
+------+
9 rows in set (0.00 sec)
```
However, in Dolt, `select * from t2` returns the following:
```
tmp/main*> select * from t2;
+---+
| i |
+---+
| 2 |
| 2 |
| 2 |
| 1 |
| 1 |
| 1 |
| 3 |
| 3 |
| 3 |
+---+
9 rows in set (0.00 sec)
```
I originally thought this was scoping issue with the analyzer, but it seems like it's an issue with the rowexec iterators.
Based on our discussions on Discord, we need to flush updates to the session's working set before a statement concludes if a relevant trigger is executing. Right now, we cache the results of table writes in the editor until the statement concludes; this is for both speed and correctness. If we flushed updates immediately ,we would need to roll them back if a later update or trigger call failed. We basically need an unoptimized table edit path that is invoked conditionally on whether a trigger will need the table update sooner and then deal with session state management.
We uncovered this issue while investigating #10175. It also affects triggers that reference views that reference the updated/triggered table. This issue may be related to #4495. We likely have similar bugs when it comes to `after insert` and `after delete` triggers that also reference the triggered table.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.