MySQL Bugs: #15231: Stored procedure bug with not found condition handler

Bug #15231	Stored procedure bug with not found condition handler
Submitted:	24 Nov 2005 15:17	Modified:	18 Jan 2006 0:06
Reporter:	Valentin Komissarov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16/5.0.17 BK	OS:	Windows (win32/Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
server version 5.0.16-nt win32 official build;
Windows 2k,XP WIN32;

THE DECLARE CURSOR TAKE BUG IN NESTED STORED PROCEDURE lp1 IN
 
SELECT `FieldName`
INTO InOutParameter
FROM `TheTable`
WHERE `KeyField`=NULL;

IF InOutParameter IS NULL take the bug result;

if delete declare cursor in outer procedure - procedure work complete.

How to repeat:
SET FOREIGN_KEY_CHECKS=0;
USE `test`;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` int(11) unsigned NOT NULL auto_increment,
  `ID2` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `ID` int(11) unsigned NOT NULL auto_increment,
  `ID_t1` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `idx_ID_t1` (`ID_t1`),
  CONSTRAINT `t2_fk` FOREIGN KEY (`ID_t1`) REFERENCES `t1` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

DROP PROCEDURE IF EXISTS `gp1`;
CREATE PROCEDURE `gp1`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  START TRANSACTION;
  CALL lmp1();
  COMMIT;
END;

DROP PROCEDURE IF EXISTS `lmp1`;
CREATE PROCEDURE `lmp1`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  DECLARE xID INTEGER;
  /*THE DECLARE CURSOR TAKE BUG IN NESTED STORED PROCEDURE lp1*/
  DECLARE xDone INTEGER DEFAULT 0;
  DECLARE cur1 CURSOR FOR
          SELECT ID
          FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET xDone = 1;
  /*END DECLARE CURSOR*/

  SET xID=NULL;
  CALL lp1(xID);
  INSERT INTO t2(ID_t1)
       VALUES(xID);
END;

DROP PROCEDURE IF EXISTS `lp1`;
CREATE PROCEDURE `lp1`(INOUT ioID INTEGER)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  SELECT ID
  INTO ioID
  FROM t1
  WHERE ID=ioID;
  IF ioID IS NOT NULL THEN
    UPDATE t1 SET
    ID2=1
    WHERE ID=ioID;
    SET @OResult=1;
  ELSE
    SET @OResult=2;
    INSERT INTO t1(ID2)
          VALUES(0);
    SELECT LAST_INSERT_ID()
    INTO ioID;
  END IF;
END;

COMMIT;

/*REPEAT BUG*/
CALL gp1();
SELECT @OResult;
/*THE BUG Column 'ID_t1' cannot be null*/

bug in declare continue handler

in mysql 5.0.15 this bug does not exist (5.0.15-nt, win32)

miguel@hegel:~/dbs/5.0> 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 5 to server version: 5.0.17-debug

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

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> USE `test`;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t1` (
    ->   `ID` int(11) unsigned NOT NULL auto_increment,
    ->   `ID2` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DROP TABLE IF EXISTS `t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t2` (
    ->   `ID` int(11) unsigned NOT NULL auto_increment,
    ->   `ID_t1` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   KEY `idx_ID_t1` (`ID_t1`),
    ->   CONSTRAINT `t2_fk` FOREIGN KEY (`ID_t1`) REFERENCES `t1` (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> delimiter //
mysql> 
mysql> DROP PROCEDURE IF EXISTS `gp1`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `gp1`()
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   START TRANSACTION;
    ->   CALL lmp1();
    ->   COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DROP PROCEDURE IF EXISTS `lmp1` //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> CREATE PROCEDURE `lmp1`()
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   DECLARE xID INTEGER;
    ->   /*THE DECLARE CURSOR TAKE BUG IN NESTED STORED PROCEDURE lp1*/
    ->   DECLARE xDone INTEGER DEFAULT 0;
    ->   DECLARE cur1 CURSOR FOR
    ->           SELECT ID
    ->           FROM t1;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET xDone = 1;
    ->   /*END DECLARE CURSOR*/
    -> 
    ->   SET xID=NULL;
    ->   CALL lp1(xID);
    ->   INSERT INTO t2(ID_t1)
    ->        VALUES(xID);
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DROP PROCEDURE IF EXISTS `lp1` //
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE `lp1`(INOUT ioID INTEGER)
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   SELECT ID
    ->   INTO ioID
    ->   FROM t1
    ->   WHERE ID=ioID;
    ->   IF ioID IS NOT NULL THEN
    ->     UPDATE t1 SET
    ->     ID2=1
    ->     WHERE ID=ioID;
    ->     SET @OResult=1;
    ->   ELSE
    ->     SET @OResult=2;
    ->     INSERT INTO t1(ID2)
    ->           VALUES(0);
    ->     SELECT LAST_INSERT_ID()
    ->     INTO ioID;
    ->   END IF;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> COMMIT //
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CALL gp1();
    -> //
ERROR 1048 (23000): Column 'ID_t1' cannot be null
mysql> 

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

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

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.05 sec)

mysql> USE `test`;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE TABLE `t1` (
    ->   `ID` int(11) unsigned NOT NULL auto_increment,
    ->   `ID2` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> DROP TABLE IF EXISTS `t2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t2` (
    ->   `ID` int(11) unsigned NOT NULL auto_increment,
    ->   `ID_t1` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   KEY `idx_ID_t1` (`ID_t1`),
    ->   CONSTRAINT `t2_fk` FOREIGN KEY (`ID_t1`) REFERENCES `t1` (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> delimiter //
mysql>
mysql> DROP PROCEDURE IF EXISTS `gp1`//
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE PROCEDURE `gp1`()
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   START TRANSACTION;
    ->   CALL lmp1();
    ->   COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP PROCEDURE IF EXISTS `lmp1` //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql>
mysql> CREATE PROCEDURE `lmp1`()
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   DECLARE xID INTEGER;
    ->   /*THE DECLARE CURSOR TAKE BUG IN NESTED STORED PROCEDURE lp1*/
    ->   DECLARE xDone INTEGER DEFAULT 0;
    ->   DECLARE cur1 CURSOR FOR
    ->           SELECT ID
    ->           FROM t1;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET xDone = 1;
    ->   /*END DECLARE CURSOR*/
    ->
    ->   SET xID=NULL;
    ->   CALL lp1(xID);
    ->   INSERT INTO t2(ID_t1)
    ->        VALUES(xID);
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP PROCEDURE IF EXISTS `lp1` //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `lp1`(INOUT ioID INTEGER)
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   SELECT ID
    ->   INTO ioID
    ->   FROM t1
    ->   WHERE ID=ioID;
    ->   IF ioID IS NOT NULL THEN
    ->     UPDATE t1 SET
    ->     ID2=1
    ->     WHERE ID=ioID;
    ->     SET @OResult=1;
    ->   ELSE
    ->     SET @OResult=2;
    ->     INSERT INTO t1(ID2)
    ->           VALUES(0);
    ->     SELECT LAST_INSERT_ID()
    ->     INTO ioID;
    ->   END IF;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> COMMIT //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CALL gp1();
    -> //
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings //
+---------+------+------------------+
| Level   | Code | Message          |
+---------+------+------------------+
| Warning | 1329 | No data to FETCH |
+---------+------+------------------+
1 row in set (0.00 sec)

mysql>

A short test case to demonstrate the problem (NOT FOUND condition terminates execution of procedure p2, while it should not):

delimiter //
drop table if exists t1//

create table t1 (id int not null auto_increment primary key)//
  
drop procedure if exists p1, p2//
  
create procedure p1()
begin
  declare xid integer;
  declare xdone integer default 0;
  declare continue handler for not found set xdone = 1;

  set xid=null;
  call p2(xid);
  select xid, xdone;
end //
create procedure p2(inout ioid integer)
begin
  select "before NOT FOUND condition is triggered" as '1';
  select id into ioid from t1 where id=ioid;
  select "after NOT FOUND condtition is triggered" as '2';

  if ioid is null then
    set ioid=1;
  end if;
end //

call p1()//

Output:

mysql> call p1()//
+-------------------+
| hello, here we go |
+-------------------+
| hello, here we go |
+-------------------+
1 row in set (0.01 sec)

+------+-------+
| xid  | xdone |
+------+-------+
| NULL |     1 |
+------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Sorry, should be:

drop procedure if exists p1//
drop procedure if exists p2//
  
And the output:

mysql> call p1()//
+-----------------------------------------+
| 1                                       |
+-----------------------------------------+
| before NOT FOUND condition is triggered |
+-----------------------------------------+
1 row in set (0.00 sec)

+------+-------+
| xid  | xdone |
+------+-------+
| NULL |     1 |
+------+-------+
1 row in set (0.00 sec)

The problem is with "not found" condition handlers (and "sqlwarning" handlers)
in the invoking procedure. (The synopsis updated accordingly.)

The code doesn't make the distinction between an "exception condition" and a
"completion condition" (i.e. warning or not found), required by the standard.
(See ISO/IEC 9075-2:2003 "Foundation", 4.29.2 "Status parameters", 3:rd
paragraph, and ISO/IEC 9075-4:2003 "PSM", 4.9 "Condition handling", 9:th and
10:th paragraphs.)

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/commits/104

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/commits/105

Pushed to bk 5.0.19.

Added to 5.0.19 changelog:

<listitem>
        <para>
          The <literal>NOT FOUND</literal> condition handler for stored
          procedures did not distinguish between a <literal>NOT
            FOUND</literal> condition and an exception or warning. (Bug
          #15231)
        </para>
      </listitem>