Details
Major
Description
Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
"Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."
Example:
DROP VIEW IF EXISTS `t1`; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `a` INTEGER UNSIGNED NULL DEFAULT NULL, `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE = MyISAM; DROP VIEW IF EXISTS `t2`; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `c1` INTEGER UNSIGNED NOT NULL ) ENGINE = MyISAM; DELIMITER || CREATE TRIGGER `t1_ins_aft` AFTER INSERT ON `t1` FOR EACH ROW BEGIN INSERT INTO `t2` (`c1`) VALUES (NEW.`b`); END || CREATE TRIGGER `t1_del_bef` BEFORE DELETE ON `t1` FOR EACH ROW BEGIN INSERT INTO `t2` (`c1`) VALUES (OLD.`b`); END || DELIMITER ;
– Then I try those:
INSERT INTO `t1` (`a`) VALUES (1), (2), (3); DELETE FROM t1;
It looks that b is treated as NULL.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
OK to push
The fix for the bug was pushed into 5.2 (rev 3187).
Igor,
Also reproducible with MyISAM and InnoDB (I've changed the test case to MyISAM).
The last two statements indeed attempt to insert NULL into t2.c1 and throw an error because the column is not nullable. If you remove NOT NULL clause and try SELECT * FROM t2 afterwards, all inserted values are NULL.