Bug #8757 Stored Procedures: Scope of Begin and End Statements do not work properly.
Submitted: 23 Feb 2005 23:50 Modified: 28 Feb 2005 16:24
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2 OS:Microsoft Windows (Windows 2003)
Assigned to: Bugs System CPU Architecture:Any

[23 Feb 2005 23:50] Disha
Description:
If two cursors are declared within a nested BEGIN -- END Statement, the cursor definition in the nested BEGIN -- END Statement is ignored and the cursor definition of the outer loop is taken.

How to repeat:
Test Setup 
1. Create database say 'Test'
2. Create table T2(f1 TEXT(20), f2 TEXT(25), f3 DATE, f4 int);
3. Insert into T2 (f1, f2, f3, f4) values ('ABC','XYZ','1990-05-07',100);

Repro Steps

1. Create the following Stored Procedure
   Create procedure sp1( )
      begin
         DECLARE x char;
         DECLARE y char;
         DECLARE cur2 CURSOR FOR SELECT f1 from T2 LIMIT 1;
         SET x = '1';
         SET y = '2';
         begin
             DECLARE x char;
             DECLARE y char;
             DECLARE cur1 CURSOR FOR SELECT f1 from T2 LIMIT 2,1;
             OPEN cur1;
             FETCH cur1 into y;
             Close cur1;
             SET @V2 = y;
             Select @V1,@v2;
         end;
         OPEN cur2;
         FETCH cur2 into y;
         Close cur2;
         SET @V1 = x;
         SET @V2 = y;
         Select @V1,@V2;
      end//

2. Execute the above procedure
     call SP1();

Expected Result
The value of @V2 in the inner BEGIN --END statement should return NULL since the table only has one row and the value of @V2 in the Outer BEGIN --END return ABC.

Actual Result

    +------+
    | @v2  |
    +------+
    | ABC  |
    +------+
    1 row in set (0.00 sec)

    +------+------+
    | @V1  | @V2  |
    +------+------+
    | 1    | NULL |
    +------+------+
    1 row in set (0.03 sec)

    Query OK, 0 rows affected (0.05 sec)

Additional Information
The same problem is observed when two identical HANDLERS are declared within a nested BEGIN -- END Statement the HANDLER  definition in the nested BEGIN -- END Statement is ignored and that of the outer loop taken.
[24 Feb 2005 9:32] Disha
Updated the synopsis.
[24 Feb 2005 13:01] Alexander Keremidarski
I got different result with 5.0 from BK tree 
ChangeSet@1.1892, 2005-02-23 20:22:51+03:00, gluh@gluh.mysql.r18.ru

mysql> call sp1()//
+------+------+
| @V1  | @v2  |
+------+------+
| 1    | ABC  |
+------+------+
1 row in set (0.00 sec)

+------+------+
| @V1  | @V2  |
+------+------+
| 1    | ABC  |
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
[28 Feb 2005 16:24] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html