Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.14
-
Reproduced on 10.6.14 on linux and windows.
Description
It may be the expected behavior, although it would feel weird to me, but if so the documentation for "select into" is incorrect or incomplete.
The bellow code will display "111, 6" instead of the expected "111, 555" :
CREATE TABLE t1 (id int, val int); |
INSERT INTO t1 (id, val) VALUES (111, 555); |
|
|
CREATE TABLE t2 (idt2 int, valt2 int); |
|
|
DELIMITER //
|
|
CREATE TRIGGER trg_bins_t2 BEFORE INSERT on t2 for each row |
BEGIN
|
|
|
DECLARE val INT DEFAULT 6; |
|
|
SELECT MAX(val) INTO val FROM t1 WHERE t1.id = new.idt2; |
SET new.valt2 = val; |
|
END; |
|
|
//
|
|
|
DELIMITER ;
|
|
|
INSERT INTO t2(idt2, valt2) VALUES (111, 1); |
|
|
SELECT * FROM t2; |
|
|
DROP TABLE t1; |
DROP TABLE t2; |
BUT if renaming the local variable to something else, we get the expected values of 111, 555
CREATE TABLE t1 (id int, val int); |
INSERT INTO t1 (id, val) VALUES (111, 555); |
|
|
CREATE TABLE t2 (idt2 int, valt2 int); |
|
|
DELIMITER //
|
|
CREATE TRIGGER trg_bins_t2 BEFORE INSERT on t2 for each row |
BEGIN
|
|
|
DECLARE othername INT DEFAULT 6; |
|
|
SELECT MAX(val) INTO othername FROM t1 WHERE t1.id = new.idt2; |
SET new.valt2 = othername; |
|
END; |
|
|
//
|
|
|
DELIMITER ;
|
|
|
INSERT INTO t2(idt2, valt2) VALUES (111, 1); |
|
|
SELECT * FROM t2; |
|
|
DROP TABLE t1; |
DROP TABLE t2; |
Moreover, if removing the "DECLARE" line entirely, we get the expected "#1327 - Undeclared variable: val" error, indicating a local variable is expected after the "INTO", even if it's not assigning it properly when a column of the same name exists.