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

              Chunwei Lei Chunwei Lei
              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