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

Bug #28227	Cursor fetches null values into variables
Submitted:	3 May 2007 22:54	Modified:	14 May 2009 13:54
Reporter:	Spencer Pratt	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.32,5.1.25	OS:	Linux
Assigned to:		CPU Architecture:	Any

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

I just lost a whole afternoon because of this issue on mySQL 5.1.58. This is ridiculous that this bug (yes it's a bug) is still not corrected after all this time.

I don't see why it would be bad practice to name the variables that will hold the values of some column to be named the same as the column.

If you insist that it is, then please don't punish your users: document the limitation.

Well, it's 2023, 16 years after this was initially posted, and this is still wasting people's time.  

MySQL 8.0.23

Figure I'm yelling in to the void but wanted to comment as the last one was 10+ years ago.  We as a community still hate this bug.