pingcap / pingcap/tidb

LEADING hint generated for a query with CTE cannot be reapplied because CTE-internal tables are emitted as @sel_N names

Open
#68,977 2 comments 0 reactions 0 assignees View on GitHub
contribution severity/moderate sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.