Bug #30400 cursor can not support recursion.

• Submitted: 14 Aug 2007 8:03
• Modified: 30 Sep 2007 11:08
• Reporter: [ name withheld ]
• Status: No Feedback
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any
• Tags: Procedures cursor

[14 Aug 2007 8:03] [ name withheld ]

Description:
cursor can not support recursion.

CREATE PROCEDURE class2 (start_with INT)
proc:
BEGIN
DECLARE v_class_id INT;
DECLARE v_parent_id INT;
DECLARE v_class_name CHAR(20);
DECLARE done, error BOOLEAN DEFAULT FALSE;
DECLARE c2 CURSOR FOR
(SELECT class_id, class_name, parent_id
FROM class WHERE parent_id = start_with);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET error = TRUE;
OPEN c2;
	IF error THEN
	SELECT 'OPEN c2 failed'; LEAVE proc; END IF;
REPEAT
SET v_class_id=NULL;
SET v_class_name=NULL;
SET v_parent_id=NULL;
FETCH c2 INTO v_class_id, v_class_name, v_parent_id;
	IF error THEN
	SELECT 'FETCH c2 failed'; LEAVE proc; END IF;
IF done=FALSE THEN
	INSERT INTO Temporary_Table VALUES
	(v_class_id, v_class_name, v_parent_id);
		IF error THEN
		SELECT 'INSERT in class2() failed'; END IF;
	CALL class2 (v_class_id);
		IF error THEN
		SELECT 'Recursive CALL class2() failed'; END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE c2;
IF error THEN
SELECT 'CLOSE c2 failed'; END IF;
END;

How to repeat:
The procedure belive will report 'Recursive CALL class2() failed'. It seems that 'CALL class2 (v_class_id);' can not execute.

[30 Aug 2007 11:08] Sveta Smirnova

Thank you for the report.

Please indicate version of MySQL server you use.

[30 Sep 2007 23:00] Bugs System

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".