Weihua Zhang created CALCITE-7540:
-------------------------------------
Summary: 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
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)
