MySQL Bugs: #3052: Rollback in stored procedure doesn't close cursor

Bug #3052	Rollback in stored procedure doesn't close cursor
Submitted:	3 Mar 2004 3:46	Modified:	24 Apr 2006 9:00
Reporter:	Peter Gulutzan
Status:	Verified
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (SuSE 8.2)
Assigned to:	Konstantin Osipov	Target Version:
Tags:	rollback, cursor
Triage:	Triaged: D3 (Medium)

Description:
ROLLBACK doesn't close open cursors. 
 
This applies only for ROLLBACK statements that occur within stored procedures, 
after a cursor is OPENed, before it is CLOSEd. 
 

How to repeat:
 mysql> delimiter // 
 mysql> create table t (s1 int)// 
 Query OK, 0 rows affected (0.26 sec) 
  
 mysql> insert into t values (55)// 
 Query OK, 1 row affected (0.00 sec) 
  
 mysql> create procedure p2 () begin 
  declare v int; 
  declare x cursor for select s1 from t; 
  open x; 
  rollback; 
  fetch x into v; 
  set @x = v; 
  end;// 
 Query OK, 0 rows affected (0.00 sec) 
  
 mysql> call p2()// 
 Query OK, 0 rows affected, 1 warning (0.01 sec) 
  
 mysql> select @x// 
 +------+ 
 | @x   | 
 +------+ 
 | 55   | 
 +------+ 
 1 row in set (0.00 sec) 
  
 It's clear that the FETCH succeeded, even though 
 there was a ROLLBACK just before it. 

Suggested fix:
Nothing immediate is expected. The behaviour is not intentional, 
but apparently we'll have to wait for the new parser.

This can be fixed. Since it will be a change in behaviour, it must be in 5.1 (or later).
At the same time, the behaviour of COMMIT should be fixed the same way. In this case we
should add the WITH[OUT] HOLD syntax for DECLARE CURSOR and close all not held cursors on
COMMIT.

This is too big to be fixed in 5.0, will be fixed in 5.1