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
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 = Aria
ROW_FORMAT = PAGE;
DROP VIEW IF EXISTS `t2`;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c1` INTEGER UNSIGNED NOT NULL
)
ENGINE = Aria
ROW_FORMAT = PAGE;
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 threated as NULL.
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:
{noformat}
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 = Aria
ROW_FORMAT = PAGE;
DROP VIEW IF EXISTS `t2`;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c1` INTEGER UNSIGNED NOT NULL
)
ENGINE = Aria
ROW_FORMAT = PAGE;
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;
{noformat}
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.
Elena Stepanova
added a comment - 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.
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:
{noformat}
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 = Aria
ROW_FORMAT = PAGE;
DROP VIEW IF EXISTS `t2`;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`c1` INTEGER UNSIGNED NOT NULL
)
ENGINE = Aria
ROW_FORMAT = PAGE;
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;
{noformat}
It looks that {{b}} is treated as {{NULL}}.
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:
{noformat}
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 ;
{noformat}
-- Then I try those:
{noformat}
INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
DELETE FROM t1;
{noformat}
Bug
Major
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.