Bug #10100 Stored procedures recursivity problem
Submitted: 22 Apr 2005 17:20 Modified: 2 Dec 2005 20:08
Reporter: John Kowawsky Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5 OS:Microsoft Windows (Windows XP)
Assigned to: Oleksandr Byelkin

[22 Apr 2005 17:20] John Kowawsky
Description:
MySQL 5's custom funcions does not accept recursivity nor it give any warnings.

Then I try this, mySQL create the function without any error.

But when I execute, the functions goes well until it tries the recursive comand. The output give the error:
ERROR 2027 (HY000): Malformed packet
and the connection to mySQL is lost.

I didnt tried with stored procedure but i guess it have the same problem.

How to repeat:
Try this:

delimiter //

DROP FUNCTION IF EXISTS test.recursivity;//
CREATE FUNCTION test.recursivity(prm INTEGER) RETURNS CHAR(50)
BEGIN
  DECLARE result CHAR(50) DEFAULT prm+' its below 6, variable is working';

  /*--- if prm > 10, the funcion will call itself with parameter 1 and all variables will turn null --*/
  IF prm > 5 THEN
    SELECT test.recursivity(1);
  END IF;

  RETURN result;
END;//

SELECT test.recursivity(1);//
SELECT test.recursivity(4);//
SELECT test.recursivity(5);//
SELECT test.recursivity(6);//

Suggested fix:
the least thing to do is to give an error then trying to create recursive function.

But recursive function would be greatly apreciated if suported. :(
[22 Apr 2005 17:34] Jorge del Conde
Verified w/5.0.5:

mysql> SELECT test.recursivity(5);//
+---------------------+
| test.recursivity(5) |
+---------------------+
| 5                   |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT test.recursivity(6);//
ERROR 2027 (HY000): Malformed packet
mysql>
[20 May 2005 17:28] Hartmut Holzgraefe
duplicate of 8680
[18 Aug 2005 15:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28461
[8 Sep 2005 14:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29506
[17 Oct 2005 15:04] Michael Widenius
Ok to push after minor changes noted in review email
[10 Nov 2005 10:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32137
[18 Nov 2005 14:58] Konstantin Osipov
Reviewed over email.
[20 Nov 2005 19:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32442
[20 Nov 2005 21:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32443
[22 Nov 2005 23:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32562
[23 Nov 2005 4:51] Oleksandr Byelkin
Thank you for bugreport!
We fixed only procedures for functions and triggers recursion left prohibited.
bugfix was pushed to 5.0.17 source tree repository.
[28 Nov 2005 10:25] Konstantin Osipov
The fix implements support for recursion in stored procedures only.
[29 Nov 2005 11:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32820
[2 Dec 2005 20:08] Paul Dubois
Noted in 5.0.17 changelog.
Also made a note in the restrictions chapter
about recursion not being allowed for stored
functions and triggers.
[5 Mar 2006 10:16] Konstantin Osipov
Bug#14361 has been marked as a duplicate of this bug.