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

["Julian Hyde (Jira)" <ji...@apache.org>]

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

Julian Hyde commented on CALCITE-5429:
--------------------------------------

I’d describe this as a “correlated scalar sub-query in the SELECT clause of a GROUP BY query”. 

I’m not sure whether standard SQL allows this, because the rules for what columns can be used as correlating variables are unclear. Do you know of other DBs that allow this?

I do think it would be useful if we could find a semantics and support it. The fix would need some negative tests - queries that are invalid because they correlate on non-grouped columns or expressions. 

> 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
>            Priority: Major
>
> 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)