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

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15403286#comment-15403286 ] 

Julian Hyde commented on CALCITE-1338:
--------------------------------------

It seems that JoinProjectTransposeRule simply not fire in this case. It shouldn't pull an expression through a the null-generating side of an outer join. Do you agree?

> JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
> --------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-1338
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1338
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: YI XINGLU
>            Assignee: Julian Hyde
>
> SQL: 
> {noformat}
> SELECT * 
> FROM dept a 
> LEFT JOIN (
>   SELECT b.name, 1 FROM dept b
> ) AS b 
> ON a.name=b.name
> {noformat}
> Selected rule set:
> {noformat}
>   SubQueryRemoveRule.JOIN
>   JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {noformat}
> Optimized logical plan:
> {noformat}
> 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]])
> {noformat}
> The right logical plan should be
> {noformat}
> 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]])
> {noformat}
> In summary, the RexLiteral project expression will make logical plan get different results when it's right child or parent node of left outer join.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)