apache / apache/datafusion

Deduplicate equijoin predicate in optimizer

Open
#4,732 0 comments 0 reactions 0 assignees View on GitHub
enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.