Bug #26977 exception handlers never hreturn
Submitted: 9 Mar 2007 0:08 Modified: 17 May 2007 14:02
Reporter: Marc ALFF Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0, 5.1 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any

[9 Mar 2007 0:08] Marc ALFF
Description:
In some cases, flow control optimization implemented in sp::optimize
removes hreturn instructions, causing SQL exception handlers to:
- never return
- execute wrong logic

How to repeat:
--disable_warnings
drop table if exists t1;
drop procedure if exists proc_broken;
drop procedure if exists proc_works;
--enable_warnings

create table t1(a int unique);

delimiter //;

create procedure proc_broken(v int)
begin
  declare i int default 5;

  declare continue handler for sqlexception
  begin
    select 'caught something';
    retry:
    while i > 0 do
      begin
        set i = i - 1;
        select 'looping', i;
      end;
    end while retry;
  end;

  select 'do something';
  insert into t1 values (v);
  select 'do something again';
  insert into t1 values (v);
end//

create procedure proc_works(v int)
begin
  declare i int default 5;

  declare continue handler for sqlexception
  begin
    select 'caught something';
    retry:
    while i > 0 do
      begin
        set i = i - 1;
        select 'looping', i;
      end;
    end while retry;
    select 'optimizer: keep hreturn';
  end;

  select 'do something';
  insert into t1 values (v);
  select 'do something again';
  insert into t1 values (v);
end//
delimiter ;//

show procedure code proc_broken;

show procedure code proc_works;

## This error is a bug
--error 1062
call proc_broken(1);

## This works
call proc_works(2);

drop table t1;
drop procedure proc_broken;
drop procedure proc_works;

The code of show procedure is :

show procedure code proc_broken;
Pos     Instruction
0       set i@1 5
1       hpush_jump 7 2 CONTINUE
2       stmt 0 "select 'caught something'"
3       jump_if_not 0(0) (i@1 > 0)
4       set i@1 (i@1 - 1)
5       stmt 0 "select 'looping', i"
6       jump 3

*wrong*, hreturn missing

7       stmt 0 "select 'do something'"
8       stmt 5 "insert into t1 values (v)"
9       stmt 0 "select 'do something again'"
10      stmt 5 "insert into t1 values (v)"
11      hpop 1
show procedure code proc_works;
Pos     Instruction
0       set i@1 5
1       hpush_jump 9 2 CONTINUE
2       stmt 0 "select 'caught something'"
3       jump_if_not 7(7) (i@1 > 0)
4       set i@1 (i@1 - 1)
5       stmt 0 "select 'looping', i"
6       jump 3
7       stmt 0 "select 'optimizer: keep hreturn'"
8       hreturn 2
9       stmt 0 "select 'do something'"
10      stmt 5 "insert into t1 values (v)"
11      stmt 0 "select 'do something again'"
12      stmt 5 "insert into t1 values (v)"
13      hpop 1

Suggested fix:
the flow analysis in optimize() should not shortcut jumps to hreturn,
since this instruction does *more* that just a jump.
Shortcut of hreturn breaks the semantic of the code.
[2 May 2007 12:25] Bugs System
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/25897

ChangeSet@1.2440, 2007-05-02 14:25:01+02:00, thek@adventure.(none) +4 -0
  Bug#26977 exception handlers never hreturn
    - In some cases, flow control optimization implemented in sp::optimize
      removes hreturn instructions, causing SQL exception handlers to:
        * never return
        * execute wrong logic
    - This patch overrides default short cut optimization on hreturn instructions
      to avoid this problem.
[7 May 2007 8:23] Bugs System
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/26200

ChangeSet@1.2440, 2007-05-07 10:23:10+02:00, thek@adventure.(none) +4 -0
  Bug#26977 exception handlers never hreturn
    - In some cases, flow control optimization implemented in sp::optimize
      removes hreturn instructions, causing SQL exception handlers to:
        * never return
        * execute wrong logic
    - This patch overrides default short cut optimization on hreturn instructions
      to avoid this problem.
[16 May 2007 13:47] Bugs System
Pushed into 5.0.42
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[17 May 2007 14:02] Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.

Flow control optimization in stored routines could cause exception
handlers to never return or execute incorrect logic.