Bug #94929 Generated column vs before insert trigger calculation order
Description:
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:
VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns.
MySQL documentation (see above quotes) implies that the value of generated column is calculated after the execution of BEFORE INSERT trigger. In practice, it seems MySQL calculates the value of generated column twice when the BEFORE INSERT trigger is present (before and after the execution of the trigger) - this can be concluded by changing the expression for generated column to (100/IF(c1=0,3,c1)) - the value of c2 will be 50 and not 33 which means c2 was calculated twice (first time divided by 3, second time divided by 2).
For the example under "How to repeat" section:
Actual results: INSERT fails with "Division by zero."
Expected results: INSERT is successful, the value of tbl.c2 is calculated only once.
SQL mode in effect: sql_mode=ANSI,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
How to repeat:
-- sql_mode=ANSI,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
CREATE TABLE tbl (
c1 INT NOT NULL,
c2 INT AS (100/c1), -- VIRTUAL or STORED make no difference here
c3 INT NOT NULL
);
DELIMITER $$
CREATE TRIGGER tbl_before_insert BEFORE INSERT ON tbl FOR EACH ROW BEGIN
SET NEW.c1 = 2;
END$$
DELIMITER ;
INSERT INTO tbl(c3) VALUES(111);
Suggested fix:
The values of generated columns should be calculated only after the execution of BEFORE INSERT triggers.
The order of evaluation of generated columns and triggers should be explicitly documented for both VIRTUAL and STORED generated columns.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.