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:Microsoft Windows (win32/Linux)
Assigned to: Bugs System

[24 Nov 2005 15:17] Valentin Komissarov
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*/
[24 Nov 2005 15:40] Valentin Komissarov
bug in declare continue handler
[24 Nov 2005 15:47] Valentin Komissarov
in mysql 5.0.15 this bug does not exist (5.0.15-nt, win32)
[24 Nov 2005 16:58] Miguel Solorzano
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>
[9 Dec 2005 18:55] Konstantin Osipov
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)
[9 Dec 2005 18:58] Konstantin Osipov
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)
[13 Dec 2005 11:58] Per-Erik Martin
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.)
[13 Dec 2005 12:13] Bugs System
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
[13 Dec 2005 12:23] Bugs System
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
[17 Jan 2006 13:49] Per-Erik Martin
Pushed to bk 5.0.19.
[18 Jan 2006 0:06] Mike Hillyer
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>