Bug #6224 Stored procedure cursors cause inconsistent results

• Submitted: 23 Oct 2004 1:35
• Modified: 19 Apr 2005 14:32
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Per-Erik Martin
• CPU Architecture: Any

[23 Oct 2004 1:35] Peter Gulutzan

Description:
If I change the contents of a table while a cursor is open, then FETCH gives me values that 
do not exist: NULLs. Okay. But it's inconsistent: (a) it stops happening if I use 
engine=myisam instead of engine=innodb, (b) it stops happening if I remove an "open" and 
a "close". 

How to repeat:
mysql> create procedure ppp2 () begin declare v int default 0; declare x cursor for select * 
from tpp; drop table tpp; create table tpp (s1 int) engine=innodb; insert into tpp values (1); 
open x; close x; open x; update tpp set s1 = 7; fetch x into v; select v; fetch x into v; select 
v; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call ppp2()// 
+------+ 
| v    | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.49 sec) 
 
ERROR 1329 (02000): No data to FETCH

[23 Oct 2004 4:52] MySQL Verification Team

Can't repeat the behavior reported with latest BK source:

mysql> create procedure ppp2 () begin declare v int default 0; declare x cursor
    -> for select * 
    -> from tpp; drop table tpp; create table tpp (s1 int) engine=innodb; insert into
    -> tpp values (1); 
    -> open x; close x; open x; update tpp set s1 = 7; fetch x into v; select v; fetch
    -> x into v; select 
    -> v; end;// 
Query OK, 0 rows affected (0.04 sec)

mysql> call ppp2()//
ERROR 1051 (42S02): Unknown table 'tpp'

[23 Oct 2004 12:12] Peter Gulutzan

I'm sorry, I should have said "DROP IF EXISTS" rather than "DROP". The corrected "How to 
repeat" is: 
 
mysql> delimiter // 
mysql> create procedure ppp2 () begin declare v int default 0; declare x cursor for select * 
     from tpp; drop table if exists tpp; create table tpp (s1 int) engine=innodb; insert into 
     tpp values (1); open x; close x; open x; update tpp set s1 = 7; fetch x into v; select v; 
fetch 
     x into v; select v; end; 
     // 
Query OK, 0 rows affected (0.35 sec) 
 
mysql> call ppp2()// 
+------+ 
| v    | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.46 sec) 
 
ERROR 1329 (02000): No data to FETCH

[19 Apr 2005 14:32] Per-Erik Martin

Apart from the problem of BUG#9367/9771 (which causes the call to hang),
this is no longer repeatable. (With a temporary workaround of these bugs in
place, it behaves as expected.)