[
https://issues.apache.org/jira/browse/CALCITE-7540?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7540:
------------------------------------
Labels: pull-request-available (was: )
> Correlated outer reference in HAVING of grouped subquery is incorrectly
> reported as not grouped
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-7540
> URL: https://issues.apache.org/jira/browse/CALCITE-7540
> Project: Calcite
> Issue Type: Bug
> Reporter: Weihua Zhang
> Assignee: Weihua Zhang
> Priority: Major
> Labels: pull-request-available
>
> Calcite incorrectly rejects a valid correlated aggregate subquery when an
> outer query column is referenced in the `HAVING` clause of the grouped
> subquery.
> The outer reference is treated as if it were a column from the inner
> aggregate query, and Calcite reports that it is "not being grouped".
> {code:sql}
> SELECT *
> FROM dept d
> INNER JOIN emp e
> ON e.sal < (
> SELECT MAX(e2.sal)
> FROM emp e2
> WHERE e2.job = e.job
> GROUP BY e2.job
> HAVING MIN(e2.deptno) = d.deptno
> );
> {code}
> {noformat}
> SELECT *
> FROM dept d
> INNER JOIN emp e
> ON e.sal < (
> SELECT MAX(e2.sal)
> FROM emp e2
> WHERE e2.job = e.job
> GROUP BY e2.job
> HAVING MIN(e2.deptno) = d.deptno
> );
> java.sql.SQLException: Error while executing SQL "SELECT *
> FROM dept d
> INNER JOIN emp e
> ON e.sal < (
> SELECT MAX(e2.sal)
> FROM emp e2
> WHERE e2.job = e.job
> GROUP BY e2.job
> HAVING MIN(e2.deptno) = d.deptno
> )": From line 9, column 29 to line 9, column 36: Expression 'D.DEPTNO' is not
> being grouped
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
> at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:317)
> at net.hydromatic.quidem.Quidem.access$2600(Quidem.java:54)
> at net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1778)
> at net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:985)
> at net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1522)
> at net.hydromatic.quidem.Quidem.execute(Quidem.java:204)
> at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:353)
> at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:543)
> at org.apache.calcite.test.CoreQuidemTest.main(CoreQuidemTest.java:54)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 9,
> column 29 to line 9, column 36: Expression 'D.DEPTNO' is not being grouped
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:511)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:960)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:6321)
> at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:144)
> at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:43)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
> at
> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:125)
> at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:986)
> at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:253)
> at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:43)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:175)
> at
> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:233)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateHavingClause(SqlValidatorImpl.java:5399)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:4427)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:96)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1466)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1437)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:292)
> at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276)
> at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:503)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:7044)
> at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:152)
> at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276)
> at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:503)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:7044)
> at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:152)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:5357)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:4034)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3887)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:4412)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:96)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1466)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1437)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:292)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1403)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:1109)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:628)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:258)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:675)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:526)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:494)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:654)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:678)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
> ... 10 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression
> 'D.DEPTNO' is not being grouped
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
> at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:511)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:605)
> ... 58 more
> {noformat}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
