Description:
A variable name used within a Function definition does not get correctly set by FETCH
cursor INTO when the variable name matches a column name within a table that is
referenced by that cursor.
This occurs with the variable 'thisname' in the SQL in 'How To Repeat' .
The resultset for the 'select' is clearly a single row with a value 99.
but FETCH cur1 INTO thisname
does not set 'thisname'
The second part of the SQL changes the variable 'thisname' to 'anothername' (so that it
no longer clashes with the column name in the table with in the select) and the FETCH
INTO works correctly.
(The problem has nothing to do with the use of a subselect. If you replace the subselect
with a table with a column named 'thisname', you get the same problem, but if you replace
the select with a select that uses no table, the problem goes away - such as 'select 99
as thisname' )
CONSOLE LOG
+=========+
mysql> drop function if exists myfunct
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION myfunct() RETURNS int
-> DETERMINISTIC
-> begin
-> DECLARE thisname int default -1;
-> DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as
thisname) as sub;
-> OPEN cur1;
-> FETCH cur1 INTO thisname ;
-> CLOSE cur1;
-> return thisname;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select version(), myfunct() as test_result
-> //
+-------------------------+-------------+
| version() | test_result |
+-------------------------+-------------+
| 5.0.27-community-nt-log | -1 | <<<<<<<<<<<< WRONG
+-------------------------+-------------+
mysql>
mysql>
mysql> DELIMITER //
mysql> drop function if exists myfunct
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION myfunct() RETURNS int
-> DETERMINISTIC
-> begin
-> DECLARE anothername int default -1;
-> DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as
thisname) as sub;
-> OPEN cur1;
-> FETCH cur1 INTO anothername ;
-> CLOSE cur1;
-> return anothername ;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select version(), myfunct() as test_result
-> //
+-------------------------+-------------+
| version() | test_result |
+-------------------------+-------------+
| 5.0.27-community-nt-log | 99 | <<<<<< RIGHT
+-------------------------+-------------+
1 row in set (0.00 sec)
mysql>
How to repeat:
DELIMITER //
drop function if exists myfunct
//
CREATE FUNCTION myfunct() RETURNS int
DETERMINISTIC
begin
DECLARE thisname int default -1;
DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as
sub;
OPEN cur1;
FETCH cur1 INTO thisname ;
CLOSE cur1;
return thisname;
end;
//
select version(), myfunct() as test_result
//
DELIMITER //
drop function if exists myfunct
//
CREATE FUNCTION myfunct() RETURNS int
DETERMINISTIC
begin
DECLARE anothername int default -1;
DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as
sub;
OPEN cur1;
FETCH cur1 INTO anothername ;
CLOSE cur1;
return anothername ;
end;
//
select version(), myfunct() as test_result
//
Description: A variable name used within a Function definition does not get correctly set by FETCH cursor INTO when the variable name matches a column name within a table that is referenced by that cursor. This occurs with the variable 'thisname' in the SQL in 'How To Repeat' . The resultset for the 'select' is clearly a single row with a value 99. but FETCH cur1 INTO thisname does not set 'thisname' The second part of the SQL changes the variable 'thisname' to 'anothername' (so that it no longer clashes with the column name in the table with in the select) and the FETCH INTO works correctly. (The problem has nothing to do with the use of a subselect. If you replace the subselect with a table with a column named 'thisname', you get the same problem, but if you replace the select with a select that uses no table, the problem goes away - such as 'select 99 as thisname' ) CONSOLE LOG +=========+ mysql> drop function if exists myfunct -> // Query OK, 0 rows affected (0.00 sec) mysql> CREATE FUNCTION myfunct() RETURNS int -> DETERMINISTIC -> begin -> DECLARE thisname int default -1; -> DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as sub; -> OPEN cur1; -> FETCH cur1 INTO thisname ; -> CLOSE cur1; -> return thisname; -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> select version(), myfunct() as test_result -> // +-------------------------+-------------+ | version() | test_result | +-------------------------+-------------+ | 5.0.27-community-nt-log | -1 | <<<<<<<<<<<< WRONG +-------------------------+-------------+ mysql> mysql> mysql> DELIMITER // mysql> drop function if exists myfunct -> // Query OK, 0 rows affected (0.00 sec) mysql> CREATE FUNCTION myfunct() RETURNS int -> DETERMINISTIC -> begin -> DECLARE anothername int default -1; -> DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as sub; -> OPEN cur1; -> FETCH cur1 INTO anothername ; -> CLOSE cur1; -> return anothername ; -> end; -> // Query OK, 0 rows affected (0.00 sec) mysql> select version(), myfunct() as test_result -> // +-------------------------+-------------+ | version() | test_result | +-------------------------+-------------+ | 5.0.27-community-nt-log | 99 | <<<<<< RIGHT +-------------------------+-------------+ 1 row in set (0.00 sec) mysql> How to repeat: DELIMITER // drop function if exists myfunct // CREATE FUNCTION myfunct() RETURNS int DETERMINISTIC begin DECLARE thisname int default -1; DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as sub; OPEN cur1; FETCH cur1 INTO thisname ; CLOSE cur1; return thisname; end; // select version(), myfunct() as test_result // DELIMITER // drop function if exists myfunct // CREATE FUNCTION myfunct() RETURNS int DETERMINISTIC begin DECLARE anothername int default -1; DECLARE cur1 CURSOR FOR SELECT thisname as thatname from (select 99 as thisname) as sub; OPEN cur1; FETCH cur1 INTO anothername ; CLOSE cur1; return anothername ; end; // select version(), myfunct() as test_result //