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
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