Bug #88840 ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed.

• Submitted: 9 Dec 2017 13:22
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• Version: 5.7, 8.0, 9.0
• OS: Any
• CPU Architecture: Any

[9 Dec 2017 13:22] Shane Bester

Description:
I need recursive stored functions... but..

mysql> select f(3);
ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed.

How to repeat:
drop function if exists f;
delimiter $

create function f(i int) returns int no sql
begin
  declare v int default 0;
  if i < 1 then return 1; end if;
  set v:=t(i-1);
  return v;
end $

delimiter ;

select f(3);

[9 Dec 2017 13:29] MySQL Verification Team

small typo in testcase.

set v:=t(i-1);
 should be 
set v:=f(i-1);

but it doesn't change the point of the bug report. 
Using OUT params in stored procedures is too hard for me, as a workaround.

[9 Dec 2017 13:50] MySQL Verification Team

https://bugs.mysql.com/bug.php?id=11394

[10 Dec 2017 22:09] Jesper wisborg Krogh

Posted by developer:
 
As mentioned, the workaround is to implement the recursiveness in a stored procedure and then use an OUT paramenter to transfer the result back to the function:

DROP PROCEDURE IF EXISTS _f;
DROP FUNCTION IF EXISTS f;
DELIMITER $$

CREATE PROCEDURE _f(IN in_i int, OUT out_i int)
BEGIN
  IF (in_i < 1) THEN
    SET out_i = 1;
  ELSE
    CALL _f(in_i-1, out_i);
  END IF;
END$$

CREATE FUNCTION f(i int) RETURNS int NO SQL
BEGIN
   DECLARE v int DEFAULT 0;
   CALL _f(i, v);
   RETURN v;
END$$
DELIMITER ;

Now the function works:

mysql> SET SESSION max_sp_recursion_depth = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT f(3);
+------+
| f(3) |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

But definitely nicer to be able to do it directly in the function.