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

Select statement doesn't behave properly within trigger (Regression from 10.1)

    Details

      Description

      Following this scenario :

       
      CREATE DATABASE bug;
      use bug;
      create table articles(id_ref INT  PRIMARY KEY  NOT NULL AUTO_INCREMENT, price DOUBLE NOT NULL DEFAULT 0);
      create table articles2(id_ref INT  PRIMARY KEY  NOT NULL, price DOUBLE NOT NULL DEFAULT 0);
      create table art(id INT  PRIMARY KEY  NOT NULL AUTO_INCREMENT, id_ref INT NOT NULL);
       
      INSERT INTO articles(price) VALUES(2.3);
      INSERT INTO articles2(id_ref, price) VALUES(-1, 7.3);
       
      CREATE VIEW view_articles as SELECT id_ref, price from articles UNION select id_ref, price from articles2;
       
      DELIMITER //
       
       CREATE TRIGGER `trg_bins_art`
       BEFORE INSERT ON `art`
       FOR EACH ROW
       BEGIN  
       
      DECLARE priceart DOUBLE DEFAULT NULL;
      DECLARE errormsg VARCHAR(255) DEFAULT '';
       
      select price into priceart  
      FROM view_articles
      where view_articles.id_ref = NEW.id_ref LIMIT 1;
       
      IF (priceart is null) THEN
       
        SET errormsg = CONCAT("INSERT ERROR : missing line in view_articles [art ref :  ",  NEW.id_ref ,  "]");
       
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errormsg; 
       
      END IF;
       
      END// 
      

      Expected behavior :

      INSERT INTO art(id_ref) VALUES (3);
      Fail with message : "INSERT ERROR : missing line in view_articles [art ref : 3]".

      INSERT INTO art(id_ref) VALUES(1);
      Works & insert into table art.

      INSERT INTO art(id_ref) VALUES(-1);
      Works & insert into table art.

      Reality :
      all three insert fails with the custom error message;

      Notes :
      => same trigger in same DB worked before migration to 10.4.6.
      => for me sql mode is set to "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION", but I also reproduced with the default of 10.4.6 ( trans_table, error on 0 division )
      => fail with both innoDB or myisam tables

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              Laloutre87 Julien
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: