pingcap / pingcap/tidb

parser: EXPLAIN FORMAT='hint' restores hint-level QB_NAME inside nested LEADING

Open
#69,907 3 comments 0 reactions 0 assignees View on GitHub
affects-8.5 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 three tables:

```sql
DROP TABLE IF EXISTS t1, t2, t3;

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE TABLE t3 (a INT);
```

The original hint can be parsed and used without an optimizer hint syntax warning:

```sql
SELECT /*+ QB_NAME(qb), LEADING(@qb (t1,t2),t3) */
t1.a, t2.a, t3.a
FROM t1
JOIN t2 ON t1.a = t2.a
JOIN t3 ON t2.a = t3.a;

SHOW WARNINGS;
```

Run `EXPLAIN FORMAT='hint'` for the same query:

```sql
EXPLAIN FORMAT='hint'
SELECT /*+ QB_NAME(qb), LEADING(@qb (t1,t2),t3) */
t1.a, t2.a, t3.a
FROM t1
JOIN t2 ON t1.a = t2.a
JOIN t3 ON t2.a = t3.a;
```

Copy the returned hint into the original query and run it again, for example:

```sql
SELECT /*+ qb_name(`qb`), leading((@`qb` `t1`, `t2`), `t3`) */
t1.a, t2.a, t3.a
FROM t1
JOIN t2 ON t1.a = t2.a
JOIN t3 ON t2.a = t3.a;

SHOW WARNINGS;
```

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

`EXPLAIN FORMAT='hint'` should return a syntactically valid, replayable hint. The hint-level query block name should remain outside the nested list, for example:

```text
leading(@`qb` (`t1`, `t2`), `t3`)
```

Replaying the returned hint should not produce an optimizer hint syntax warning.

### 3. What did you see instead (Required)

`EXPLAIN FORMAT='hint'` returns the hint-level query block name inside the first nested list, for example:

```text
qb_name(`qb`), leading((@`qb` `t1`, `t2`), `t3`)
```

Replaying this output produces an optimizer hint syntax error (1064), so the generated hint is not replayable and the `LEADING` constraint is ignored.

The issue is triggered when a `LEADING` hint has a hint-level `QB_NAME` and its first element is a parenthesized list, such as `LEADING(@qb (t1,t2),t3)`. Flat forms such as `LEADING(@qb t1,t2)` are not affected.

### 4. What is your TiDB version? (Required)

Observed on `8.0.11-TiDB-v8.5.7` (release-8.5).

Related: #63253 and #68867.

Contributor guide

Open the contributing guide

Research direction

Start by running the provided three-table SQL reproduction and compare the original LEADING hint with EXPLAIN FORMAT='hint' output. Trace the parser and hint-restoration path for nested LEADING lists; done means the generated hint keeps @qb outside the nested list and replays without optimizer hint syntax warnings.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.