Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8099

"WHERE OLD.col" does not work well in triggers

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.0.18
        • Component/s: Triggers
        • Labels:

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                mysql-5.5.31 is also affected.

                Show
                bar Alexander Barkov added a comment - mysql-5.5.31 is also affected.
                Hide
                Permalink
                bar Alexander Barkov added a comment -

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

                Show
                bar Alexander Barkov added a comment - Not a bug. OLD.a is actually 100, the value in table.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: