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