Deduplicate equijoin predicate in optimizer
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
**Is your feature request related to a problem or challenge? Please describe what you are trying to do.**
For correlated subqueries, datafusion will deduplicate equijoin predicate.
```sql
❯ explain select * from test0 where test0.a in (select a from test1 where test0.a = test1.a);
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: test0.a, test0.b, test0.c |
| | LeftSemi Join: test0.a = __sq_1.a |
| | TableScan: test0 projection=[a, b, c] |
| | SubqueryAlias: __sq_1 |
| | Projection: test1.a AS a |
| | TableScan: test1 projection=[a] |
```
But for explict join, it will not:
```sql
❯ explain select * from test0 inner join test1 on test0.a = test1.a and test0.a = test1.a;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: test0.a, test0.b, test0.c, test1.a, test1.b, test1.c |
| | Inner Join: test0.a = test1.a, test0.a = test1.a |
| | TableScan: test0 projection=[a, b, c] |
| | TableScan: test1 projection=[a, b, c] |
```
In spark, it also support this:
```sql
> explain extended select * from test0 inner join test1 on test0.a = test1.a and test0.a = test1.a;
== Optimized Logical Plan ==
Join Inner, (a#439 = a#442)
:- Filter isnotnull(a#439)
: +- HiveTableRelation [`test`.`test0`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#439, b#440, c#441], Partition Cols: []]
+- Filter isnotnull(a#442)
+- HiveTableRelation [`test`.`test1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [a#442, b#443, c#444], Partition Cols: []]
```
**Describe the solution you'd like**
Deduplicate equijoin predicate in optimizer
**Describe alternatives you've considered**
**Additional context**
Contributor guide
Research direction
Start by reproducing the two EXPLAIN queries in the issue and trace the optimizer's handling of explicit join predicates. Done means the optimized logical plan retains a single equijoin predicate for the duplicated INNER JOIN while preserving the existing correlated-subquery behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100