Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.6, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
Tested on Linux Debian 9 ( upgrade from 10.1), and fresh install on Windows 7.
64 bits for both.
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