pingcap / pingcap/tidb

explain explore's output binding cannot work.

Open
#64,460 0 comments 0 reactions 1 assignee Claimed by @qw4990 View on GitHub
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)

```
CREATE TABLE `t1` (
`c1` bigint NOT NULL AUTO_INCREMENT,
`c2` bigint NOT NULL,
`c3` decimal(38,0) NOT NULL,
`c4` tinyint(1) NOT NULL DEFAULT '1',
`c5` varchar(255) DEFAULT NULL,
`c6` bigint DEFAULT NULL,
`c7` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`c8` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`c1`) /*T![clustered_index] CLUSTERED */,
KEY `i1` (`c2`),
KEY `i2` (`c6`),
KEY `i3` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

explain explore SELECT
*
FROM
`t1` `r2_0`
LEFT JOIN `t1` `r3_0`
ON `r3_0`.`c1` > `r2_0`.`c1`
AND `r3_0`.`c6` = `r2_0`.`c6`
WHERE `r3_0`.`c1` IS NULL;
```
its Query Plan looks like this.

```
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
| Selection_9 | 9990.00 | root | | isnull(test.t1.c1) |
| └─HashJoin_19 | 12487.50 | root | | left outer join, left side:TableReader_28, equal:[eq(test.t1.c6, test.t1.c6)], other cond:gt(test.t1.c1, test.t1.c1) |
| ├─TableReader_35(Build) | 9990.00 | root | | data:Selection_34 |
| │ └─Selection_34 | 9990.00 | cop[tikv] | | not(isnull(test.t1.c6)) |
| │ └─TableFullScan_33 | 10000.00 | cop[tikv] | table:r3_0 | keep order:false, stats:pseudo |
| └─TableReader_28(Probe) | 10000.00 | root | | data:TableFullScan_27 |
| └─TableFullScan_27 | 10000.00 | cop[tikv] | table:r2_0 | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
```

Its result looks like this.

```
...
...
...
***************************[ 3. row ]***************************
statement | SELECT
*
FROM
`t1` `r2_0`
LEFT JOIN `t1` `r3_0`
ON `r3_0`.`c1` > `r2_0`.`c1`
AND `r3_0`.`c6` = `r2_0`.`c6`
WHERE `r3_0`.`c1` IS NULL
binding_hint | merge_join(@`sel_1` `test`.`r2_0`), use_index(@`sel_1` `test`.`r2_0` `i2`), order_index(@`sel_1` `test`.`r2_0` `i2`), use_index(@`sel_1` `test`.`r3_0` `i2`), order_index(@`sel_1` `test`.`r3_0` `i2`)
plan | Selection 9990.00 root isnull(test.t1.c1)
└─MergeJoin 12487.50 root left outer join, left side:IndexLookUp, left key:test.t1.c6, right key:test.t1.c6, other cond:gt(test.t1.c1, test.t1.c1)
├─IndexLookUp(Probe) 10000.00 root
│ ├─IndexFullScan(Build) 10000.00 cop[tikv] table:r2_0, index:i2(c6) keep order:true, stats:pseudo
│ └─TableRowIDScan(Probe) 10000.00 cop[tikv] table:r2_0 keep order:false, stats:pseudo
└─IndexLookUp(Build) 9990.00 root
├─IndexFullScan(Build) 9990.00 cop[tikv] table:r3_0, index:i2(c6) keep order:true, stats:pseudo
└─TableRowIDScan(Probe) 9990.00 cop[tikv] table:r3_0 keep order:false, stats:pseudo
plan_digest | 6b843b11d0dc834f91edcccf2b52028fcce8be36f6af54b80e34a52481dffecf
avg_latency | 0
exec_times | 0
avg_scan_rows | 0
avg_returned_rows | 0
latency_per_returned_row | 0
scan_rows_per_returned_row | 0
recommend |
reason |
explain_analyze | EXPLAIN ANALYZE '6b843b11d0dc834f91edcccf2b52028fcce8be36f6af54b80e34a52481dffecf'
binding | CREATE GLOBAL BINDING FROM HISTORY USING PLAN DIGEST '6b843b11d0dc834f91edcccf2b52028fcce8be36f6af54b80e34a52481dffecf'
...
...
```

then we run this create binding command which is from explain explore.

```
CREATE GLOBAL BINDING FROM HISTORY USING PLAN DIGEST '375e6f78dde8ca522e8ec3422b36273666b629f27e36143e05ca7438766c3ee9';
admin reload bindings;

explain SELECT
*
FROM
`t1` `r2_0`
LEFT JOIN `t1` `r3_0`
ON `r3_0`.`c1` > `r2_0`.`c1`
AND `r3_0`.`c6` = `r2_0`.`c6`
WHERE `r3_0`.`c1` IS NULL;
```

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

Explain explore also give us the hints. we use hints and get right plan.

```
DROP GLOBAL BINDING FOR SQL DIGEST '7f15b517669ac5e74d6206b0d1f83a7d7106185b398461bd948543745b941967';
explain SELECT /*+ merge_join(@`sel_1` `test`.`r2_0`), use_index(@`sel_1` `test`.`r2_0` `i2`), order_index(@`sel_1` `test`.`r2_0` `i2`), use_index(@`sel_1` `test`.`r3_0` `i2`), order_index(@`sel_1` `test`.`r3_0` `i2`) */
*
FROM
`t1` `r2_0`
LEFT JOIN `t1` `r3_0`
ON `r3_0`.`c1` > `r2_0`.`c1`
AND `r3_0`.`c6` = `r2_0`.`c6`
WHERE `r3_0`.`c1` IS NULL
```

```
+--------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| Selection_9 | 9990.00 | root | | isnull(test.t1.c1) |
| └─MergeJoin_12 | 12487.50 | root | | left outer join, left side:IndexLookUp_17, left key:test.t1.c6, right key:test.t1.c6, other cond:gt(test.t1.c1, test.t1.c1) |
| ├─IndexLookUp_23(Build) | 9990.00 | root | | |
| │ ├─IndexFullScan_21(Build) | 9990.00 | cop[tikv] | table:r3_0, index:i2(c6) | keep order:true, stats:pseudo |
| │ └─TableRowIDScan_22(Probe) | 9990.00 | cop[tikv] | table:r3_0 | keep order:false, stats:pseudo |
| └─IndexLookUp_17(Probe) | 10000.00 | root | | |
| ├─IndexFullScan_15(Build) | 10000.00 | cop[tikv] | table:r2_0, index:i2(c6) | keep order:true, stats:pseudo |
| └─TableRowIDScan_16(Probe) | 10000.00 | cop[tikv] | table:r2_0 | keep order:false, stats:pseudo |
+--------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+
```

### 3. What did you see instead (Required)
```
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
| Selection_9 | 9990.00 | root | | isnull(test.t1.c1) |
| └─HashJoin_19 | 12487.50 | root | | left outer join, left side:TableReader_28, equal:[eq(test.t1.c6, test.t1.c6)], other cond:gt(test.t1.c1, test.t1.c1) |
| ├─TableReader_35(Build) | 9990.00 | root | | data:Selection_34 |
| │ └─Selection_34 | 9990.00 | cop[tikv] | | not(isnull(test.t1.c6)) |
| │ └─TableFullScan_33 | 10000.00 | cop[tikv] | table:r3_0 | keep order:false, stats:pseudo |
| └─TableReader_28(Probe) | 10000.00 | root | | data:TableFullScan_27 |
| └─TableFullScan_27 | 10000.00 | cop[tikv] | table:r2_0 | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------+
```

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

```
+-----------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-763-g9e38acfdbf |
| Edition: Community |
| Git Commit Hash: 9e38acfdbfaf2bcfd1ad81da127980c3fd84253c |
| Git Branch: HEAD |
| UTC Build Time: 2025-11-12 14:15:24 |
| GoVersion: go1.23.12 |
| Race Enabled: false |
| Check Table Before Drop: false |
| Store: tikv |
| Kernel Type: Classic |
+-----------------------------------------------------------+
```

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.