MySQL Bugs: #10136: select returns wrong result when called from stored procedure

Bug #10136	select returns wrong result when called from stored procedure
Submitted:	25 Apr 2005 5:36	Modified:	22 Jun 2005 7:50
Reporter:	Jacek Becla	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.4-beta-standard	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
"select * from theTable" returns incorrect result when called from stored procedure. The problem is related to presence of primary key in theTable - removing the primary key "solves" the problem.

How to repeat:
DROP TABLE IF EXISTS pt;
CREATE TABLE IF NOT EXISTS pt (
    name CHAR(5) NOT NULL PRIMARY KEY,
    val FLOAT NOT NULL
);
INSERT INTO pt VALUES ('AAAAA', 1), ('BBBBB', 2), ('CCCCC', 3);

DROP TABLE IF EXISTS tmp1
CREATE TABLE tmp1 (d date);
INSERT INTO tmp1 VALUES ('2002-12-31'), ('2002-01-02'), ('2002-01-03');

DROP PROCEDURE IF EXISTS pp;

DELIMITER //
CREATE PROCEDURE pp()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE theDate DATE;
  DECLARE theCursor CURSOR FOR SELECT d FROM tmp1 ORDER BY d;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN theCursor;

  REPEAT
    FETCH theCursor INTO theDate;
    IF NOT done THEN
      SELECT * from pt;
    END IF;
  UNTIL done END REPEAT;

  CLOSE theCursor;
END
//
DELIMITER ;

call pp();

The "call pp();" returns:

+-------+-----+
| name  | val |
+-------+-----+
| AAAAA |   1 |
| BBBBB |   2 |
| CCCCC |   3 |
+-------+-----+
3 rows in set (0.00 sec)
 
+-------+-----+
| name  | val |
+-------+-----+
| AAAAA |   3 |
| BBBBB |   3 |
| CCCCC |   3 |
+-------+-----+
3 rows in set (0.00 sec)
 
+-------+-----+
| name  | val |
+-------+-----+
| AAAAA |   3 |
| BBBBB |   3 |
| CCCCC |   3 |
+-------+-----+
3 rows in set (0.00 sec)

while it should return 1,2,3 each time, not only the very first time.

If I remove 'PRIMARY KEY' from the table pt, things work ok.

A simpler test case:

CREATE PROCEDURE pp2()
BEGIN
  DECLARE done INT DEFAULT 3;

  REPEAT
    SELECT * from pt;
    set done = done - 1;
  UNTIL done <= 0 END REPEAT;

END

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26264

pushed to 5.0.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.8 change history; marked as Closed.