explain explore's output binding cannot work.
- 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
Assessment
This issue has not been assessed yet.