MySQL Bugs: #61392: CONTINUE HANDLER FOR NOT FOUND being triggered from internal stored function

Bug #61392	CONTINUE HANDLER FOR NOT FOUND being triggered from internal stored function
Submitted:	2 Jun 2011 21:05	Modified:	31 Oct 2011 14:13
Reporter:	David Poor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.14, 5.5.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	continue handler, stored function

Description:
I have a stored function that collects data and builds a concatenated string that has been working without failure.  Having upgraded to version 5.5.10, the function returns no data.  

After quite a bit of testing, the error comes from the CONTINUE HANDLER FOR NOT FOUND.  In my function, I use a query to get a number of records and then use a cursor to traverse those records.  In the initial query, I call another function that does a query that may fail (no data match requirements) in which case it returns a null.  

In version 5.5.10, if the second query fails anywhere, the CONTINUE HANDLER FOR NOT FOUND for the calling function is triggered so that no records are processed. 

In the sample data, the same functions can be run in either of two modes.  In the first, "select testCursorHandler(0);", the internal function is called with valid numbers for all four rows so that it never fails.  In this case, the concatenated data are properly returned. 

In the second, "select testCursorHandler(1);", the internal function is called with variable data such that the THIRD record will return a null.  In this instance, the REPEAT  FETCH cur1... process fails to traverse the records because the "done" variable has already been set by the CONTINUE HANDLER.  (NOTE that in earlier versions, the records were properly traversed and the CONTINUE HANDLER did not trigger until all of the records had been processed.) 

How to repeat:
Build the following test2 table and the two stored functions.  
Then run the outer test function twice: 

select testCursorHandler(0);
select testCursorHandler(1);

The second should return "1(0), 2(0), ---(0), 4(0), 4(1), ".  If it returns "1(1), ", you have replicated the bug. 

===============================================================

CREATE TABLE `test2` (
  `RecNum` int(3) NOT NULL AUTO_INCREMENT,
  `TableValue` varchar(3) DEFAULT NULL,
  UNIQUE KEY `RecNum` (`RecNum`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `test2` */

insert  into `test2`(`RecNum`,`TableValue`) values (1,'1');
insert  into `test2`(`RecNum`,`TableValue`) values (2,'2');
insert  into `test2`(`RecNum`,`TableValue`) values (3,'-1');
insert  into `test2`(`RecNum`,`TableValue`) values (4,'4');

DELIMITER $$

CREATE FUNCTION `returnFromQuery`(param int) RETURNS varchar(5) CHARSET latin1
BEGIN
    DECLARE newValue  varchar(5) default null;
    Select TableValue FROM test2 where RecNum=param INTO newValue;
    RETURN newValue;
    END */$$

CREATE FUNCTION `testCursorHandler`(doCall int) RETURNS varchar(200) CHARSET latin1
    READS SQL DATA
BEGIN
        DECLARE returnValue VARCHAR(200) DEFAULT ""; 
        DECLARE newValue    varchar(3);
        DECLARE done INT DEFAULT 0;
        DECLARE cur1 CURSOR FOR 
          select  IF(doCall=0,returnFromQuery(RecNum),returnFromQuery(TableValue)) as testValue
          from test2
          order by RecNum;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
        IF done=0 THEN  
           OPEN cur1;
           REPEAT
              FETCH cur1 INTO newValue;
              set newValue=IFNULL(newValue,"---");
              set returnValue=CONCAT(returnValue,newvalue,"(",done,"), ");
           UNTIL done END REPEAT;
           CLOSE cur1;
        END IF; 
        RETURN returnValue;
    END */$$
DELIMITER ;

select testCursorHandler(0);
/* For both 5.0.51a and 5.5.10, correct results returned */
/* returns "1(0), 2(0), -1(0), 4(0), 4(1), " */

select testCursorHandler(0);
/* For 5.0.51a, processes all records properly */
/* returns "1(0), 2(0), ---(0), 4(0), 4(1), " */

/* For 5.5.10: ERROR: sets NOT FOUND HANDLER on first record although first record data properly returned */
/* returns "1(1), "  <== stops because "done" was set to 1 showing past end of record set */

Thank you for the bug report. Verified with current mysql-5.5 from bzr on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
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 16
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE `test2` (
    ->   `RecNum` int(3) NOT NULL AUTO_INCREMENT,
    ->   `TableValue` varchar(3) DEFAULT NULL,
    ->   UNIQUE KEY `RecNum` (`RecNum`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql> insert  into `test2`(`RecNum`,`TableValue`) values (1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert  into `test2`(`RecNum`,`TableValue`) values (2,'2');
Query OK, 1 row affected (0.00 sec)

mysql> insert  into `test2`(`RecNum`,`TableValue`) values (3,'-1');
Query OK, 1 row affected (0.00 sec)

mysql> insert  into `test2`(`RecNum`,`TableValue`) values (4,'4');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> delimiter $$
mysql> CREATE FUNCTION `returnFromQuery`(param int) RETURNS varchar(5) CHARSET latin1
    -> BEGIN
    ->     DECLARE newValue  varchar(5) default null;
    ->     Select TableValue FROM test2 where RecNum=param INTO newValue;
    ->     RETURN newValue;
    ->     END$$
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE FUNCTION `testCursorHandler`(doCall int) RETURNS varchar(200) CHARSET latin1
    ->     READS SQL DATA
    -> BEGIN
    ->         DECLARE returnValue VARCHAR(200) DEFAULT ""; 
    ->         DECLARE newValue    varchar(3);
    ->         DECLARE done INT DEFAULT 0;
    ->         DECLARE cur1 CURSOR FOR 
    ->           select  IF(doCall=0,returnFromQuery(RecNum),returnFromQuery(TableValue)) as
    -> testValue
    ->           from test2
    ->           order by RecNum;
    ->         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    ->         IF done=0 THEN  
    ->            OPEN cur1;
    ->            REPEAT
    ->               FETCH cur1 INTO newValue;
    ->               set newValue=IFNULL(newValue,"---");
    ->               set returnValue=CONCAT(returnValue,newvalue,"(",done,"), ");
    ->            UNTIL done END REPEAT;
    ->            CLOSE cur1;
    ->         END IF; 
    ->         RETURN returnValue;
    ->     END$$
Query OK, 0 rows affected (0.03 sec)

mysql> select testCursorHandler(0);
    -> $$
+---------------------------------+
| testCursorHandler(0)            |
+---------------------------------+
| 1(0), 2(0), -1(0), 4(0), 4(1),  |
+---------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

mysql> select testCursorHandler(1);
    -> $$
+----------------------+
| testCursorHandler(1) |
+----------------------+
| 1(1),                |
+----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

my tests show that it seems this bug was introduced in 5.5.6 ...  And the change history of 5.5.6 say this:
"Incompatible Change: Handling of warnings and errors during stored program execution was problematic:

If one statement generated several warnings or errors, only the handler for the first was activated, even if another might be more appropriate.

Warning or error information could be lost.

(Bug #36185, Bug #5889, Bug #9857, Bug #23032)"

Can be easily repeated using the example in http://dev.mysql.com/doc/refman/5.5/en/cursors.html

USE TEST;

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id char(16), data int);
CREATE TABLE t2 (i int);

DROP PROCEDURE IF EXISTS curdemo;

delimiter $$

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END$$

delimiter ;

CALL curdemo();

On 5.5.5:

mysql> CALL curdemo();
Query OK, 0 rows affected (0.00 sec)

On 5.5.14:

mysql> CALL curdemo();
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

mysql>

Noted in 5.6.4 changelog.

The RETURN statement did not clear the diagnostics area as it should
have. Now the diagnostics area is cleared before executing RETURN.
This prevents a condition in a nested function call from incorrectly
propagating to an outer scope. It also means there is no way to
return an SQL warning from a stored function. This change is not
backward compatible, but the resulting behavior is more like standard 
SQL.

Bug #64521 was marked as duplicate of this one.