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

UPDATE + JOIN + TRIGGERS = table doesn't exists error

    Details

      Description

      After upgrading from 10.3.14 to 10.3.15 we are getting errors "Table xy doesn't exists".

      Test case:

      CREATE TABLE `a` (`id` INT NOT NULL, `x` INT NOT NULL DEFAULT 1, PRIMARY KEY (`id`));
      CREATE TABLE `b` (`id` INT NOT NULL, `x` INT NOT NULL DEFAULT 1, PRIMARY KEY (`id`));
      CREATE TABLE `c` (`id` INT NOT NULL, `x` INT NOT NULL DEFAULT 1, PRIMARY KEY (`id`));
      CREATE TABLE `d` (`id` INT NOT NULL, `x` INT NOT NULL DEFAULT 1, PRIMARY KEY (`id`));
      INSERT INTO `a` (`id`, `x`) VALUES ('1', '1');
      INSERT INTO `b` (`id`, `x`) VALUES ('1', '1');
      INSERT INTO `c` (`id`, `x`) VALUES ('1', '1');
      INSERT INTO `d` (`id`, `x`) VALUES ('1', '1');
       
      DELIMITER $$
      CREATE DEFINER = CURRENT_USER TRIGGER `a_AFTER_UPDATE` AFTER UPDATE ON `a` FOR EACH ROW
      BEGIN
          UPDATE c SET c.x = c.x + NEW.x-OLD.x WHERE c.id=NEW.id;
      END$$
       
      CREATE DEFINER = CURRENT_USER TRIGGER `c_BEFORE_UPDATE` BEFORE UPDATE ON `c` FOR EACH ROW
      BEGIN
      DECLARE y INT;
          SET y = (SELECT x FROM d WHERE d.id=NEW.id);
          SET NEW.x = NEW.x + y;
      END$$
      DELIMITER ;
       
      UPDATE a JOIN b ON a.id=b.id SET a.x=a.x+b.x WHERE a.id=1;
      

      • 10.3.14 works
      • 10.3.15 doesn't work
      • UPDATE a JOIN b ON a.id=b.id SET a.x=a.x+b.x WHERE a.id=1; ERROR
      • UPDATE a SET a.x=a.x+(SELECT b.x FROM b WHERE b.id=1) WHERE a.id=1; WORKS

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                PavelCibulka PavelCibulka
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: