pingcap / pingcap/tidb

planner: predicates cannot be pushed into sub-queries for HashJoin

Open
#36,551 7 comments 2 reactions 2 assignees Claimed by @hawkingrei View on GitHub
feature/reviewing good first issue help wanted sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```
mysql> CREATE TABLE t1 (
-> risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );

mysql> CREATE TABLE t2 (
-> risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
-> -- risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );

mysql> explain select /*+ hash_join(t1) */ t1.risk_id
-> from t1
-> where t1.risk_id = 'xxxx'
-> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_18 | 8.00 | root | | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_24(Build) | 10000.00 | root | | index:IndexFullScan_23 |
| │ └─IndexFullScan_23 | 10000.00 | cop[tikv] | table:t2, index:risk_id(risk_id) | keep order:false, stats:pseudo |
| └─IndexReader_20(Probe) | 10.00 | root | | index:IndexRangeScan_19 |
| └─IndexRangeScan_19 | 10.00 | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
```

The predicate `t1.risk_id = 'xxxx'` should be propagated to `t2` and the optimizer should generate a `IndexRangeScan` for `t2`.
If remove `COLLATE utf8mb4_general_ci`, this predicate can be pushed down.
```
mysql> CREATE TABLE t2 (
-> -- risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
-> risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> explain select /*+ hash_join(t1) */ t1.risk_id
-> from t1
-> where t1.risk_id = 'xxxx'
-> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_21 | 8.00 | root | | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_25(Build) | 10.00 | root | | index:IndexRangeScan_24 |
| │ └─IndexRangeScan_24 | 10.00 | cop[tikv] | table:t2, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
| └─IndexReader_23(Probe) | 10.00 | root | | index:IndexRangeScan_22 |
| └─IndexRangeScan_22 | 10.00 | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
```

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.