Bug #59169 Bad handling of continue handler for sqlstate/not found declaration.
Description:
Very weird behaviour of stored procedure in case declared cursor or simple "select into" has no (more) data.
In case I declare "handler for sqlstate '02000'" (or 'not found') I suppose that no warnings or even errors will be generated as it is/was in my previous MySQL version 5.1.39-community-log. But warning is still generated instead.
How to repeat:
Store procedure (example causing problem):
************************************************************
delimiter $$
drop procedure if exists prc_test$$
create procedure prc_test() sql security invoker
begin
declare l_id mediumint;
declare l_done tinyint default 0;
declare c_source cursor for
select 1 from dual where 1 = 0;
declare continue handler for sqlstate '02000' set l_done = 1;
open c_source;
repeat
fetch c_source into l_id;
if not l_done then
-- some code
set l_id = 0;
end if;
until l_done end repeat;
close c_source;
end$$
delimiter ;
************************************************************
And results are:
select version();
+-----------+
| version() |
+-----------+
| 5.5.8-log |
+-----------+
1 row in set (0.00 sec)
call prc_test();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
************************************************************
select version();
+----------------------+
| version() |
+----------------------+
| 5.1.39-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> call prc_test();
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
Suggested fix:
Not generate any warning or error in case of correctly declared continue handler.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Very weird behaviour of stored procedure in case declared cursor or simple "select into" has no (more) data. In case I declare "handler for sqlstate '02000'" (or 'not found') I suppose that no warnings or even errors will be generated as it is/was in my previous MySQL version 5.1.39-community-log. But warning is still generated instead. How to repeat: Store procedure (example causing problem): ************************************************************ delimiter $$ drop procedure if exists prc_test$$ create procedure prc_test() sql security invoker begin declare l_id mediumint; declare l_done tinyint default 0; declare c_source cursor for select 1 from dual where 1 = 0; declare continue handler for sqlstate '02000' set l_done = 1; open c_source; repeat fetch c_source into l_id; if not l_done then -- some code set l_id = 0; end if; until l_done end repeat; close c_source; end$$ delimiter ; ************************************************************ And results are: select version(); +-----------+ | version() | +-----------+ | 5.5.8-log | +-----------+ 1 row in set (0.00 sec) call prc_test(); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1329 | No data - zero rows fetched, selected, or processed | +---------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) ************************************************************ select version(); +----------------------+ | version() | +----------------------+ | 5.1.39-community-log | +----------------------+ 1 row in set (0.00 sec) mysql> call prc_test(); Query OK, 0 rows affected (0.00 sec) mysql> show warnings; Empty set (0.00 sec) Suggested fix: Not generate any warning or error in case of correctly declared continue handler.