Bug #55852 Possibly inappropriate handler activation
Submitted: 9 Aug 2010 17:45 Modified: 16 Oct 2011 22:27
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1, 5.6.0-m4 OS:Linux (SUSE 64-bit)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[9 Aug 2010 17:45] Peter Gulutzan
Description:
I'm using mysql-trunk-bugfixing.

The SQL standard document says:
"8) If there is a general<handler declaration>  and a specific
<handler declaration>  for the same<condition value>  in the same
scope, then only the specific<handler declaration>  is associated
with that<condition value>."
A general handler declaration has SQLWARNING + SQLEXCEPTION + NOT FOUND.
A specific handler declaration has SQLSTATE, condition name,
or (for us) an errno.

So when there are multiple handlers in the same scope, for example
DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'sqlwarning';
DECLARE EXIT HANDLER FOR SQLSTATE '22003' SELECT 'sqlstate';
DECLARE EXIT HANDLER FOR 1264 SELECT '1264';
INSERT INTO t (smallint_column) VALUES (99999);
They're all applicable, but MySQL very rightly activates the
most specific handler, that is, '1264'.

But notice the standard's exact words: "in the same scope".
MySQL is activating a more specific handler even if it's in
a different, containing, scope.

How to repeat:
For example, this script will say '22003':
delimiter //
drop procedure if exists p//
drop table if exists t//
set @@sql_mode=''//
create table t (s1 smallint)//
create procedure p ()
begin
  declare continue handler for sqlstate '22003' select '22003';
  begin
    declare continue handler for sqlwarning select 'sqlwarning';
    insert into t values (99999);
    end;
  end//
call p()//

That appears wrong to me. I'm sure we all know that, when there
are multiple handlers in different scopes, the "more appropriate"
handler is the one in the innermost compound statement.
So I'd expect the script to display 'sqlwarning'.
[9 Aug 2010 18:22] MySQL Verification Team
Thank you for the bug report.
[14 Oct 2010 12:35] Alexander Nozdrin
Related to Bug#38806.
[18 Nov 2010 11:56] Alexander Nozdrin
This bug is related to Bug#38806 and will be fixed
by the patch for that bug.
[16 Oct 2011 22:27] Paul DuBois
Noted in 5.6.4 changelog.

If an error occurred in a context where different handlers were
present at different levels of nesting, an outer handler could be
chosen rather than the innermost one.