[MDEV-8099] "WHERE OLD.col" does not work well in triggers Created: 2015-05-04  Updated: 2015-05-05  Resolved: 2015-05-05

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.0.18

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: upstream


 Description   

This script:

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (100);
 
CREATE TABLE t2 (a INT);
 
INSERT INTO t2 VALUES (100);
 
 
 
DROP TRIGGER IF EXISTS tr1;
 
DELIMITER $$
 
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW
 
BEGIN
 
 DELETE FROM t2 WHERE OLD.a;
 
END$$
 
DELIMITER ;
 
SELECT * FROM t1;
 
 
 
SELECT * FROM t2;
 
UPDATE t1 SET a=0;
 
SELECT * FROM t2;

returns one row before UPDATE on t1, and returns empty set after UPDATE on t1.
This looks wrong, because OLD.a should evaluate to 0, and the DELETE query inside the trigger should be equal to:

DELETE FROM t2 WHERE 0;

Thus no rows should be deleted from t2.

 Comments   
Comment by Alexander Barkov [ 2015-05-04 ]

mysql-5.5.31 is also affected.

Comment by Alexander Barkov [ 2015-05-05 ]

Not a bug. OLD.a is actually 100, the value in table.

Generated at Thu Feb 08 07:24:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.