Bug #8892 Cursors: New cursor opened in nested scope displays 'Cursor Already Open' error
Submitted: 2 Mar 2005 14:32 Modified: 2 Mar 2005 15:47
Reporter: Disha Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Windows (Windows 2003)
Assigned to: CPU Architecture:Any

[2 Mar 2005 14:32] Disha
Description:
If we Declare and Open a Cursor in a nested scope in a procedure, on calling the procedure, error message that the cursor is already open is displayed.

Note that if we remove the nested scope in the same procedure, the error does not occur.

How to repeat:
1)	Use test//
2)	create table tb1 (f1 char (20), f2 date)//

	insert into tb1 values ('a',050210)//
	insert into tb1 values ('b',050211)//

3)	Drop procedure if exists sp1 //
4)	Create procedure sp1 ()
	BEGIN
		DECLARE cur1 CURSOR FOR SELECT f1, f2 FROM Tb1 ;
		Open cur1;

		BEGIN
			DECLARE cur3 CURSOR FOR SELECT * FROM Tb1 ;
			Open cur3;
			Close Cur3;
		END;
		Close Cur1;
	END //

5)          call sp1()//

ACTUAL RESULT:
	ERROR 1325 (24000): Cursor is already open.

EXPECTED RESULT:
             Since cursor names are different, there should be no error.
[2 Mar 2005 14:38] MySQL Verification Team
Verified with  5.0.3-alpha-log
OS: any
[2 Mar 2005 15:05] MySQL Verification Team
I wasn't able to repeat with a Windows server built from BK source.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-pro-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create table tb1 (f1 char (20), f2 date)//
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tb1 values ('a',050210)//
Query OK, 1 row affected (0.05 sec)

mysql>  insert into tb1 values ('b',050211)//
Query OK, 1 row affected (0.01 sec)

mysql> Drop procedure if exists sp1 //
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> Create procedure sp1 ()
    ->  BEGIN
    ->          DECLARE cur1 CURSOR FOR SELECT f1, f2 FROM Tb1 ;
    ->          Open cur1;
    ->
    ->          BEGIN
    ->                  DECLARE cur3 CURSOR FOR SELECT * FROM Tb1 ;
    ->                  Open cur3;
    ->                  Close Cur3;
    ->          END;
    ->          Close Cur1;
    ->  END //
Query OK, 0 rows affected (0.02 sec)

mysql> call sp1()//
Query OK, 0 rows affected (0.04 sec)

mysql>

I will test on Linux.
[2 Mar 2005 15:47] Per-Erik Martin
This is probably another symptom of BUG#8757, and thus fixed in the source.