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

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

    XMLWordPrintable

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

            shulga Dmitry Shulga
            Laloutre87 Julien
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.