[CALCITE-1338] JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Chunwei Lei
• Reporter: Yi Xinglu
• Created: 02/Aug/16 02:57
• Updated: 22/Jul/19 09:42
• Resolved: 15/Apr/19 03:39

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.

Issue Links

• blocks: HIVE-15457 Return path failures
• is related to: HIVE-14594 CBO: Calcite Operator To Hive Operator(Calcite Return Path): Fix wrong result in join_filters_overlap
• is related to: CALCITE-1526 Use Strong to infer whether a predicate's inputs may be null
• links to: GitHub Pull Request #1153