Leonid Chistov created CALCITE-5952: ---------------------------------------
Summary: 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 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") .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)