pingcap / pingcap/tidb

Predicate pushdown with function referencing other columns may be considered

Open
#45,218 0 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```
mysql> create table t1(a int not null primary key, b varchar(10), key idx(b));
mysql> create table t2(a int not null primary key, b varchar(10), key idx(b));
mysql> explain select count(*) from t1 join t2 on t1.a=t2.a where t1.a=(select a from t1 where b=concat(t2.b,'') limit 1);
+-----------------------------------+----------+-----------+---------------+-----------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------------+----------+-----------+---------------+-----------------------------------------------------+
| HashAgg_18 | 1.00 | root | | funcs:count(1)->Column#7 |
| └─Apply_21 | 12500.00 | root | | inner join, equal:[eq(test.t1.a, test.t1.a)] |
| ├─MergeJoin_22(Build) | 12500.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a |
| │ ├─TableReader_46(Build) | 10000.00 | root | | data:TableFullScan_45 |
| │ │ └─TableFullScan_45 | 10000.00 | cop[tikv] | table:t2 | keep order:true, stats:pseudo |
| │ └─TableReader_44(Probe) | 10000.00 | root | | data:TableFullScan_43 |
| │ └─TableFullScan_43 | 10000.00 | cop[tikv] | table:t1 | keep order:true, stats:pseudo |
| └─Limit_55(Probe) | 12500.00 | root | | offset:0, count:1 |
| └─TableReader_62 | 12500.00 | root | | data:Limit_61 |
| └─Limit_61 | 12500.00 | cop[tikv] | | offset:0, count:1 |
| └─Selection_58 | 12500.00 | cop[tikv] | | eq(test.t1.b, concat(test.t2.b, "")) |
| └─TableFullScan_57 | 15625.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-----------------------------------+----------+-----------+---------------+-----------------------------------------------------+
12 rows in set (0.01 sec)
```
The table `t1` on the probe side may use `idx` (b=concat(t2.b,'')) to do index lookup, as `t2.b` can be resolved in MergeJoin result.

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.