[jira] [Commented] (CALCITE-3495) RelDecorrelator generate plan with different semantics when handle Aggregate

["Jin Xing (Jira)" <ji...@apache.org>]

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

Jin Xing commented on CALCITE-3495:
-----------------------------------

By nested loop join, when deptno=0 in dept, it will generate a corresponding record of 0 from the subquery;

However in the Aggregate of decorrelated plan, there's no chance to generate such a record of 0;

IMHO we should fail the decorrelation for Aggregate when the child contains correlated ref but at the same time the Aggregate can generate rows with no input, e.g. the aggregate function is SUM, COUNT

> 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)