Bug #16568 Continue handler with simple CASE not working correctly
Submitted: 17 Jan 2006 16:41 Modified: 6 Feb 2006 23:53
Reporter: Per-Erik Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.19/5.1.6 BK OS:Linux (Linux)
Assigned to: Per-Erik Martin CPU Architecture:Any

[17 Jan 2006 16:41] Per-Erik Martin 
Description:
This is a follow-up to BUG#14498 (which has been fixed):

When using the simple CASE statement where the evaluation of the test expression fails, and a CONTINUE handler is present, execution is resumed at the beginning of the CASE statement instead of after it:

mysql> call bug14498_4()//
+---------+
| Handler |
+---------+
| error   |
+---------+
1 row in set (0.00 sec)

+---+
| v |
+---+
| ? |
+---+
1 row in set (0.00 sec)

+------+
| End  |
+------+
| done |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

Only the "Handler" and "End" results should be returned, not the "v" result.

How to repeat:
\d //
create procedure bug14498_4()
begin
  declare continue handler for sqlexception select 'error' as 'Handler';

  case v
  when 1 then
    select '1' as 'v';
  when 2 then
    select '2' as 'v';
  else
    select '?' as 'v';
  end case;
  select 'done' as 'End';
end//

call bug14498_4()//

Suggested fix:
The reason is known: The newly introduced set_case_expr instruction needs a continue destination, just like jump_if_not.
[25 Jan 2006 9:51] Per-Erik Martin 
A patch exists, but an (seemingly) unrelated test failure needs to be resolved before it can be committed.
[25 Jan 2006 16:41] Per-Erik Martin 
(updated wrong bug)
[26 Jan 2006 16: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/1680
[6 Feb 2006 15:04] 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/2196
[6 Feb 2006 16:56] Per-Erik Martin 
Pushed to bk 5.0.19
[6 Feb 2006 23:53] Mike Hillyer 
Documented in 5.0.19:

    <listitem>
        <para>
          When evaluation of the test in a <literal>CASE</literal>
          failed
          in a stored procedure that contained a
          <literal>CONTINUE</literal> handler, execution resumed at the
          beginning of the CASE statement instead of at the end. (Bug
          #16568)
        </para>
      </listitem>