MySQL Bugs: #28227: Cursor fetches null values into variables

Bug #28227	Cursor fetches null values into variables
Submitted:	4 May 2007 0:54	Modified:	14 May 15:54
Reporter:	Spencer Pratt
Status:	Duplicate
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0.32,5.1.25	OS:	Linux
Assigned to:		Target Version:	6.0
Triage:	Triaged: D2 (Serious)

Description:
Bug #8692 which was reportedly fixed in mysql version 5.0.11 is alive in 5.0.32.

Cursor fetches null value into variables that should contain valid data.  Please see "How
to repeat" section for more information.

How to repeat:
drop database if exists BugTester;
create database BugTester;

use BugTester;

create table BugTable
(
  id int(10) auto_increment not null,

  name varchar(16),

  primary key (id)
) ENGINE=InnoDB default CHARSET=latin1;

insert into BugTable (name) values ('one');
insert into BugTable (name) values ('two');
insert into BugTable (name) values ('three');
insert into BugTable (name) values ('four');
insert into BugTable (name) values ('five');
insert into BugTable (name) values ('six');
insert into BugTable (name) values ('seven');
insert into BugTable (name) values ('eight');
insert into BugTable (name) values ('nine');
insert into BugTable (name) values ('ten');

DELIMITER $$

drop procedure if exists BugProcedure$$
create procedure BugProcedure()
BEGIN
  declare id int(10);
  declare name varchar(16);
  declare numRows integer;
  declare noMoreRows integer;

  declare curs cursor for
  select id, name from BugTable;

  declare continue handler for not found set noMoreRows = 1;
  set noMoreRows = 0;
  set numRows = 0;

  open curs;
  myLoop:loop
    fetch curs into id, name;
    if noMoreRows then
      leave myLoop;
    end if;
    -- do whatever here
  set numRows = numRows + 1;
  select id, numRows, name;
  end loop myLoop;
  close curs;

END$$

DELIMITER ;

------------------------------------------------------------
[May  3, 16:46:46] 1064 shell> mysql < BugTester.sql

[May  3, 16:46:58] 1065 shell> mysql BugTester
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 236
Server version: 5.0.32-Debian_4-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables
    -> ;
+---------------------+
| Tables_in_BugTester |
+---------------------+
| BugTable            |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from BugTable;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  6 | six   |
|  7 | seven |
|  8 | eight |
|  9 | nine  |
| 10 | ten   |
+----+-------+
10 rows in set (0.00 sec)

mysql> call BugProcedure();
+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       1 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       2 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       3 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       4 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       5 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       6 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       7 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       8 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |       9 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

+------+---------+------+
| id   | numRows | name |
+------+---------+------+
| NULL |      10 | NULL |
+------+---------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Suggested fix:
Variables should contain valid data.  Fetch should fetch valid data into the variables.

Thank you for the bug report.

c:\build\5.0>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.42 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database if exists BugTester;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create database BugTester;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> use BugTester;
Database changed
mysql>
mysql> create table BugTable
    -> (
    ->   id int(10) auto_increment not null,
    ->
    ->   name varchar(16),
    ->
    ->   primary key (id)
    -> ) ENGINE=InnoDB default CHARSET=latin1;
Query OK, 0 rows affected (0.51 sec)

mysql>
mysql> insert into BugTable (name) values ('one');
Query OK, 1 row affected (0.07 sec)

mysql> insert into BugTable (name) values ('two');
Query OK, 1 row affected (0.03 sec)

mysql> insert into BugTable (name) values ('three');
Query OK, 1 row affected (0.04 sec)

mysql> insert into BugTable (name) values ('four');
Query OK, 1 row affected (0.04 sec)

mysql> insert into BugTable (name) values ('five');
Query OK, 1 row affected (0.05 sec)

mysql> insert into BugTable (name) values ('six');
Query OK, 1 row affected (0.04 sec)

mysql> insert into BugTable (name) values ('seven');
Query OK, 1 row affected (0.04 sec)

mysql> insert into BugTable (name) values ('eight');
Query OK, 1 row affected (0.05 sec)

mysql> insert into BugTable (name) values ('nine');
Query OK, 1 row affected (0.04 sec)

mysql> insert into BugTable (name) values ('ten');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> drop procedure if exists BugProcedure$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure BugProcedure()
    -> BEGIN
    ->   declare id2 int(10); # see here
    ->   declare name2 varchar(16); # see here
    ->   declare numRows integer;
    ->   declare noMoreRows integer;
    ->
    ->   declare curs cursor for
    ->   select id, name from BugTable;
    ->
    ->   declare continue handler for not found set noMoreRows = 1;
    ->   set noMoreRows = 0;
    ->   set numRows = 0;
    ->
    ->   open curs;
    ->   myLoop:loop
    ->     fetch curs into id2, name2; # see here
    ->     if noMoreRows then
    ->       leave myLoop;
    ->     end if;
    ->     -- do whatever here
    ->   set numRows = numRows + 1;
    ->   select id2, numRows, name2; # see here
    ->   end loop myLoop;
    ->   close curs;
    ->
    -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> DELIMITER ;
mysql> call BugProcedure();
+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    1 |       1 | one   |
+------+---------+-------+
1 row in set (0.07 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    2 |       2 | two   |
+------+---------+-------+
1 row in set (0.08 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    3 |       3 | three |
+------+---------+-------+
1 row in set (0.11 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    4 |       4 | four  |
+------+---------+-------+
1 row in set (0.16 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    5 |       5 | five  |
+------+---------+-------+
1 row in set (0.21 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    6 |       6 | six   |
+------+---------+-------+
1 row in set (0.25 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    7 |       7 | seven |
+------+---------+-------+
1 row in set (0.30 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    8 |       8 | eight |
+------+---------+-------+
1 row in set (0.34 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|    9 |       9 | nine  |
+------+---------+-------+
1 row in set (0.39 sec)

+------+---------+-------+
| id2  | numRows | name2 |
+------+---------+-------+
|   10 |      10 | ten   |
+------+---------+-------+
1 row in set (0.44 sec)

Query OK, 0 rows affected (0.48 sec)

mysql>

This is ridiculous that this is considered not to be a bug. It shouldn't break the Fetch
just because you are using the same local variable names as the column names in the
select cursor. If MySQL ever hopes to become a real database, it should probably up its
standards......somewhere towards usable.

(Using MySQL 5.1.25)
Although it's good practice not to declare variables with same names as fields, it also
shouldn't matter to much. Just like it's good to prefix 'p_' to the procedure
parameters.

However, this problem here gives wrong results, and that's bad.

Here is a more simplified test case. Result is:

mysql> CALL p1();
+------------------+------+
| comment          | id   |
+------------------+------+
| id should be 1:  | NULL | 
+------------------+------+

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1);
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1 ()
BEGIN
	DECLARE id INT;
	DECLARE cur CURSOR FOR SELECT id FROM t1;
	OPEN cur;
	FETCH cur INTO id;
	SELECT 'id should be 1: ' AS comment, id;
END;
//
DELIMITER ;

CALL p1();

Duplicate of Bug#5967