Bug #21416 SP: Recursion level higher than zero needed for non-recursive call

Description:
A procedure which in turn does show create procedure of itself, gives an error that the recursion level is 0, though there is no recursive call at all.

How to repeat:
mysql> select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.12-beta-valgrind-max-debug-log |
+------------------------------------+
1 row in set (0.00 sec)

mysql> use db2;
Database changed
mysql> create procedure proc_show() show create procedure proc_show;
Query OK, 0 rows affected (0.04 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> create procedure proc_show2() show create function func1;
Query OK, 0 rows affected (0.04 sec)

mysql> create function func1() returns int return 1;
Query OK, 0 rows affected (0.04 sec)

mysql> call proc_show2();
+----------+----------+-------------------------------------------------------------------------------+
| Function | sql_mode | Create Function                                                               |
+----------+----------+-------------------------------------------------------------------------------+
| func1    |          | CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
return 1 |
+----------+----------+-------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> set max_sp_recursion_depth=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
+-----------+----------+-------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                                          |
+-----------+----------+-------------------------------------------------------------------------------------------+
| proc_show |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_show`()
show create procedure proc_show |
+-----------+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Thank you for the bug report. Verified as described:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db2
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create procedure proc_show() show create procedure proc_show;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> create procedure proc_show2() show create function func1;
Query OK, 0 rows affected (0.00 sec)

mysql> create function func1() returns int return 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show2();
+----------+----------+-------------------------------------------------------------------------------+
| Function | sql_mode | Create Function                                                               |
+----------+----------+-------------------------------------------------------------------------------+
| func1    |          | CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
return 1 | 
+----------+----------+-------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> set max_sp_recursion_depth=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
+-----------+----------+-------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                                          |
+-----------+----------+-------------------------------------------------------------------------------------------+
| proc_show |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_show`()
show create procedure proc_show | 
+-----------+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

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/commits/10043

ChangeSet@1.2244, 2006-08-04 12:46:54+02:00, andrey@lmy004. +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
  
  Actually there is no recursive call but the limit is checked.
  
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

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/commits/10044

ChangeSet@1.2244, 2006-08-04 12:50:49+02:00, andrey@lmy004. +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
  
  Actually there is no recursive call but the limit is checked.
  
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

Pushed to 5.1.12.

The previons comment is wrong---the patch wasn't pushed yet.

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/commits/10851

ChangeSet@1.2244, 2006-08-24 19:36:26+02:00, andrey@example.com +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
    
  Actually there is no recursive call but the limit is checked.
    
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

Queued in 5.0-rt

Pushed into 5.0.25 and 5.1.12.

Noted in 5.0.25, 5.1.12 changelogs.

With max_sp_recursion set to 0, a stored procedure that executed a
SHOW CREATE PROCEDURE statement for itself triggered a recursion
limit exceeded error, though the statement involves no recursion.