Bug #81370 Recursive Procedures - as they unwind do not restore parameters
Submitted: 10 May 2016 18:53 Modified: 15 Aug 2016 7:15
Reporter: Robin Lord Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.7.9 OS:Windows
Assigned to: CPU Architecture:Any
Tags: MySQL, PROCEDURE, recursive

[10 May 2016 18:53] Robin Lord
Description:
If a procedure is called recursively until a condition is reached, the values of any variables are retained from the last call to the procedure when it unwinds.

I have created a simple procedure with three fields 

Aid (an autoincrementing field)
Astart varchar 
Aend varchar

then I call a procedure to find the first record with a particular value of astart, and then call the procedure recursively until there are no more records, as it moves back through the recursion the value of astart (using a local parameter) from the final recursion is retained rather than the value from that instance.

In the real data there would be several records with the same values of the astart and there would be a repeat until acount was zero adding a field to indicate the record had been processed, which would result in further recursions and thus a whole network from a critical path diagram would be processed using a single initial call. 

As things stand I must use an iterative process which is much less elegant.

How to repeat:
delimiter $$

DROP PROCEDURE IF EXISTS `testfind`$$

create procedure testfind(node varchar(10))

 begin
   set @node1=node;
   select @node1;
   set max_sp_recursion_depth = 100;
   set @acount = (select count(*) from test where astart=@node1);
   set @actid = (select min(Act_id) from test where astart=@node1);
   set @node2 = (select aend from test where Act_id=@actid);
   select "a",@actid,@acount,@node1,@node2;
   if @node2 is not null then call testfind(@node2); end if;
   select "b",@actid,@acount,@node1,@node2;
 end$$  
 
 delimiter ;

add this procedure to the database and then call it after adding 

record 1 astart "a1", aend ="A2"
record 2 astart "A2", aend = "A3"
record 3 astart "A3"  aend = "A4"

then the call testfind("A1")
 will result in 

a 1 A1 A2
a 1 A2 A3
a 1 A3 A4
a 0 A4 null
b 0 A4 null
b 0 A4 null (this should be A3 null)
b 0 A4 null (this should be A2 null)
b 0 A4 null (this should be A1 null)

Suggested fix:
It seems that as a recursive procedure is exitted the variable stack is not reduced as the number of 'unwinds' is correct but the parameters from the earlier calls are not restored.
[10 May 2016 22:07] MySQL Verification Team
Thank you for the bug report. Which is the value of max_sp_recursion_depth on your test case?. Thanks.
[27 May 2016 14:34] Robin Lord
nearly three weeks since I reported this problem, has anything been done about it?
[15 Jul 2016 7:15] MySQL Verification Team
My apologies, somehow missed this issue from my radar.
Imho this is not a bug, stack applies to local variables, not to user defined variables that are global to session.  

-- 5.7.13
-- Changed user variables to local variable

drop table if exists test;
create table test(Act_id int not null auto_increment primary key,Astart varchar(10), Aend varchar(10));

INSERT INTO test values(1,"A1", "A2");
INSERT INTO test values(2,"A2","A3");
INSERT INTO test values(3, "A3","A4");

SET @@session.max_sp_recursion_depth = 255; 

delimiter $$
DROP PROCEDURE IF EXISTS `testfind`$$
create procedure testfind(node varchar(10))
begin
   declare v_acount, v_actid, v_node1 varchar(10);
   set v_node1=node;
   select v_node1;

   set v_acount = (select count(*) from test where astart=v_node1);
   set v_actid = (select min(Act_id) from test where astart=v_node1);
   set @v_node2 = (select aend from test where Act_id=v_actid);
   select "a",v_actid,v_acount,v_node1,@v_node2;
   
   if @v_node2 is not null then call testfind(@v_node2); end if;
   select "b",v_actid,v_acount,v_node1,@v_node2;
 end$$  
 delimiter ;
call testfind("A1");

mysql> call testfind("A1");
+---------+
| v_node1 |
+---------+
| A1      |
+---------+
1 row in set (0.00 sec)

+---+---------+----------+---------+----------+
| a | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| a | 1       | 1        | A1      | A2       |
+---+---------+----------+---------+----------+
1 row in set (0.00 sec)

+---------+
| v_node1 |
+---------+
| A2      |
+---------+
1 row in set (0.00 sec)

+---+---------+----------+---------+----------+
| a | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| a | 2       | 1        | A2      | A3       |
+---+---------+----------+---------+----------+
1 row in set (0.00 sec)

+---------+
| v_node1 |
+---------+
| A3      |
+---------+
1 row in set (0.00 sec)

+---+---------+----------+---------+----------+
| a | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| a | 3       | 1        | A3      | A4       |
+---+---------+----------+---------+----------+
1 row in set (0.00 sec)

+---------+
| v_node1 |
+---------+
| A4      |
+---------+
1 row in set (0.00 sec)

+---+---------+----------+---------+----------+
| a | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| a | NULL    | 0        | A4      | NULL     |
+---+---------+----------+---------+----------+
1 row in set (0.01 sec)

+---+---------+----------+---------+----------+
| b | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| b | NULL    | 0        | A4      | NULL     |
+---+---------+----------+---------+----------+
1 row in set (0.01 sec)

+---+---------+----------+---------+----------+
| b | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| b | 3       | 1        | A3      | NULL     |
+---+---------+----------+---------+----------+
1 row in set (0.01 sec)

+---+---------+----------+---------+----------+
| b | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| b | 2       | 1        | A2      | NULL     |
+---+---------+----------+---------+----------+
1 row in set (0.01 sec)

+---+---------+----------+---------+----------+
| b | v_actid | v_acount | v_node1 | @v_node2 |
+---+---------+----------+---------+----------+
| b | 1       | 1        | A1      | NULL     |
+---+---------+----------+---------+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)
[16 Aug 2016 1: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".