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