| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2 | OS: | Windows (Windows 2003) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[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
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.