[
https://issues.apache.org/jira/browse/CALCITE-5429?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17646236#comment-17646236
]
Aleksey Plekhanov commented on CALCITE-5429:
--------------------------------------------
[~julianhyde], I've checked this query on MySQL, PostgreSQL, Oracle and MSSQL.
All these DBs process query as expected. Also, all these DBs throw an error
(not GROUP BY expression) in case of not groupped column (second example).
> 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)
