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

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

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            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: