Bug #108455 Problem with NULL constraints on generated columns if trigger exists
Submitted: 11 Sep 2022 3:37 Modified: 11 Sep 2022 17:35
Reporter: Александр Ммммммм Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[11 Sep 2022 3:37] Александр Ммммммм 
Description:
If trigger exists, the constraint on the generated fields is raised before the trigger is executed

How to repeat:
DROP TABLE IF EXISTS test
;
CREATE TABLE test (col INT, calc_col INT GENERATED ALWAYS AS (1) NOT NULL)
;
INSERT INTO test (col) VALUE (1)
;
DROP TRIGGER IF EXISTS test_b_i
;
CREATE TRIGGER test_b_i BEFORE INSERT ON test FOR EACH ROW SET @aaa = 1
;
INSERT INTO test (col) VALUE (1)
;
[23000][1048] Column 'calc_col' cannot be null
[11 Sep 2022 17:35] MySQL Verification Team 
Thank you for the bug report, verified as described.
[13 Sep 2022 8:11] huahua xu 
Hi,

You can avoid the bug by using INSERT IGNORE Statement.
[15 Sep 2022 2:17] huahua xu 
The bug may be introduced by the commit: https://github.com/mysql/mysql-server/commit/6395567e5cc3e379e42dfa91ab3c66f9edaf5b09