Description:
Hi, I think it's a bug, at least it is very inconsistent and confusing.
The 3 scenarios should return 1 'test me' as a result!
Please notice that the column names in the table are the same
as the variable names declared in the DECLARE statements!
DROP PROCEDURE IF EXISTS `testing_issue`;
DELIMITER $$
CREATE PROCEDURE `testing_issue`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS testing_issue_table;
CREATE TEMPORARY TABLE `testing_issue_table` (
`id` int(10) NOT NULL,
`name` varchar(64) NOT NULL
) ENGINE = HEAP;
INSERT INTO testing_issue_table VALUES(1, 'test me');
BEGIN
DECLARE id INT(10) DEFAULT 100;
DECLARE name VARCHAR(64) DEFAULT 'default me';
DECLARE v_id INT(10) DEFAULT 101;
DECLARE v_name VARCHAR(64) DEFAULT 'V default me';
-- * See scenarios below for declaration of cursor
OPEN cur1;
-- ** See scenarios below for fetching
CLOSE cur1;
END;
DROP TEMPORARY TABLE IF EXISTS testing_issue_table;
END $$
DELIMITER ;
Let's try to compile the SP with different scenarios.
1st:
* DECLARE cur1 CURSOR FOR SELECT id, name FROM testing_issue_table;
** FETCH cur1 INTO id, name;
SELECT id, name;
The result is: 100 'default me'
2nd:
* DECLARE cur1 CURSOR FOR SELECT id, name FROM testing_issue_table;
** FETCH cur1 INTO v_id, v_name;
SELECT v_id, v_name;
The result is: 100 'default me'
3rd:
* DECLARE cur1 CURSOR FOR SELECT * FROM testing_issue_table;
** FETCH cur1 INTO id, name;
SELECT id, name;
The result is: 1 'test me'
Isn't it confusing?
1st:
That tells us that when I declare the cursor, the statement:
SELECT <column name1>, <column name2>, ... FROM <table_name>
is actually
SELECT <local_variable_name1 instead of the column name>, ... FROM <table_name>
because the column names - id, name - I'm expecting to select from the table are the same as the ones declare earlier. If you change the table name with something else that does not exist, the compiler will tell you that the table does not exist, but it won't tell you that id and name are ambiguous.
2nd:
I don't fetch into id and name, but into v_id and v_name, according to 1st that makes sense since id and name are local variables so the compiler uses those instead of the column names.
3rd:
very confusing! why * (meaning all column names from the table testing_issue_table) are not replaced by the local declared variable names id and name? We get the right result on the scenario but after 1st and 2nd I would expect the result to be 100 'default me'.
Some thoughts:
I would expect the SELECT statement for declaring the cursor to be compiled without expanding column names with variable names. If names are the same, declaration should use first names from the column names because that's how SELECT statements are expecting to work, then variable names if it is applicable.
To avoid this:
Anywhere inside the procedure declaration, yes anywhere, never use variable names that could be column names of any table you use inside the procedure. I haven't checked for stored functions!
Guess the output of this below: The result is: 100 'default me'
DROP PROCEDURE IF EXISTS `testing_issue`;
DELIMITER $$
CREATE PROCEDURE `testing_issue`()
BEGIN
DECLARE id INT(10) DEFAULT 100;
DECLARE name VARCHAR(64) DEFAULT 'default me';
-- I can have 300 lines of code here that hide the declaration of id and
-- name.
DROP TEMPORARY TABLE IF EXISTS testing_issue_table;
CREATE TEMPORARY TABLE `testing_issue_table` (
`id` int(10) NOT NULL,
`name` varchar(64) NOT NULL
) ENGINE = HEAP;
INSERT INTO testing_issue_table VALUES(1, 'test me');
BEGIN
DECLARE v_id INT(10) DEFAULT 101;
DECLARE v_name VARCHAR(64) DEFAULT 'V default me';
DECLARE cur1 CURSOR FOR SELECT id, name FROM testing_issue_table;
OPEN cur1;
FETCH cur1 INTO v_id, v_name;
SELECT v_id, v_name;
CLOSE cur1;
END;
DROP TEMPORARY TABLE IF EXISTS testing_issue_table;
END $$
DELIMITER ;
How to repeat:
See above.
Suggested fix:
When DECLARING a cursor, when variable names and column names in the SELECT statement are the same, priority should be on the column names, then variable names.
DECLARE cur1 CURSOR FOR SELECT id, name FROM testing_issue_table;
I'm expecting the cursor to go through all the id and name entries in DB and stuff them into the local variables in the following code:
FETCH cur1 INTO id, name;
SELECT id, name;
The second SELECT makes sense to look in the variable since there is no table provided.
But in that case:
SELECT id, name FROM testing_issue_table;
It should look into the table first.
