[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1338:
---------------------------------
Description:
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.
was:
# 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.
> 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)
