Bug #4726 Stored procedure crash when looping over SELECT with complex WHERE's
Submitted: 23 Jul 2004 16:15 Modified: 26 Jul 2004 14:18
Reporter: Per-Erik Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0 OS:Any (All)
Assigned to: Bugs System CPU Architecture:Any

[23 Jul 2004 16:15] Per-Erik Martin
Description:
When repeatedly calling a SELECT with a more complex WHERE from within a stored procedure, the server crashes.

How to repeat:
drop procedure if exists bug_sp_2; 
drop table if exists bug_sp_2; 
 
create table bug_sp_2 (f1 int, f2 int, f3 int); 
insert into  bug_sp_2 values (1,1,1); 
 
delimiter |; 
CREATE PROCEDURE bug_sp_2() 
BEGIN 
 
   DECLARE      tmp_o_id INT; 
   DECLARE      tmp_d_id INT default 1; 
 
   while tmp_d_id <= 2 DO 
   BEGIN 
 
#Query below crashes server 
     SELECT f1 INTO tmp_o_id FROM bug_sp_2 WHERE f2=1 AND f3=1; 
 
#Query below not 
#    SELECT f1 INTO tmp_o_id FROM bug_sp_2 WHERE f2=1; 
 
     SET tmp_d_id = tmp_d_id + 1; 
 
   END; 
   END WHILE; 
 
END| 
delimiter ;|

call bug_sp_2();
[26 Jul 2004 14:18] 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

Additional info:

Fixed in 4.1 and then merged into 5.0.