outer join simplification should be applied in normalization phase
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
drop table if exists t;
create table t(a int primary key, b int);
insert into t values (1, 11), (4, 44), (2, 22), (3, 33);
set session tidb_executor_concurrency = 4;
set @@session.tidb_hash_join_concurrency = 5;
set @@session.tidb_distsql_scan_concurrency = 15;
mysql> explain select max(a.a) from t a left join t b on a.a = b.a;
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_14 | 1.00 | root | | funcs:max(test.t.a)->Column#5 |
| └─IndexJoin_70 | 1.25 | root | | left outer join, inner:TableReader_67, left side:TopN_52, outer key:test.t.a, inner key:test.t.a, equal cond:eq(test.t.a, test.t.a) |
| ├─TopN_52(Build) | 1.00 | root | | test.t.a:desc, offset:0, count:1 |
| │ └─TableReader_58 | 1.00 | root | | data:TopN_57 |
| │ └─TopN_57 | 1.00 | cop[tikv] | | test.t.a:desc, offset:0, count:1 |
| │ └─TableFullScan_34 | 4.00 | cop[tikv] | table:a | keep order:false, stats:pseudo |
| └─TableReader_67(Probe) | 1.00 | root | | data:TableRangeScan_66 |
| └─TableRangeScan_66 | 1.00 | cop[tikv] | table:b | range: decided by [test.t.a], keep order:false, stats:pseudo |
+----------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
```
currently we saw old-default-off transformation rule `EliminateOuterJoinBelowAggregation` could simplify this query to the plan as below
```
HashAgg_36 1.00 root funcs:max(planner__cascades__integration.t.a)->Column#5
└─Limit_38 1.00 root offset:0, count:1
└─TableReader_43 1.00 root data:Limit_44
└─Limit_44 1.00 cop[tikv] offset:0, count:1
└─TableFullScan_42 1.00 cop[tikv] table:a keep order:true, desc, stats:pseudo
```
since this is a `always-good-rule` and it's valuable as above, we should migrate it to normalization phase, or current logical optimization phase, to make it public.
Contributor guide
Assessment
This issue has not been assessed yet.