Bug #100271 cursor in procedure works abnormally for with_clause
Submitted: 21 Jul 2020 6:44 Modified: 21 Jul 2020 13:24
Reporter: Brian Yue (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:MySQL8.0.20 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:x86 (intel x86)

[21 Jul 2020 6:44] Brian Yue
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
[21 Jul 2020 13:24] MySQL Verification Team
Hi Mr. Yue,

Thank you for your bug report.

We have managed to repeat your test case and have got erroneous results, just like yourself.

Verified as reported.