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.
