Details
Minor
Description
Create a trivial procedure with a cursor and a loop for that cursor. Handle the NOT FOUND event, or the 1329 error, or both.
DELIMITER || CREATE TABLE `test`.`tab1` (c INT); CREATE PROCEDURE `test`.demo() BEGIN DECLARE `eof` BOOL; DECLARE `v` INT; DECLARE `crs` CURSOR FOR SELECT `c` FROM `test`.`tab1`; -- this should handle error... DECLARE CONTINUE HANDLER FOR NOT FOUND, 1329 SET eof = TRUE; OPEN `crs`; `lp`: LOOP FETCH `crs` INTO `v`; IF `eof` IS TRUE THEN LEAVE `lp`; END IF; END LOOP; CLOSE `crs`; END; || DELIMITER ;
Then run it:
MariaDB [(none)]> CALL test.demo(); Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> SHOW WARNINGS; +-------+------+-----------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------+ | Error | 1329 | No data - zero rows fetched, selected, or processed | +-------+------+-----------------------------------------------------+ 1 row in set (0.00 sec)
As far as I understand this warning should not appear, because that warning is handled.
This also happens with MySQL 5.5.30, but not with 5.6.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Fixed in 10.0
Hi Federico,
This limitation/bug is documented in MySQL 5.5 manual (http://dev.mysql.com/doc/refman/5.5/en/condition-handling.html):
"Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround."
I'm not quite sure it's worth backporting the fix from 5.6, but I'll get a second opinion.