MySQL Bugs: #28349: LOOP together with DECLARE exits loop after 3 iterations

Bug #28349	LOOP together with DECLARE exits loop after 3 iterations
Submitted:	10 May 2007 11:04	Modified:	19 Jul 2007 17:38
Reporter:	Tobias Asplund	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.38	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
See testcase

How to repeat:
DROP PROCEDURE IF EXISTS bugsp;
DROP TABLE IF EXISTS bugt1;
CREATE TABLE bugt1 ( a INT PRIMARY KEY );
DELIMITER //
CREATE PROCEDURE bugsp (IN v INT)
BEGIN
  foo: LOOP
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
        ITERATE foo;

      SET v = v + 1;
      INSERT INTO bugt1 (a) VALUES (v - 1);
      LEAVE foo;
    END;
  END LOOP foo;
  SELECT * FROM bugt1;
END;
//
DELIMITER ;
CALL bugsp(1);
CALL bugsp(1);
CALL bugsp(1);
CALL bugsp(1);

-- Calling this procedure 4 times, should produce the outputs:
-- 1
-- 1,2
-- 1,2,3
-- 1,2,3,4
-- But it stops after 3 iterations for some reason.

This becomes even more strange if you try it on 5.1, just creating the procedure fails...

mysql> CREATE PROCEDURE bugsp (IN v INT)
    -> BEGIN
    ->   foo: LOOP
    ->     BEGIN
    ->       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    ->         ITERATE foo;
    -> 
    ->       SET v = v + 1;
    ->       INSERT INTO bugt1 (a) VALUES (v - 1);
    ->       LEAVE foo;
    ->     END;
    ->   END LOOP foo;
    ->   SELECT * FROM bugt1;
    -> END;
    -> //
ERROR 1308 (42000): ITERATE with no matching label: foo

i'm getting the same 3 iterations result with 5.0.37,
and the same 1308: ITERATE with no matching label: foo
error message as you got on 5.1 with latest 5.0bk

so something seems to have changed after 5.0.38 ...

mysqltest test case

Attachment: bug28349.tgz (application/x-gtar, text), 799 bytes.

This is not a bug.

The SQL code in the report is illegal per the SQL spec,
and is now rejected with the error :
  1308: ITERATE with no matching label: foo,
starting with 5.0.40 (where related bug#26503 was fixed).

The error in this case is the expected result,
this is not a limitation or a regression.

I'm setting this bug report back to verified status and asking that this be reconsidered.  The problem that stored procedures and triggers that work in 5.0.38 don't compile in 5.0.42 is only part of the issue:

1.  Users that upgrade in place (without reloading from backup) will experience runtime errors caused by procedures that compiled and worked fine in previous versions:

mysql> USE test;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS d;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE d (i INT PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO d VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql>
mysql> DROP PROCEDURE IF EXIST test_label;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'EXIST
 test_label' at line 1
mysql>
mysql> DELIMITER ||
mysql> CREATE PROCEDURE test_label ()
    -> MAIN:BEGIN
    -> DECLARE EXIT HANDLER FOR SQLSTATE '23000' LEAVE MAIN;
    -> INSERT INTO d VALUES (1);
    -> INSERT INTO d VALUES (2);
    -> END MAIN
    -> ||
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL test_label();
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM d;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

[ copy mysql.proc to 5.0.40 installation and start ]

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.40-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)
mysql> CALL test_label();
ERROR 1457 (HY000): Failed to load routine test.test_label. The table mysql.proc
 is missing, corrupt, or contains bad data (internal code -6)

Changing behavior of a GA release is dangerous.  The problem here isn't that the syntax isn't SQL compliant, but that the implementation was buggy.  The patch provides a mechanism to prevent reaching the buggy code, but at the cost of the ability to upgrade between versions.

At the very minimum, this needs to be documented far more extensively than it currently is - introducing a code change that can result in runtime errors in production systems is a very significant change.

As indicated earlier, the fact that the server, with the fix for Bug#26503,
rejects the SP code with the error:
  1308: ITERATE with no matching label: foo
is the expected result, because the scope of the label in the outer block
does not extend to the content of exception handlers.

Closing this bug report, as no code change will be done in the server implementation.

The documentation of Bug#26503 is indeed missing a more detailed description
of the impact of the change, and detailed instructions to identify and fix
affected stored programs (procedures, functions, triggers, events).

Work is currently in progress to update the documentation of bug#26503,
please check this report for more details, it will be updated shortly.