Bug #103890 wrong result when use cte with subquery

• Submitted: 2 Jun 2021 13:47
• Modified: 3 Jun 2021 7:12
• Reporter: jiangtao guo
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.23, 8.0.25
• OS: Any
• CPU Architecture: Any
• Tags: cte

[2 Jun 2021 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 2021 7:12] MySQL Verification Team

Hello jiangtao,

Thank you for the report and test case.

regards,
Umesh