[MDEV-31648] Inconsistant behavior when local variable is declared with the same name as a column within trigger Created: 2023-07-08  Updated: 2023-07-08  Resolved: 2023-07-08

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.6.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Julien Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: select, triggers
Environment:

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.



 Comments   
Comment by Sergei Golubchik [ 2023-07-08 ]

It is assigning it properly and it's not specific to SELECT ... INTO.
The problem is not in the INTO clause, but in the MAX(val) — it interprets the argument as your variable.
If you rewrite the query as

SELECT MAX(t1.val)  INTO val FROM t1 WHERE t1.id = new.idt2;

then you'll get 111, 555

Generated at Thu Feb 08 10:25:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.