Bug #103890 wrong result when use cte with subquery
Submitted: 2 Jun 13:47 Modified: 3 Jun 7:12
Reporter: jiangtao guo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.23, 8.0.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: cte

[2 Jun 13:47] jiangtao guo
Description:
Server version: 8.0.23 MySQL Community Server - GPL
mysql>  with recursive cte1(c1) as (select 1 union select c1 + 1 from cte1 limit 2 offset 1) select c1 from cte1 where c1 in (select 2);
Empty set (0.01 sec)

The above SQL's result should be "2". Looks like it's a wrong result. 

How to repeat:
with recursive cte1(c1) as (select 1 union select c1 + 1 from cte1 limit 2 offset 1) select c1 from cte1 where c1 in (select 2);
[3 Jun 7:12] MySQL Verification Team
Hello jiangtao,

Thank you for the report and test case.

regards,
Umesh