CTE query execute failed:Recursive Common Table Expression '' can contain neither aggregation nor window functions in recursive query block

[snowballbear]

Bug Report

Please answer these questions before submitting your issue. Thanks!

The results of cte query execution are different in v6.1.6 and v6.5.2 .

1. Minimal reproduce step (Required)

create table a (id int);
create table b (id int);
create table temp (lvl int);
with a as (select 8 as id from dual),
maxa as (select max(id) as max_id from a),
b as (
with recursive temp as (
select 1 as lvl from dual
union all
select lvl+1 from temp, maxa where lvl < max_id
)
select * from temp
)
select * from b;

2. What did you expect to see? (Required)

v6.1.6
mysql> with a as (select 8 as id from dual),
-> maxa as (select max(id) as max_id from a),
-> b as (
-> with recursive temp as (
-> select 1 as lvl from dual
-> union all
-> select lvl+1 from temp, maxa where lvl < max_id
-> )
-> select * from temp
-> )
-> select * from b;
+------+
| lvl |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+------+
8 rows in set (0.01 sec)

3. What did you see instead (Required)

v6.5.2
mysql> with a as (select 8 as id from dual),
-> maxa as (select max(id) as max_id from a),
-> b as (
-> with recursive temp as (
-> select 1 as lvl from dual
-> union all
-> select lvl+1 from temp, maxa where lvl < max_id
-> )
-> select * from temp
-> )
-> select * from b;
ERROR 3575 (HY000): Recursive Common Table Expression '' can contain neither aggregation nor window functions in recursive query block

4. What is your TiDB version? (Required)

v6.5.2

[snowballbear]

/found gs

[YangKeao]

Bisect shows this issue is caused by #37800 🤔 .

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
The issue didn't show up in v6.3.0 but started to show up in v6.4.0.

[kennedy8312]

Regression Analysis
PR caused this regression: #37800