Bug #117419 BEFORE INSERT trigger causes all columns in DEFAULT value expression to be NULL

• Submitted: 10 Feb 2025 4:45
• Modified: 10 Feb 2025 6:54
• Reporter: Robert Mueller
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 8.0.34, 8.0.41, 8.4.4, 9.2.0
• OS: Any
• CPU Architecture: Any

[10 Feb 2025 4:45] Robert Mueller

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.

[10 Feb 2025 6:54] MySQL Verification Team

Hello Robert Mueller,

Thank you for the report and test case.

regards,
Umesh