MySQL Bugs: #16342: SP Continue handler swith where no data

Bug #16342	SP Continue handler swith where no data
Submitted:	10 Jan 2006 15:11	Modified:	12 Jan 2006 8:19
Reporter:	Valentin Komissarov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Windows (win32)
Assigned to:		CPU Architecture:	Any

Description:
The bug http://bugs.mysql.com/bug.php?id=15231
do not fix ....

Why continue handler swith where no data in select ???
i am sorry for my English...

How to repeat:
USE `test`;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
DROP PROCEDURE IF EXISTS `gp_Test`;
CREATE PROCEDURE `gp_Test`(OUT oDone INTEGER)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
 DECLARE xDone INTEGER DEFAULT 0;
 DECLARE xID INTEGER;
 /*DECLARE xcur CURSOR FOR
   SELECT ID
   FROM t1;*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET xDone=1;
 
 SELECT ID
 INTO xID
 FROM t1
 WHERE ID=1;/*NO DATA*/
 SET oDone=xDone;
END;

/*TEST*/
CALL gp_Test(@Done);
SELECT @Done;
/*RESULT @Done=1 !!!*/

Thank you for a prbolem report. Bug #15231 was fixed, but the fix is not included into 5.0.18. Wait for the next release, 5.0.19, to check it.

As for why NOT FOUND handler is called - it obvious requirement. SELECT INTO should return one and only one row. Let me quote the SQL 2003 Standard:

"1) Let Q be the result of <query specification> S.
2) Case:
a) If the cardinality of Q is greater than 1 (one), then an exception condition is raised: cardinality violation.
It is implementation-dependent whether or not SQL-data values are assigned to the targets identified by the <select target list>.
b) If Q is empty, then no SQL-data values are assigned to any targets identified by the <select target list>, and a completion condition is raised: no data.
c) Otherwise, values in the row of Q are assigned to their corresponding targets."

We have case b) here. It works the same way in Oracle, for example. So, it is not a bug, but intended and documented behaviour.