pingcap / pingcap/tidb

EXPLAIN FORMAT='hint' may omit hint-level QB names for MERGE_JOIN hints exported from CTE query blocks

Open
#68,925 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
Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```sql
create table t1(a int, b int, key(a));
create table t2(a int, b int, key(a));
create table t3(a int, b int, key(a));
create table t4(a int, b int, key(a));
create table t5(a int, b int, key(a));
create table t6(a int, b int, key(a));
create table t7(a int, b int, key(a));

explain format='hint'
with channel as (
select t2.a
from t2
join t3 on t2.a = t3.a
join t6 on t3.a = t6.a
union
select t4.a
from t4
join t5 on t4.a = t5.a
join t7 on t5.a = t7.a
)
select *
from t1
join channel on t1.a = channel.a;
```

Then replay the exported hint string on the same query and check warnings.

### 2. What did you expect to see? (Required)
For join-method hints exported from inner CTE query blocks, the hint-level query block name should be emitted consistently when it is required for replay.

For this query, the exported `MERGE_JOIN` hints for inner query block tables should keep the same scope as the corresponding `LEADING` hints, for example:
```text
merge_join(@sel_2 `test`.`t6`@`sel_2`)
merge_join(@sel_3 `test`.`t7`@`sel_3`)
```

The exported hint string should be replayable without warnings.

### 3. What did you see instead (Required)
The exported hint string mixed scoped and unscoped forms.

The `LEADING` hints were emitted with hint-level query block names, for example:
```text
leading(@sel_2 `test`.`t2`@`sel_2`, `test`.`t3`@`sel_2`, `test`.`t6`@`sel_2`)
leading(@sel_3 `test`.`t4`@`sel_3`, `test`.`t5`@`sel_3`, `test`.`t7`@`sel_3`)
```

But the related `MERGE_JOIN` hints were emitted without hint-level query block names, for example:
```text
merge_join(`test`.`t6`@`sel_2`)
merge_join(`test`.`t7`@`sel_3`)
```

After replaying the exported hints, warnings were produced, including cases like:
```text
There are no matching table names for (t6, t7) in optimizer hint /*+ MERGE_JOIN(t6, t7) */
```

This makes the exported hint string inconsistent and not replay-clean for this CTE case.

### 4. What is your TiDB version? (Required)
Please replace this section with the output of:
```sql
select tidb_version();
```

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.