Description:
When using a DEFAULT expression on a table column, the expression can reference other columns in that table during the insert. However if the table also has any BEFORE INSERT trigger, then the any other referenced columns will be treated as NULL in the expression.
How to repeat:
> create table test (col1 int, col2 int default (col1+1));
> insert into test (col1) values (1);
> select * from test;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
> create trigger testinsert before insert on test for each row set @s = @s + 1;
> insert into test (col1) values (2);
> select * from test;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 2 | NULL |
+------+------+
2 rows in set (0.00 sec)
> drop trigger testinsert;
> insert into test (col1) values (3);
> select * from test;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 2 | NULL |
| 3 | 4 |
+------+------+
3 rows in set (0.00 sec)
Suggested fix:
The mere existence of a trigger should not affect the DEFAULT expression calculation.
