Bug #59169 Bad handling of continue handler for sqlstate/not found declaration.

Submitted: 25 Dec 2010 17:19 Modified: 26 Dec 2010 8:35
Reporter: Milan Matejec Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.5.8-log OS:Linux
Assigned to: CPU Architecture:Any
Tags: handler, SQLSTATE

[25 Dec 2010 17:19] Milan Matejec
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.
[26 Dec 2010 8:35] Valeriy Kravchuk
This is a duplicated of/related to bug #55843.

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.