MySQL Bugs: #38806: Wrong scope for SQL HANDLERS in SP

Bug #38806	Wrong scope for SQL HANDLERS in SP
Submitted:	14 Aug 2008 16:40	Modified:	16 Oct 2011 22:29
Reporter:	Marc ALFF	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0 and up	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
The scope of an exception handler in a stored procedure should not cover
instructions present in other exception handlers in the same level.

For example, in

create procedure p()
begin
  declare continue handler for XXX
  begin
    -- body of HANDLER H1
  end;

  declare continue handler for YYY
  begin
    -- body of HANDLER H2
  end;

  -- body of p()
end

Expected:
The scope of H1 is P
The scope of H2 is P

A SQL condition raised by P can be caught by H1 or H2
A SQL condition raised by H1 or H2 can *not* be caught by H2 or H1

Actual:
The scope of H1 and H2 is (H1 + H2 + P), wrong

A SQL condition raised by P can be caught by H1 or H2 (ok)
A SQL condition raised by H1 or H2 is caught by H2 or H1 (wrong)

How to repeat:
delimiter //
drop procedure if exists p//
drop table if exists t1, t2//
create table t1 (s1 smallint)//
create table t2 (s1 smallint)//
create procedure p()
begin
  declare continue handler for sqlwarning
    create table t2 (s1 int);
  declare continue handler for sqlexception
    insert into t1 values (99999);
  create table t1 (s1 int);
  end//
call p()//
Result: "ERROR 1050 (42S01): Table 't2' already exists".
Apparently the exception handler's INSERT was caught
by the warning handler.

Thank you for the bug report. Verified as described.

Server version: 5.0.70-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > delimiter //
mysql 5.0 > drop procedure if exists p//
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql 5.0 > drop table if exists t1, t2//
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql 5.0 > create table t1 (s1 smallint)//
Query OK, 0 rows affected (0.08 sec)

mysql 5.0 > create table t2 (s1 smallint)//
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 > create procedure p()
    -> begin
    ->   declare continue handler for sqlwarning
    ->     create table t2 (s1 int);
    ->   declare continue handler for sqlexception
    ->     insert into t1 values (99999);
    ->   create table t1 (s1 int);
    ->   end//
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 > call p()//
ERROR 1050 (42S01): Table 't2' already exists
mysql 5.0 >

Here's how imho we should fix it :

Data structures :
 1. we should get rid of the m_in_handler/m_ihsp stuff : it's a hack
 2. we should be able to keep a stack of m_handler/m_hcount : for this purpose I propose we create a new structure for the m_hstack and keep copies of the combination of m_handler/m_hcont in it (together with the return ip as we do now) : 
struct cont_handler_t {
  sp_handler_t *m_saved_handler;
  uint m_saved_hcount;
  uint m_return_ip;
}
And we change m_hstack's definition to be : 
struct cont_handler_t *m_hstack;

Algorithm (change places):
- on jumping to a continue exception (in sp_head::excute()) we should preserve the current m_handler/m_hcount (together with the return ip) into the m_hstack
- on jumping to a exception we should take out all the handlers (by effectively executing what sp_instr_hpop::execute does) from the current parse context up to the common ancestor context of the handler we're jumping into and the current instruction (the output of the sp_pcontext::diff_handlers)
- on sp_instr_hreturn() for continue handlers we should restore the m_handler/m_hcount from the stack together with the sp

This will allow us to handle more complex examples e.g. like this one : 

  7 create procedure p()
  8 begin
  9   declare continue handler for 1 
 10   begin
 11     signal 1;
 12   end  
 13   declare continue handler for 2
 14   begin
 15     declare continue handler for 3
 16     begin
 17       print;
 18     end  
 19     signal 3
 20   end;  
 21   signal 2;
 22 end//

See related bug #23032

See related Bug#36185

This is a group of bugs that needs to be fixed together. 
The fix depends on a fix for Bug#36649, which is only present in 5.4.

The error is still present with the described 'how to repeat',
with mysql-trunk-bugfixing, pulled today.

Typo: related to Bug#55852.

Noted in 5.6.4 changelog.

If multiple handlers existed at the same level within a stored
program, the wrong one could be chosen.