[jira] [Created] (CALCITE-5429) Correlated subquery over aggregate with grouping throws an error

["Aleksey Plekhanov (Jira)" <ji...@apache.org>]

Aleksey Plekhanov created CALCITE-5429:
------------------------------------------

             Summary: Correlated subquery over aggregate with grouping throws an error
                 Key: CALCITE-5429
                 URL: https://issues.apache.org/jira/browse/CALCITE-5429
             Project: Calcite
          Issue Type: Bug
            Reporter: Aleksey Plekhanov


Queries like:
{noformat}
select (select dname from "scott".dept where dept.deptno = emp.deptno) from "scott".emp group by deptno; {noformat}
Throw an error:
{noformat}
> java.lang.AssertionError: Required columns {1} not subset of left columns {0}
>     at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
>     at org.apache.calcite.util.Litmus.check(Litmus.java:76)
>     at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:145)
>     at org.apache.calcite.rel.core.Correlate.<init>(Correlate.java:109)
>     at org.apache.calcite.rel.logical.LogicalCorrelate.<init>(LogicalCorrelate.java:72)
>     at org.apache.calcite.rel.logical.LogicalCorrelate.create(LogicalCorrelate.java:115)
>     at org.apache.calcite.rel.core.RelFactories$CorrelateFactoryImpl.createCorrelate(RelFactories.java:440)
>     at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:2864)
>     at org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteScalarQuery(SubQueryRemoveRule.java:136){noformat}
It happens because when subquery is converted to {{{}LogicalCorrelate{}}}, left side of {{LogicalCorrelate}} has different row type than expected by correlate field access on the right side. {{RexFieldAccess}} expects original (table) row type for {{$cor0}} variable, where column {{DEPTNO}} has index 1, but after project and aggregate is applied on the left side, only one column in a row type remains and corresponding column has index 0.

Plan for this query:
{noformat}
LogicalProject(EXPR$0=[$SCALAR_QUERY({
LogicalProject(DNAME=[$1])
  LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
    LogicalTableScan(table=[[scott, DEPT]])
})]), id = 25096
  LogicalAggregate(group=[{0}]), id = 25094
    LogicalProject(DEPTNO=[$7]), id = 25092
      LogicalTableScan(table=[[scott, EMP]]), id = 25076{noformat}
If we change query to:
{noformat}
select (select dname from "scott".dept where dept.deptno = emp.empno) from "scott".emp group by deptno; {noformat}
It silently return the wrong result ({{{}DEPTNO{}}} is treated as {{EMPNO}} and error is no throwed). 

 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)