MySQL Bugs: #15011: error handler for mysql errno in nested block not activated

Bug #15011	error handler for mysql errno in nested block not activated
Submitted:	17 Nov 2005 11:12	Modified:	2 Feb 2006 18:26
Reporter:	Guy Harrison	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.17-BK	OS:	Linux (Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
If I have a handler in an "outer" block and a handler on the same error code in an inner block I would expect the inner handler to take precedence for errors that occur in the inner block.  However, the "outer" handler appears to activated in precedence to the inner handler.  

Eg:

BEGIN
   DECLARE CONTINUE HANDLER FOR ....
   BEGIN
       DECLARE CONTINUE HANDLER FOR ....
       StatementX
   END;
END;

If StatementX has an error the 2nd handler should be invoked, shouldn't it?  But it's the first handler that actually fires. 

  
   

How to repeat:
DELIMITER $$ ;

DROP TABLE IF EXISTS ht$$

CREATE TABLE ht (c1 INT PRIMARY KEY)$$

INSERT INTO ht VALUES(1)$$

DROP PROCEDURE IF EXISTS  handler_test$$

CREATE PROCEDURE handler_test() DETERMINISTIC
BEGIN

  DECLARE CONTINUE HANDLER FOR 1062
    SELECT 'Outer handler invoked';

  SELECT version();

  BEGIN
    DECLARE CONTINUE HANDLER FOR 1062
      SELECT 'Inner handler invoked';

    INSERT INTO ht VALUES(1);
  END;
END$$

CALL handler_test()$$

mysql> source scope_error.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-----------------------------+
| version()                   |
+-----------------------------+
| 5.0.17-nightly-20051114-log |
+-----------------------------+
1 row in set (0.00 sec)

+-----------------------+
| Outer handler invoked |
+-----------------------+
| Outer handler invoked |
+-----------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Verified just as described on 5.0.17-BK (ChangeSet@1.1969, 2005-11-17 15:08:49+01:00, ...) on Linux:

mysql> delimiter $$
mysql> CREATE TABLE ht (c1 INT PRIMARY KEY)$$

Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO ht VALUES(1)$$

Query OK, 1 row affected (0,01 sec)

mysql> DROP PROCEDURE IF EXISTS  handler_test$$
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE PROCEDURE handler_test() DETERMINISTIC
    -> BEGIN
    ->
    ->   DECLARE CONTINUE HANDLER FOR 1062
    ->     SELECT 'Outer handler invoked';
    ->
    ->   SELECT version();
    ->
    ->   BEGIN
    ->     DECLARE CONTINUE HANDLER FOR 1062
    ->       SELECT 'Inner handler invoked';
    ->
    ->     INSERT INTO ht VALUES(1);
    ->   END;
    -> END$$

Query OK, 0 rows affected (0,02 sec)

mysql> CALL handler_test()$$
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

+-----------------------+
| Outer handler invoked |
+-----------------------+
| Outer handler invoked |
+-----------------------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

This only happens for handlers of explicit mysql error numbers.

https://intranet.mysql.com/secure/mailarchive/mail.php?folder=5&mail=47213

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2009

Pushed to bk 5.0.19

Documented in 5.0.19 changelog:

     <listitem>
        <para>
          When multiple handlers are created for the same MySQL error
          number within nested blocks, the outermost handler took
          precedence. (Bug #15011)
        </para>
      </listitem>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2662