MySQL Bugs: #56424: some variable names do not play nicely with cursors

Bug #56424	some variable names do not play nicely with cursors
Submitted:	31 Aug 2010 20:51	Modified:	23 Sep 2010 17:06
Reporter:	Adam Robbins-Pianka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	mysql Ver 14.14 Distrib 5.1.45	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	Cursors, names, variables

Description:
Before declaring a cursor, one cannot declare a variable name that is identical to the name of one of the columns in the cursor's SELECT statement (see example code below), regardless of whether or not that variable is actually involved in the procedure at all.  I think this is a bug, but I may be missing something...  If this is the "expected behavior," then it should be documented somewhere.  The code below is commented, hopefully helpfully.

Run the code as-is (without uncommenting that line) to see that the cursor and everything else works as it should at first.  Uncomment that line to see it break...

How to repeat:
create table tstTable (col1 int);
insert into tstTable values (cast(rand()*1000 as unsigned integer)); -- execute this a few times to populate tstTable with a few integers

delimiter $$
drop procedure if exists tstProc $$
create procedure tstProc()
begin
    -- declare col1 int; -- to break this procedure, uncomment this line and create the procedure again
    declare _col1 int;
    declare done tinyint(1) default 0;
    declare curs cursor for select col1 from tstTable;
    declare continue handler for not found set done = 1;
    
    open curs;
    fetch from curs into _col1;
    while done = 0 do
        select _col1;
        fetch from curs into _col1;
    end while;
end $$
delimiter ;

call tstProc();

Suggested fix:
Either document somewhere (preferably here? http://dev.mysql.com/doc/refman/5.0/en/cursors.html) that this is the expected, not-going-to-be-changed behavior and recommend to avoid variable names that are identical to cursor query column names, or lift this limitation of variable naming in a future patch.

Please, check with a newer version, 5.1.50. 

Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.48-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tstTable (col1 int);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into tstTable values (cast(rand()*1000 as unsigned integer));
Query OK, 1 row affected, 1 warning (0.45 sec)

mysql> delimiter $$
mysql> drop procedure if exists tstProc $$
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> create procedure tstProc()
    -> begin
    ->     declare col1 int;
    ->     declare _col1 int;
    ->     declare done tinyint(1) default 0;
    ->     declare curs cursor for select col1 from tstTable;
    ->     declare continue handler for not found set done = 1;
    ->
    ->     open curs;
    ->     fetch from curs into _col1;
    ->     while done = 0 do
    ->         select _col1;
    ->         fetch from curs into _col1;
    ->     end while;
    -> end $$
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> call tstProc();
+-------+
| _col1 |
+-------+
|  NULL |
+-------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

Do I miss something in my test above?

+-------+
     | _col1 |
     +-------+
     |  NULL |
     +-------+
     1 row in set (0.05 sec)
     
     Query OK, 0 rows affected (0.05 sec)
     
     Do I miss something in my test above?

Well, _col1 should not be NULL, assuming tstTable is populated with at least one value.  The result set(s) should have random ingeters.

Now I got it. Local col1 variable is used, not column, when you refer to col1 in cursor. This is clearly documented at http://dev.mysql.com/doc/refman/5.1/en/local-variable-scope.html:

"Local variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. For example, in the following statement, xname is interpreted as a reference to the xname  variable rather than the xname column:

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;

    SELECT xname,id INTO newname,xid
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

When this procedure is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column."

and, thus, is NOT a bug.

Actually, SELECT ... INTO uses implicit cursor, but this priority of local variables over column names should be explicitly documented for cursors also (http://dev.mysql.com/doc/refman/5.0/en/cursors.html and similar pages for other versions).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated http://dev.mysql.com/doc/refman/5.1/en/local-variable-scope.html to include a cursor example that shows the problem:

"
Local variable names should not be the same as column names. If an 
SQL statement, such as a SELECT ... INTO statement, contains a
reference to a column and a declared local variable with the same
name, MySQL currently interprets the reference as the name of a
variable. Consider the following procedure definition:

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;
  
  SELECT xname, id INTO newname, xid
    FROM table1 WHERE xname = xname;
  SELECT newname;
END;

MySQL interprets xname in the SELECT statement as a reference to the
xname variable rather than the xname column. Consequently, when the
procedure sp1()is called, the newname variable returns the value
'bob' regardless of the value of the table1.xname column.
      
Similarly, the cursor definition in the following procedure contains 
a SELECT statement that refers to xname. MySQL interprets this as a
reference to the variable of that name rather than a column
reference. 

CREATE PROCEDURE sp2 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;
  DECLARE done TINYINT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  read_loop: LOOP
    FETCH FROM cur1 INTO newname, xid;
    IF done THEN LEAVE read_loop; END IF;
    SELECT newname;
  END LOOP;
  CLOSE cur1;
END;
"

Also updated these sections to note that variables with the same names as columns should not be used, with a cross reference to the preceding section:

http://dev.mysql.com/doc/refman/5.1/en/cursors.html
http://dev.mysql.com/doc/refman/5.1/en/declare-cursor.html