MySQL Bugs: #13748: Endless Loop or returns only one row

Bug #13748	Endless Loop or returns only one row
Submitted:	4 Oct 2005 16:43	Modified:	2 Dec 2005 14:59
Reporter:	manoj chopra	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.x	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The code returns only one row if I define:
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET done=1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
or goes into endless loop, if I comment the above out.
The problem is I want to fetch the data from the cursor and then depending upon what cursor record is I want to get details from another table. Now if the code_master and code_detail have exact keys, the program runs, but if code_detail has duplicate code, the program errors out.
Is there a way to write multiple exception handling? Like we can do in Oracle. We can define multiple blocks and each block can have its own error handling.

I am having problem when the SQL returns NO DATA FOUND or multiple rows, the program terminates or behaves improperly.

I have two tables:
code_master
========
code INT(3) (values-> 1,2,3,4,5)

code_detail
=======
code INT(3) (values-> 1,2,3,4,8,9)
description varchar(255) (values-> 'A','B','C','D','D','E,)

I have written the following procedure
CREATE PROCEDURE code_dim(code int)
BEGIN
    DECLARE Cshort_desc VARCHAR(255);
    DECLARE Ccode  INT;
    DECLARE done INT;
    DECLARE Curcode CURSOR FOR
                        SELECT code from code_master;
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET done=1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

      SET done=0;
      OPEN Curcode;
           REPEAT
                #
                #Fetch the record information into a record and process them one by one
                #
                FETCH Curcode INTO Ccode;
                SELECT description into Cshort_desc 	
			FROM code_detail
			WHERE code=Ccode ;
               select Cshort_desc;
	UNTIL done=0 END REPEAT;
           CLOSE Curcode;
    END;
END;

How to repeat:
Run the above procedure :
CALL source_dim(1);
and it would go into endless loop.

Manoj,

this is not a MaxDB bug report but a MySQL bug report, isn't it? I've changed the Category to MySQL Sever, so that my MySQL co-workers can continue to help you..

Regards,
Ulf

Thank you for a problem report. Please, indicate the exact MySQL version you use.

As for the real problem you are trying to solve... In MySQL handlers are working in scope of the block where they are defined (just as in Oracle). Please, read similar bug report, http://bugs.mysql.com/bug.php?id=13560, and comments in it, and you will be able to solve your problem yourself, I suppose. Inform about any results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".