[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17757955#comment-17757955 ]
Leonid Chistov commented on CALCITE-5952: ----------------------------------------- [~rubenql] The expected result is mentioned in issue description (I have called it "original plan", meaning that plan that is constructed by RelBuilder is not supposed to be changed by SEMI_JOIN_JOIN_TRANSPOSE): {code:java} LogicalJoin(condition=[=($9, $12)], joinType=[semi]) LogicalJoin(condition=[=($7, $8)], joinType=[left]) LogicalTableScan(table=[[scott, EMP]]) LogicalTableScan(table=[[scott, DEPT]]) LogicalTableScan(table=[[scott, BONUS]]) {code} > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --------------------------------------------------------------------------- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug > Affects Versions: 1.35.0 > Reporter: Leonid Chistov > Assignee: Leonid Chistov > Priority: Major > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function<RelBuilder, RelNode> relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)