Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1338

JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression

    XMLWordPrintableJSON

    Details

      Description

      SQL:

      SELECT * 
      FROM dept a 
      LEFT JOIN (
        SELECT b.name, 1 FROM dept b
      ) AS b 
      ON a.name=b.name
      

      Selected rule set:

        SubQueryRemoveRule.JOIN
        JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
      

      Optimized logical plan:

      LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
        LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[CAST($3):INTEGER])
          LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
            LogicalJoin(condition=[=($3, $1)], joinType=[left])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      

      The right logical plan should be

      LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
        LogicalJoin(condition=[=($2, $1)], joinType=[left])
          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
          LogicalProject(NAME=[$1], EXPR$1=[1])
            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      

      Summary

      The RexLiteral project expression will make logical plan get different results when it's right child or parent node of left outer join.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Chunwei Lei Chunwei Lei
                Reporter:
                yee Yi Xinglu
              • Votes:
                0 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 50m
                  50m