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: | |
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 |
[24 Nov 2005 15:17]
Valentin Komissarov
[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]
MySQL Verification Team
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>