MySQL Bugs: #12168: 'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional handled incorrectly

Bug #12168	'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional handled incorrectly
Submitted:	26 Jul 2005 1:03	Modified:	24 Aug 2005 17:29
Reporter:	Jim Winstead	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0-bk	OS:	Any (*)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Given a stored procedure with two 'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' statements within conditional blocks, a call to the procedure that uses a cursor within the second block will return a spurious 'No data to FETCH' error.

Probably related to Bug #6127.

How to repeat:
--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (a int);
insert into t1 values (1),(2),(3),(4);

create table t2 (a int);

delimiter //;

create procedure p1 (arg1 char(1))
begin
  declare b, c integer;
  if arg1 = 'a' then
    begin
      declare c1 cursor for select a from t1 where a % 2;
      declare continue handler for not found set b = 1;
      set b = 0;
      open c1;
      c1_repeat: repeat
        fetch c1 into c;
        if (b = 1) then
          leave c1_repeat;
        end if;

        insert into t2 values (c);
        until b = 1
      end repeat;
    end;
  end if;
  if arg1 = 'b' then
    begin
      declare c2 cursor for select a from t1 where not a % 2;
      declare continue handler for not found set b = 1;
      set b = 0;
      open c2;
      c2_repeat: repeat
        fetch c2 into c;
        if (b = 1) then
          leave c2_repeat;
        end if;

        insert into t2 values (c);
        until b = 1
      end repeat;
    end;
  end if;
end//

delimiter ;//

call p1('a');
select * from t2;
truncate t2;
# this next call fails, but should not
call p1('b');
select * from t2;
drop table t1, t2;
drop procedure p1;

Here is a row base replication version to try with your patch:

#############################################################################
# Original Author: Jonathan Miller                                          #
# Original Date: Aug/18/2005                                                #
#############################################################################
#TEST: Taken and modfied from http://bugs.mysql.com/bug.php?id=12168        #
#############################################################################

# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

# Begin clean up test section
connection master;
--disable_warnings
DROP PROCEDURE IF EXISTS test.p1;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;

# Begin test section 1
CREATE TABLE test.t1 (a INT, PRIMARY KEY(a));
INSERT INTO test.t1 VALUES (1),(2),(3),(4);
CREATE TABLE test.t2 (a INT, PRIMARY KEY(a));

delimiter |;
CREATE PROCEDURE test.p1 (arg1 CHAR(1))
BEGIN
  DECLARE b, c INT;
  IF arg1 = 'a' THEN
    BEGIN
      DECLARE cur1 CURSOR FOR SELECT A FROM test.t1 WHERE a % 2;
      DECLARE continue handler for not found set b = 1;
      SET b = 0;
      OPEN cur1;
      c1_repeat: REPEAT
        FETCH cur1 INTO c;
        IF (b = 1) THEN
          LEAVE c1_repeat;
        END IF;

        INSERT INTO test.t2 VALUES (c);
        UNTIL b = 1
      END REPEAT;
      CLOSE cur1;
    END;
  END IF;
  IF arg1 = 'b' THEN
    BEGIN
      DECLARE cur2 CURSOR FOR SELECT a FROM test.t1 WHERE NOT a % 2;
      DECLARE continue handler for not found set b = 1;
      SET b = 0;
      OPEN cur2;
      c2_repeat: REPEAT
        FETCH cur2 INTO c;
        IF (b = 1) THEN
          LEAVE c2_repeat;
        END IF;

        INSERT INTO test.t2 VALUES (c);
        UNTIL b = 1
      END REPEAT;
      CLOSE cur2;
    END;
  END IF;
END|
delimiter ;|

CALL test.p1('a');
SELECT * FROM test.t2;
connection slave;
sleep 3;
SELECT * FROM test.t2;
connection master;
truncate test.t2;

# this next call fails, but should not
call test.p1('b');
select * from test.t2;
connection slave;
sleep 3;
SELECT * FROM test.t2;
connection master;
truncate test.t2;
connection slave;
#SELECT * FROM test.t2;

connection master;
#show binlog events;
# lets cleanup

DROP PROCEDURE test.p1;
DROP TABLE test.t1;
DROP TABLE test.t2;

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/internals/28487

Fixed in 5.0 tree, currently tagged 5.0.12

A new patch:
http://lists.mysql.com/internals/28530

Documented in 5.0.12 changelog:

<listitem><para>Duplicate instructions in stored procedures resulted in incorrect execution when the optimizer optimized the duplicate code away. (Bug #12168)</para></listitem>