ygf11 opened a new issue, #4732:
URL: https://github.com/apache/arrow-datafusion/issues/4732
**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**
Add a new rule in optimizer.
**Describe alternatives you've considered**
**Additional context**
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
