Repository navigation
Closed
Labels
enhancementNew feature or requestNew feature or request
Description
Is your feature request related to a problem or challenge? Please describe what you are trying to do.
Currently some equality joins which contain normal expressions will run as cross join.
For example:
❯ explain select * from test0 as t0 inner join test1 as t1 on t0.c0 + 1 = t1.c0;
+---------------+-----------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-----------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: t0.c0, t1.c0 |
| | Filter: CAST(t0.c0 AS Int64) + Int64(1) = CAST(t1.c0 AS Int64) |
| | CrossJoin: |
| | SubqueryAlias: t0 |
| | TableScan: test0 projection=[c0] |
| | SubqueryAlias: t1 |
| | TableScan: test1 projection=[c0] |
| physical_plan | ProjectionExec: expr=[c0@0 as c0, c0@1 as c0] |
| | CoalesceBatchesExec: target_batch_size=4096 |
| | FilterExec: CAST(c0@0 AS Int64) + 1 = CAST(c0@1 AS Int64) |
| | CrossJoinExec |
| | RepartitionExec: partitioning=RoundRobinBatch(32) |
| | ParquetExec: limit=None, partitions=[test0.parquet], projection=[c0] |
| | RepartitionExec: partitioning=RoundRobinBatch(32) |
| | ParquetExec: limit=None, partitions=[test1.parquet], projection=[c0] |
| | |
+---------------+-----------------------------------------------------------------------------------------------------------+
2 rows in set. Query took 0.008 seconds.We can move these to hash-join to improve performance.
Describe the solution you'd like
Move these equality joins from cross join to join in logical and physical plan.
In addition, it also helps to fix:
Describe alternatives you've considered
Additional context
Reacted by Daniël Heres
Metadata
Metadata
Assignees
Labels
enhancementNew feature or requestNew feature or request
Activity
liukun4515 commentedon Nov 8, 2022
maybe it related to the
subqueryand the type coercion. @andygroveygf11 commentedon Nov 9, 2022
Yes, it is relative to
type coercion.The issue is not very detailed, Let me add a little more.
Currently our
LogicalPlan::Join(..)only supportscolumn equalityfor join, so does physical plan.https://github.com/apache/arrow-datafusion/blob/a32fb657d1caec634cb53979b2f7ef2fad224905/datafusion/expr/src/logical_plan/plan.rs#L1488
https://github.com/apache/arrow-datafusion/blob/a32fb657d1caec634cb53979b2f7ef2fad224905/datafusion/core/src/physical_plan/joins/hash_join.rs#L125
We can extend
columntonormal expression, then datafusion can support more equality joins.That means:
equality joins.cross join.For #2877, since the join unit is expression, we can wrap
castintype coercionphase when data types of two side is not same.Dandandan commentedon Nov 9, 2022
Sounds like a good plan. Any expression of the form
e1=e2should be supported in equality joins.liukun4515 commentedon Nov 9, 2022
cc @mingmwang
mingmwang commentedon Nov 9, 2022
I can take a look at the issue.
My current preference is to keep the equal join condition as columns, but add another projection to project the expression to normal columns and push down the projection.
The major reason is
A inner join B on (udf1(A.a) = udf2(B.b))The first time calculation happens when during the
Repartition,Repartition exprwill take the exprs now and need to evaluate.The second time calculation happens when building the hashtable or comparing.
A inner join B on (A.a = B.b = B.c)This expression is actually a valid expression and can be evaluated, but could cause wrong join result, similar to cross join.
Maybe we can also take a complex approach and check the expression's complexity
logical plan
physical plan
logical plan
physical plan
return Error.
A inner join B on (A.a = B.b = B.c)mingmwang commentedon Nov 9, 2022
And I just added the EquivalenceProperties/EquivalentClass to the physical execution plan, if we plan to support expressions as equal join conditions, I need to enhance the EquivalenceProperties/EquivalentClass related logic as well.
@yahoNanJing @alamb
alamb commentedon Nov 9, 2022
For what it is worth, the existing join logic contains hard coded assumptions that the join is between two columns in several places. Changing the join logic (which is already complicated and will likely only get more so) is likely to be quite challenging
So therefore I agree with @mingmwang's proposal of:
I don't think there would be any performance difference between casting in a Projection or in the Join itself and I think it would keep the Join significantly less complicated.
I would suggest not handling casts in the Join but instead work on improving the other optimizer simplification rules to remove the casts. Like the expression
Can be rewritten into
Which we would still have to have a
projectionto evaluatet0.c0 + 1butt1.c0would need no processingThis type of unwrapping is already done in https://github.com/apache/arrow-datafusion/blob/master/datafusion/optimizer/src/unwrap_cast_in_comparison.rs
ygf11 commentedon Nov 10, 2022
Thank you @mingmwang @alamb.
Agree with your suggestion to implement this with another projection. I will work on it.