MySQL Bugs: #30163: Name confusion in Fetch Into in a Function defintion

Bug #30163	Name confusion in Fetch Into in a Function defintion
Submitted:	31 Jul 2007 23:21	Modified:	7 Aug 2007 18:50
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt-log, 5.0 BK, 5.1 BK	OS:	Any (fails on Linux and Windows)
Assigned to:	Marc ALFF	CPU Architecture:	Any

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
//

I appreciate that there's an obvious work around (change the variable name!)

but it is very natural (and readable) to use variables with the same name as the columns you are reading them into:

 declare sales cursor for SELECT SKU, UNITS FROM SALES
 ...
  FETCH sales INTO SKU, UNITS

 Since the bug wasted a lot of time to isolate, it's worth fixing the bug so that other people dont waste that time.

Thank you for the report.

Verified as described. Can be related with Bug #5967

"Can be related with Bug #5967"

Yes - I now see that mysql is interpreting the 'thisname' within the SELECT statement as a reference to the declared local variable, so in fact the FETCH cur1 INTO thisname was setting thisname to its own value, rather than not setting it.

Thanks for the pointer.

This is therefore probably not a bug, just a symptom of a design stuck between a rock and a hard place.

A duplicate of Bug#5967