Predicate pushdown with function referencing other columns may be considered
- 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
Assessment
This issue has not been assessed yet.