Description:
As the below mysql-test-run code demonstrates, If you create a procedure such that one of the procedure parameters has the same name as one of the column names used in a cursor declaration, then when the cursor is opened, and a fetch is executed, the data is not retrieved.
This is a problem even though the fetch was asked to copy the data from the results set to a local variable first which actually does have a different name.
How to repeat:
connect (root, localhost, root,,);
CREATE DATABASE procDB;
CREATE TABLE procDB.t1 (some_name CHAR(64));
INSERT INTO procDB.t1 VALUES ('this is test record one'),
('this is test record three'),
('this is test record four');
DELIMITER |;
CREATE PROCEDURE procDB.bad(INOUT some_name CHAR(30))
BEGIN
DECLARE pname CHAR(30);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT some_name FROM procDB.t1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN c;
WHILE NOT done DO
FETCH c INTO pname;
SELECT pname;
SET some_name=pname;
END WHILE;
CLOSE c;
END|
CREATE PROCEDURE procDB.good(INOUT something_else CHAR(30))
BEGIN
DECLARE pname CHAR(30);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR SELECT some_name FROM procDB.t1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN c;
WHILE NOT done DO
FETCH c INTO pname;
SELECT pname;
SET something_else=pname;
END WHILE;
CLOSE c;
END|
DELIMITER ;|
SET @name='init1';
CALL procDB.bad(@name);
SELECT @name;
SET @name='init2';
CALL procDB.good(@name);
SELECT @name;
DROP TABLE procDB.t1;
DROP PROCEDURE procDB.good;
DROP PROCEDURE procDB.bad;
DROP DATABASE procDB;
## OUTPUT #######
+ CALL procDB.bad(@name);
+ pname
+ init1
+ pname
+ init1
+ pname
+ init1
+ pname
+ init1
+ SELECT @name;
+ @name
+ init1
+ SET @name='init2';
+ CALL procDB.good(@name);
+ pname
+ this is test record one
+ pname
+ this is test record three
+ pname
+ this is test record four
+ pname
+ this is test record four
+ SELECT @name;
+ @name
+ this is test record four
Suggested fix:
It doesn't really seem like there should be a namespace violation in this case, but if there is and it can't be resolved, then we really need a specific error message to flag the problem when the CREATE PROCEDURE statement is parsed. Otherwise it's a very difficult problem to debug.
Description: As the below mysql-test-run code demonstrates, If you create a procedure such that one of the procedure parameters has the same name as one of the column names used in a cursor declaration, then when the cursor is opened, and a fetch is executed, the data is not retrieved. This is a problem even though the fetch was asked to copy the data from the results set to a local variable first which actually does have a different name. How to repeat: connect (root, localhost, root,,); CREATE DATABASE procDB; CREATE TABLE procDB.t1 (some_name CHAR(64)); INSERT INTO procDB.t1 VALUES ('this is test record one'), ('this is test record three'), ('this is test record four'); DELIMITER |; CREATE PROCEDURE procDB.bad(INOUT some_name CHAR(30)) BEGIN DECLARE pname CHAR(30); DECLARE done INT DEFAULT 0; DECLARE c CURSOR FOR SELECT some_name FROM procDB.t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN c; WHILE NOT done DO FETCH c INTO pname; SELECT pname; SET some_name=pname; END WHILE; CLOSE c; END| CREATE PROCEDURE procDB.good(INOUT something_else CHAR(30)) BEGIN DECLARE pname CHAR(30); DECLARE done INT DEFAULT 0; DECLARE c CURSOR FOR SELECT some_name FROM procDB.t1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN c; WHILE NOT done DO FETCH c INTO pname; SELECT pname; SET something_else=pname; END WHILE; CLOSE c; END| DELIMITER ;| SET @name='init1'; CALL procDB.bad(@name); SELECT @name; SET @name='init2'; CALL procDB.good(@name); SELECT @name; DROP TABLE procDB.t1; DROP PROCEDURE procDB.good; DROP PROCEDURE procDB.bad; DROP DATABASE procDB; ## OUTPUT ####### + CALL procDB.bad(@name); + pname + init1 + pname + init1 + pname + init1 + pname + init1 + SELECT @name; + @name + init1 + SET @name='init2'; + CALL procDB.good(@name); + pname + this is test record one + pname + this is test record three + pname + this is test record four + pname + this is test record four + SELECT @name; + @name + this is test record four Suggested fix: It doesn't really seem like there should be a namespace violation in this case, but if there is and it can't be resolved, then we really need a specific error message to flag the problem when the CREATE PROCEDURE statement is parsed. Otherwise it's a very difficult problem to debug.