Description:
Accessing a with_clause statement with cursor in a defined procedure may leads to empty result set in MySQL8.0.20 ;
Please check details in the following `How to repeat` part;
How to repeat:
(1) #initialize
create database if not exists test;
use test;
create table nums (pid int, ppid int);
insert into nums values (1,0),(2,0),(3,1),(4,1),(5,2),(6,2),(7,5),(8,5),(9,6),(10,6);
(2) # define and call this procedure (access common select statement with cursor), we can get result set normally
# you can copy, paste and excute these sql commands directly
drop procedure if exists p1;
delimiter //
create procedure p1()
begin
declare pid, ppid, total int;
declare done int default 0;
declare cur cursor for (select * from nums) ;
declare continue handler for not found set done = 1;
open cur;
set total = 0;
while done != 1 do
fetch cur into pid,ppid;
if done != 1 then
select pid, ppid;
set total = total + 1;
end if;
end while;
close cur;
select concat(total, " records found!");
end //
delimiter ;
call p1();
(3) # run this sql, we can get 3 records: (1,0), (3,1), (4,1)
with recursive cte as (select * from nums where pid = 1 union all select nums.* from nums join cte on nums.ppid = cte.pid) select * from cte;
(4) # but if we replace the sql in the cursor definition with previous with_clause, we get empty resultset (3 records are expected)
drop procedure if exists p1;
delimiter //
create procedure p1()
begin
declare pid, ppid, total int;
declare done int default 0;
declare cur cursor for (with recursive cte as (select * from nums where pid = 1 union all select nums.* from nums join cte on nums.ppid = cte.pid) select * from cte ) ;
declare continue handler for not found set done = 1;
open cur;
set total = 0;
while done != 1 do
fetch cur into pid,ppid;
if done != 1 then
select pid, ppid;
set total = total + 1;
end if;
end while;
close cur;
select concat(total, " records found!");
end //
delimiter ;
call p1();
Suggested fix:
none