[MDEV-3845] Virtual Columns are always NULL is Triggers Created: 2012-11-09  Updated: 2012-11-20  Resolved: 2012-11-20

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0, 5.5.28, 5.3.8, 5.2.12
Fix Version/s: 10.0.1, 5.5.28a, 5.3.10, 5.2.13

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: 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.

 Comments   
Comment by Elena Stepanova [ 2012-11-09 ]

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.

Comment by Oleksandr Byelkin [ 2012-11-13 ]

OK to push

Comment by Igor Babaev [ 2012-11-20 ]

The fix for the bug was pushed into 5.2 (rev 3187).

Generated at Thu Feb 08 06:51:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.