[GitHub] [arrow-datafusion] askoa commented on issue #4795: Physical planner makes assumption that join keys are specified in left = right order

[GitBox <gi...@apache.org>]

askoa commented on issue #4795:
URL: https://github.com/apache/arrow-datafusion/issues/4795#issuecomment-1380647966

   I found part of the problem. The rule `common_sub_expression_eliminate` swallows one of the `EXIST (<subquery>)`. Below are detailed steps
   
   logical plan before `decorrelate_where_exists` -> `... EXISTS(<subquery>) AND (EXISTS(<subquery>) OR EXISTS(<subquery>)) .... subquery: ... subquery: ... subquery: ...`
   
   the rule `decorrelate_where_exists` mutates the first `EXIST` into a `LeftSemi Join`
   
   logical plan after `decorrelate_where_exists ` and before `common_sub_expression_eliminate` -> `...(EXISTS(<subquery>) OR EXISTS(<subquery>))  ... subquery: ... subquery: ... LeftSemi Join: ...`
   
   the rule `common_sub_expression_eliminate` runs:
     - The rule maintains a HashMap of expressions to no. of occurrences. The expression `EXISTS(<subquery>)` is recognized as a single expression occurring twice.
   
   https://github.com/apache/arrow-datafusion/blob/1844d39eb92f04e483095f491ff07da3a2f67f25/datafusion/optimizer/src/common_subexpr_eliminate.rs#L451-L454
   
     - The rule creates a projection that merges the two similar expressions and thus making the second subquery irrelevant.
   
   logical plan after `common_sub_expression_eliminate`:  `...(EXISTS(<subquery>) OR EXISTS(<subquery>))  ... Projection: EXISTS<subquery>... subquery: ... LeftSemi Join: ...`
   
   As I am new to both sql optimization and to data fusion I would like to use this thread to discuss potential solutions. I assume adding unique identifiers or aliases to subqueries might be part of the solution. Adding unique identifiers or aliases will stop `common_sub_expression_elimitate` treating both subqueries as the same. 


-- 
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: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org