Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.28, 5.3.8, 5.2.12
-
None
-
None
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.
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.