LEADING hint generated for a query with CTE cannot be reapplied because CTE-internal tables are emitted as @sel_N names
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
Create four simple tables:
```sql
create table t1(a int);
create table t2(a int, b int);
create table t3(a int);
create table t4(a int);
```
The current code generates a `LEADING` hint that references `test.t2@sel_2` for the CTE side. Reapplying that hint to the same SQL reproduces the problem:
```sql
with pm as (
select a, sum(b) as s
from t2
group by a
)
select /*+ leading(`test`.`t1`, `test`.`t3`, `test`.`t4`, `test`.`t2`@`sel_2`) */ *
from t1
join t3 on t1.a = t3.a
join t4 on t3.a = t4.a
left join pm on t4.a = pm.a;
show warnings;
```
The warning is:
```sql
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ LEADING(t1, t3, t4, t2) */. Maybe you can use the table alias name |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
```
### 2. What did you expect to see? (Required)
If TiDB generates a `LEADING` hint for a query plan, that hint should be replayable on the same SQL.
For a query involving a CTE, TiDB should either:
- generate a replayable representation for the CTE side in `LEADING`, or
- avoid emitting CTE-internal tables in the outer `LEADING` hint.
Reapplying the generated hint should not invalidate the whole `LEADING` hint.
### 3. What did you see instead (Required)
TiDB emits a `LEADING` hint that contains `test.t2@sel_2`, which is an internal table reference from the CTE query block.
When the hint is reapplied to the outer query, `t2` cannot be matched as a valid table name in that scope, so TiDB reports warning 1815 and the whole `LEADING` hint becomes inapplicable.
In other words, the generated join-order hint for this CTE query is not round-trippable.
### 4. What is your TiDB version? (Required)
Contributor guide
Assessment
This issue has not been assessed yet.