[jira] [Created] (CALCITE-3605) Semantics of relation operator changed after decorrelated

["Wang Yanlin (Jira)" <ji...@apache.org>]

Wang Yanlin created CALCITE-3605:
------------------------------------

             Summary: Semantics of relation operator changed after decorrelated
                 Key: CALCITE-3605
                 URL: https://issues.apache.org/jira/browse/CALCITE-3605
             Project: Calcite
          Issue Type: Bug
            Reporter: Wang Yanlin


For sql
{code:java}
final String sql = "select deptno from (select deptno, empno from emp) p\n"
        + "where empno in\n"
        + "(select count(*) from dept where p.deptno = dept.deptno)";
{code}
before decorrelated, the relnode tree is
{code:java}
LogicalProject(DEPTNO=[$0])
  LogicalProject(DEPTNO=[$0], EMPNO=[$1])
    LogicalFilter(condition=[=($1, $2)])
      LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
        LogicalProject(DEPTNO=[$7], EMPNO=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
        LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
          LogicalProject($f0=[0])
            LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
after decorrelatd, the relnode tree is
{code:java}
LogicalProject(DEPTNO=[$0])
  LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
    LogicalProject(DEPTNO=[$7], EMPNO=[$0])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
      LogicalProject(DEPTNO=[$0], $f0=[0])
        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
however, these two relnode trees have different semantics.

Assume, the data in dept and emp is as below
{code:java}
EMP (deptno, empno)
3, 6
10, 1
8, 0

DEPT (deptno, name)
3, "a"
10, "b"
{code}
The output of the sql should be 
 +------+
|10|
|8|

+------+

but the output of the relnode after decorrelated is
 +------+
|10|

+------+



--
This message was sent by Atlassian Jira
(v8.3.4#803005)