|
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
|