EXPLAIN FORMAT='hint' may omit hint-level QB names for MERGE_JOIN hints exported from CTE query blocks
- 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
Assessment
This issue has not been assessed yet.