pingcap / pingcap/tidb

OR will forbid pushing predicates down across the join

Open
#42,400 0 comments 0 reactions 1 assignee Claimed by @winoros View on GitHub
sig/planner type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Plan enhancement when using `or` in different table

### 1. Minimal reproduce step (Required)
```
create table t1(id int,name1 varchar(50),name2 varchar(50),name3 varchar(50),name4 varchar(50),name5 varchar(50),key idx_id(id),key idx_name1(name1));
create table t2(id int,name1 varchar(50),name2 varchar(50),name3 varchar(50),name4 varchar(50),name5 varchar(50),key idx_id(id),key idx_name1(name1));
```

### 2. What did you expect to see? (Required)
Refer to oracle,union -> concatenation
```
mysql> explain select * from t1 a,t2 b where a.name1 =b.name1 and a.id=20123 union select * from t1 a,t2 b where a.name1 =b.name1 and b.id =20123;
+-------------------------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Concatenation_18 | 20.48 | root | | group by:Column#29, Column#30, Column#31, Column#32, Column#33, Column#34, Column#35, Column#36, Column#37, Column#38, Column#39, Column#40, funcs:firstrow(Column#29)->Column#29, funcs:firstrow(Column#30)->Column#30, funcs:firstrow(Column#31)->Column#31, funcs:firstrow(Column#32)->Column#32, funcs:firstrow(Column#33)->Column#33, funcs:firstrow(Column#34)->Column#34, funcs:firstrow(Column#35)->Column#35, funcs:firstrow(Column#36)->Column#36, funcs:firstrow(Column#37)->Column#37, funcs:firstrow(Column#38)->Column#38, funcs:firstrow(Column#39)->Column#39, funcs:firstrow(Column#40)->Column#40 | |
| ├─IndexHashJoin_28 | 12.49 | root | | inner join, inner:IndexLookUp_25, outer key:test.t1.name1, inner key:test.t2.name1, equal cond:eq(test.t1.name1, test.t2.name1) |
| │ ├─IndexLookUp_69(Build) | 9.99 | root | | |
| │ │ ├─IndexRangeScan_66(Build) | 10.00 | cop[tikv] | table:a, index:idx_id(id) | range:[20123,20123], keep order:false, stats:pseudo |
| │ │ └─Selection_68(Probe) | 9.99 | cop[tikv] | | not(isnull(test.t1.name1)) |
| │ │ └─TableRowIDScan_67 | 10.00 | cop[tikv] | table:a | keep order:false, stats:pseudo |
| │ └─IndexLookUp_25(Probe) | 12.49 | root | | |
| │ ├─Selection_24(Build) | 12.49 | cop[tikv] | | not(isnull(test.t2.name1)) |
| │ │ └─IndexRangeScan_22 | 12.50 | cop[tikv] | table:b, index:idx_name1(name1) | range: decided by [eq(test.t2.name1, test.t1.name1)], keep order:false, stats:pseudo |
| │ └─TableRowIDScan_23(Probe) | 12.49 | cop[tikv] | table:b | keep order:false, stats:pseudo |
| └─Projection_81 | 12.49 | root | | test.t1.id, test.t1.name1, test.t1.name2, test.t1.name3, test.t1.name4, test.t1.name5, test.t2.id, test.t2.name1, test.t2.name2, test.t2.name3, test.t2.name4, test.t2.name5 |
| └─IndexHashJoin_89 | 12.49 | root | | inner join, inner:IndexLookUp_86, outer key:test.t2.name1, inner key:test.t1.name1, equal cond:eq(test.t2.name1, test.t1.name1) |
| ├─IndexLookUp_130(Build) | 9.99 | root | | |
| │ ├─IndexRangeScan_127(Build) | 10.00 | cop[tikv] | table:b, index:idx_id(id) | range:[20123,20123], keep order:false, stats:pseudo |
| │ └─Selection_129(Probe) | 9.99 | cop[tikv] | | not(isnull(test.t2.name1)) |
| │ └─TableRowIDScan_128 | 10.00 | cop[tikv] | table:b | keep order:false, stats:pseudo |
| └─IndexLookUp_86(Probe) | 12.49 | root | | |
| ├─Selection_85(Build) | 12.49 | cop[tikv] | | not(isnull(test.t1.name1)) |
| │ └─IndexRangeScan_83 | 12.50 | cop[tikv] | table:a, index:idx_name1(name1) | range: decided by [eq(test.t1.name1, test.t2.name1)], keep order:false, stats:pseudo |
| └─TableRowIDScan_84(Probe) | 12.49 | cop[tikv] | table:a | keep order:false, stats:pseudo |
+-------------------------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.01 sec)

```
### 3. What did you see instead (Required)
```
mysql> explain select * from t1 a,t2 b where a.name1 =b.name1 and (a.id=20123 or b.id =20123)
-> ;
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
| HashJoin_37 | 12487.50 | root | | inner join, equal:[eq(test.t1.name1, test.t2.name1)], other cond:or(eq(test.t1.id, 20123), eq(test.t2.id, 20123)) |
| ├─TableReader_55(Build) | 9990.00 | root | | data:Selection_54 |
| │ └─Selection_54 | 9990.00 | cop[tikv] | | not(isnull(test.t2.name1)) |
| │ └─TableFullScan_53 | 10000.00 | cop[tikv] | table:b | keep order:false, stats:pseudo |
| └─TableReader_49(Probe) | 9990.00 | root | | data:Selection_48 |
| └─Selection_48 | 9990.00 | cop[tikv] | | not(isnull(test.t1.name1)) |
| └─TableFullScan_47 | 10000.00 | cop[tikv] | table:a | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
```
### 4. What is your TiDB version? (Required)

Master

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.