[
https://issues.apache.org/jira/browse/CALCITE-3495?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jin Xing updated CALCITE-3495:
------------------------------
Description:
Given below Sql and data
{code:java}
SELECT deptno FROM dept A where deptno in
(SELECT count(1) FROM emp B where A.deptno = B.deptno)
-----------------------------------------------------
Data of dept:
deptno
0
1
----------------
Data of emp:
deptno
1{code}
>From the Sql semantics by nest-loop join, we will expect the result as
{code:java}
Result:
deptno
0
1{code}
However the decorrelated plan is as below:
{code:java}
LogicalProject(DEPTNO=[$0])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[=($1, $0)])
LogicalProject(EXPR$0=[$1], DEPTNO=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(DEPTNO=[$7], $f0=[1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
The result of this plan is as below:
{code:java}
Result:
deptno
1
{code}
As we can see the two above results are different.
> RelDecorrelator generate plan with different semantics when handle Aggregate
> ----------------------------------------------------------------------------
>
> Key: CALCITE-3495
> URL: https://issues.apache.org/jira/browse/CALCITE-3495
> Project: Calcite
> Issue Type: Bug
> Reporter: Jin Xing
> Priority: Major
>
> Given below Sql and data
> {code:java}
> SELECT deptno FROM dept A where deptno in
> (SELECT count(1) FROM emp B where A.deptno = B.deptno)
> -----------------------------------------------------
> Data of dept:
> deptno
> 0
> 1
> ----------------
> Data of emp:
> deptno
> 1{code}
> From the Sql semantics by nest-loop join, we will expect the result as
> {code:java}
> Result:
> deptno
> 0
> 1{code}
> However the decorrelated plan is as below:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($1, $0)])
> LogicalProject(EXPR$0=[$1], DEPTNO=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(DEPTNO=[$7], $f0=[1])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> The result of this plan is as below:
> {code:java}
> Result:
> deptno
> 1
> {code}
> As we can see the two above results are different.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
