This is an automated email from the ASF dual-hosted git repository.
xuzifu666 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new e3b5c5600f [CALCITE-6104] Aggregate function that references outer
column should be evaluated in outer query
e3b5c5600f is described below
commit e3b5c5600f2d0d88408953e4a77654457a9b9f3c
Author: Yu Xu <[email protected]>
AuthorDate: Fri Jun 26 10:45:13 2026 +0800
[CALCITE-6104] Aggregate function that references outer column should be
evaluated in outer query
---
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 16 +
.../calcite/sql/validate/SqlConformanceEnum.java | 10 +
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 328 +++++++++++++++++++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../org/apache/calcite/test/SqlValidatorTest.java | 38 +++
core/src/test/resources/sql/agg.iq | 227 ++++++++++++++
9 files changed, 631 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 01ea9cbda0..c6e1a4dbdc 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -930,6 +930,9 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("Extended columns not allowed under the current SQL conformance
level")
ExInst<SqlValidatorException> extendNotAllowed();
+ @BaseMessage("Aggregate function referencing outer column is not allowed
under the current SQL conformance level")
+ ExInst<SqlValidatorException> correlatedAggregateNotAllowed();
+
@BaseMessage("Rolled up column ''{0}'' is not allowed in {1}")
ExInst<SqlValidatorException> rolledUpNotAllowed(String column, String
context);
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 84cf77e3c9..7734f1b2fe 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -184,4 +184,8 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
@Override public boolean isDistinctOnAllowed() {
return SqlConformanceEnum.DEFAULT.isDistinctOnAllowed();
}
+
+ @Override public boolean isCorrelatedAggregateAllowed() {
+ return SqlConformanceEnum.DEFAULT.isCorrelatedAggregateAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index db0dd693a9..703dfd4a14 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -731,4 +731,20 @@ default boolean isColonFieldAccessAllowed() {
* false otherwise.
*/
boolean isDistinctOnAllowed();
+
+ /**
+ * Whether an aggregate function inside a scalar sub-query is allowed to
+ * reference columns from an outer query.
+ *
+ * <p>This is not allowed by the SQL standard, but is supported by some
+ * databases, including SQLite, DuckDB and SQL Server.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BABEL},
+ * {@link SqlConformanceEnum#LENIENT};
+ * false otherwise.
+ */
+ default boolean isCorrelatedAggregateAllowed() {
+ return false;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 4ed6cb93c2..681835724f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -562,4 +562,14 @@ public enum SqlConformanceEnum implements SqlConformance {
return false;
}
}
+
+ @Override public boolean isCorrelatedAggregateAllowed() {
+ switch (this) {
+ case BABEL:
+ case LENIENT:
+ return true;
+ default:
+ return false;
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index e142893891..7bed86a410 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -189,4 +189,8 @@ protected SqlDelegatingConformance(SqlConformance delegate)
{
@Override public boolean isDistinctOnAllowed() {
return delegate.isDistinctOnAllowed();
}
+
+ @Override public boolean isCorrelatedAggregateAllowed() {
+ return delegate.isCorrelatedAggregateAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 331c8849a6..5d667fe502 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -5618,6 +5618,17 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
// First pass, ensure that aliases are unique. "*" and "TABLE.*" items
// are ignored.
+ // Rewrite scalar sub-queries whose single select item is an aggregate
+ // over outer columns. The aggregate belongs to the outer query per SQL
+ // standard, but only if the current conformance allows this non-standard
+ // correlated-aggregate construct. If the conformance does not allow it,
+ // validate that no such construct is present.
+ if (config.conformance().isCorrelatedAggregateAllowed()) {
+ rewriteOuterAggregatesInSelectList(select);
+ } else {
+ checkNoCorrelatedAggregatesInSelectList(select);
+ }
+
// Validate SELECT list. Expand terms of the form "*" or "TABLE.*".
final SqlValidatorScope selectScope = getSelectScope(select);
final List<SqlNode> expandedSelectItems = new ArrayList<>();
@@ -5677,6 +5688,323 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
return typeFactory.createStructType(fieldList);
}
+ /**
+ * Rewrites scalar sub-queries in the SELECT list whose single select item is
+ * an aggregate function whose arguments reference only outer columns. Per
the
+ * SQL standard, such aggregates belong to the outer query.
+ *
+ * <p>The algorithm is:
+ * <ol>
+ * <li>For each item in the SELECT list, check whether it is a scalar
+ * sub-query, optionally wrapped in {@code AS} or {@code WITH}.
+ * <li>Inside the sub-query, require the SELECT list to contain exactly one
+ * item, and that item to be an aggregate function call.
+ * <li>Require every argument of the aggregate to reference only columns from
+ * the outer query (no inner columns), and to contain no nested sub-queries.
+ * <li>If all conditions hold, lift the aggregate out of the sub-query: keep
+ * the aggregate in the outer SELECT list, and guard it with
+ * {@code (SELECT 1 FROM ... LIMIT 1) IS NOT NULL}. The result is equivalent
+ * because the aggregate is evaluated over the outer rows, but yields NULL
+ * whenever the inner query has no rows (as the original scalar sub-query
+ * would).
+ * <li>If the outer query becomes an aggregate query as a result, upgrade its
+ * SELECT clause scope from {@link SelectScope} to
+ * {@link AggregatingSelectScope}.
+ * </ol>
+ *
+ * <p>For example,
+ * <blockquote><pre>
+ * WITH aa(a) AS (VALUES 1, 2, 3),
+ * t(x) AS (VALUES 10, 20, 30)
+ * SELECT (SELECT sum(a) FROM t) FROM aa
+ * </pre></blockquote>
+ * is rewritten to
+ * <blockquote><pre>
+ * WITH aa(a) AS (VALUES 1, 2, 3),
+ * t(x) AS (VALUES 10, 20, 30)
+ * SELECT CASE WHEN (SELECT 1 FROM t LIMIT 1) IS NOT NULL
+ * THEN sum(a) END FROM aa
+ * </pre></blockquote>
+ */
+ private void rewriteOuterAggregatesInSelectList(SqlSelect select) {
+ final SqlNodeList selectItems = select.getSelectList();
+ if (selectItems == null) {
+ return;
+ }
+ final SqlValidatorScope selectScope = getSelectScope(select);
+ final boolean wasAggregate = isAggregate(select);
+ for (int i = 0; i < selectItems.size(); i++) {
+ final SqlNode selectItem = selectItems.get(i);
+ final SqlNode rewrittenItem =
+ rewriteOuterAggregateItem(selectScope, selectItem);
+ if (rewrittenItem != selectItem) {
+ selectItems.set(i, rewrittenItem);
+ }
+ }
+ // The rewrite may have introduced an aggregate into a query that was not
+ // previously aggregate. Update the SELECT clause scope accordingly so that
+ // subsequent validation and conversion see an AggregatingSelectScope.
+ if (!wasAggregate && isAggregate(select)) {
+ SqlValidatorScope scope =
+ clauseScopes.get(IdPair.of(select, Clause.SELECT));
+ if (scope instanceof AggregatingSelectScope) {
+ scope = ((AggregatingSelectScope) scope).getParent();
+ }
+ clauseScopes.put(IdPair.of(select, Clause.SELECT),
+ new AggregatingSelectScope(
+ requireNonNull(scope, "scope"), select, false));
+ }
+ }
+
+ /**
+ * Validates that the SELECT list does not contain a correlated aggregate
+ * when the current conformance does not allow it.
+ */
+ private void checkNoCorrelatedAggregatesInSelectList(SqlSelect select) {
+ final SqlNodeList selectItems = select.getSelectList();
+ if (selectItems == null) {
+ return;
+ }
+ final SqlValidatorScope selectScope = getSelectScope(select);
+ for (SqlNode selectItem : selectItems) {
+ final SqlSelect subQuery = findScalarSubQuerySelect(selectItem);
+ if (subQuery == null) {
+ continue;
+ }
+ final SqlNode aggExpr = findCorrelatedAggregate(subQuery, selectScope);
+ if (aggExpr != null) {
+ throw newValidationError(aggExpr,
+ RESOURCE.correlatedAggregateNotAllowed());
+ }
+ }
+ }
+
+ /**
+ * Rewrites a single SELECT list item if it is a scalar sub-query containing
+ * an aggregate over outer columns.
+ *
+ * @return the rewritten expression, or {@code selectItem} if no rewrite
applies
+ */
+ private SqlNode rewriteOuterAggregateItem(SqlValidatorScope parentScope,
+ SqlNode selectItem) {
+ final SqlSelect subQuery = findScalarSubQuerySelect(selectItem);
+ if (subQuery == null) {
+ return selectItem;
+ }
+ // Unwrap AS.
+ SqlNode expr = selectItem;
+ @Nullable SqlIdentifier alias = null;
+ if (SqlUtil.isCallTo(selectItem, SqlStdOperatorTable.AS)) {
+ SqlCall asCall = (SqlCall) selectItem;
+ expr = asCall.operand(0);
+ alias = asCall.operand(1);
+ }
+ final SqlBasicCall scalarSubQuery = (SqlBasicCall) expr;
+ final SqlNode query = scalarSubQuery.operand(0);
+ final SqlNode rewrittenSubQuery =
+ rewriteOuterAggregate(query, subQuery, parentScope);
+ if (rewrittenSubQuery == query) {
+ return selectItem;
+ }
+ SqlNode result = rewrittenSubQuery;
+ if (alias != null) {
+ result =
+ SqlStdOperatorTable.AS.createCall(selectItem.getParserPosition(),
+ rewrittenSubQuery, alias);
+ }
+ return result;
+ }
+
+ /**
+ * Extracts the {@link SqlSelect} from a SELECT list item that is a scalar
+ * sub-query, optionally wrapped in {@code AS}.
+ *
+ * @return the sub-query's SELECT, or {@code null} if the item is not a
+ * scalar sub-query
+ */
+ private @Nullable SqlSelect findScalarSubQuerySelect(SqlNode selectItem) {
+ SqlNode expr = selectItem;
+ if (SqlUtil.isCallTo(selectItem, SqlStdOperatorTable.AS)) {
+ expr = ((SqlCall) selectItem).operand(0);
+ }
+ if (!SqlUtil.isCallTo(expr, SqlStdOperatorTable.SCALAR_QUERY)) {
+ return null;
+ }
+ final SqlNode query = ((SqlBasicCall) expr).operand(0);
+ return query instanceof SqlWith
+ ? (SqlSelect) ((SqlWith) query).body
+ : query instanceof SqlSelect
+ ? (SqlSelect) query
+ : null;
+ }
+
+ /**
+ * Finds the single select item of a scalar sub-query that can be lifted to
+ * the outer query. Such an item contains at least one aggregate function and
+ * references only columns from the outer query, so per the SQL standard it
+ * belongs to the outer query.
+ *
+ * <p>The item may be a bare aggregate call (e.g. {@code sum(a)}) or an
+ * expression over aggregates of outer columns (e.g. {@code sum(a) +
sum(b)}).
+ * It is not lifted if any column reference resolves to an inner column,
which
+ * covers mixed cases such as {@code sum(a) + sum(x)} where {@code x} is an
+ * inner column.
+ *
+ * @return the liftable select item, or {@code null} if none applies
+ */
+ private @Nullable SqlNode findCorrelatedAggregate(SqlSelect subQuery,
+ SqlValidatorScope parentScope) {
+ final SqlNodeList subSelectItems =
SqlNonNullableAccessors.getSelectList(subQuery);
+ if (subSelectItems.size() != 1) {
+ return null;
+ }
+ if (subQuery.getGroup() != null && !subQuery.getGroup().isEmpty()) {
+ return null;
+ }
+ SqlNode subSelectItem = subSelectItems.get(0);
+ if (SqlUtil.isCallTo(subSelectItem, SqlStdOperatorTable.AS)) {
+ subSelectItem = ((SqlCall) subSelectItem).operand(0);
+ }
+ if (aggFinder.findAgg(subSelectItem) == null) {
+ return null;
+ }
+ final SqlValidatorScope subScope = getSelectScope(subQuery);
+ final SqlValidatorScope operandScope =
+ subScope instanceof AggregatingSelectScope
+ ? ((AggregatingSelectScope) subScope).parent
+ : subScope;
+ if (!referencesOnlyOuterColumns(subSelectItem, parentScope, operandScope))
{
+ return null;
+ }
+ return subSelectItem;
+ }
+
+ /**
+ * Rewrites a scalar sub-query whose single select item is an aggregate
+ * function whose arguments reference only outer columns. The aggregate is
+ * pulled out to the enclosing query, and the sub-query's select item is
+ * replaced with the constant 1.
+ *
+ * @return the rewritten sub-query expression, or {@code subQuery} if no
rewrite applies
+ */
+ private SqlNode rewriteOuterAggregate(SqlNode originalQuery,
+ SqlSelect subQuery, SqlValidatorScope parentScope) {
+ final SqlNode aggExpr = findCorrelatedAggregate(subQuery, parentScope);
+ if (aggExpr == null) {
+ return originalQuery;
+ }
+ final SqlNodeList subSelectItems =
SqlNonNullableAccessors.getSelectList(subQuery);
+ // Rewrite: replace aggregate with 1 in a new sub-query, and multiply
+ // by the aggregate in the outer query.
+ final SqlLiteral one = SqlLiteral.createExactNumeric("1",
SqlParserPos.ZERO);
+ final SqlNodeList newSubSelectItems =
+ new SqlNodeList(ImmutableList.of(one),
subSelectItems.getParserPosition());
+ final SqlNodeList keywordList = (SqlNodeList)
subQuery.getOperandList().get(0);
+ final SqlSelect newSubQuery =
+ new SqlSelect(subQuery.getParserPosition(),
+ keywordList,
+ newSubSelectItems,
+ subQuery.getFrom(),
+ subQuery.getWhere(),
+ subQuery.getGroup(),
+ subQuery.getHaving(),
+ subQuery.getWindowList(),
+ subQuery.getQualify(),
+ subQuery.getOrderList(),
+ subQuery.getOffset(),
+ subQuery.getFetch(),
+ subQuery.getHints(),
+ subQuery.getDistinctOn());
+ // Ensure the rewritten sub-query still returns at most one row, because
+ // removing the aggregate may otherwise produce multiple rows.
+ if (newSubQuery.getFetch() == null) {
+ newSubQuery.setFetch(
+ SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO));
+ }
+ final SqlNode newQuery = originalQuery instanceof SqlWith
+ ? new SqlWith(originalQuery.getParserPosition(),
+ ((SqlWith) originalQuery).withList, newSubQuery)
+ : newSubQuery;
+ registerQuery(parentScope, null, newQuery, newQuery, null, false);
+ validateQuery(newQuery, parentScope, unknownType);
+ final SqlNode scalarSubQuery =
+
SqlStdOperatorTable.SCALAR_QUERY.createCall(newQuery.getParserPosition(),
newQuery);
+
+ final SqlParserPos pos = aggExpr.getParserPosition();
+ final SqlNode condition =
+ SqlStdOperatorTable.IS_NOT_NULL.createCall(pos, scalarSubQuery);
+ return new SqlCase(pos, null,
+ new SqlNodeList(ImmutableList.of(condition), pos),
+ new SqlNodeList(ImmutableList.of(aggExpr), pos),
+ SqlLiteral.createNull(pos));
+ }
+
+ /**
+ * Returns whether an expression contains only references to columns that are
+ * outside the current select scope, and can be resolved in the parent scope.
+ */
+ private boolean referencesOnlyOuterColumns(SqlNode node,
+ SqlValidatorScope parentScope, SqlValidatorScope currentScope) {
+ // Do not rewrite if the aggregate argument contains a sub-query; the
+ // sub-query may reference inner columns, and pulling the aggregate out
+ // would change the semantics.
+ if (containsSubQuery(node)) {
+ return false;
+ }
+ // ok[0] is cleared if any identifier is not an outer reference;
+ // ok[1] is set once at least one outer column is found.
+ final boolean[] ok = {true, false};
+ node.accept(new SqlBasicVisitor<Void>() {
+ @Override public Void visit(SqlIdentifier id) {
+ if (!isOuterReference(currentScope, id)) {
+ ok[0] = false;
+ return null;
+ }
+ // The identifier must be resolvable in the parent scope; otherwise it
+ // references an intermediate scope, not the immediate enclosing query.
+ try {
+ parentScope.fullyQualify(id);
+ ok[1] = true;
+ } catch (CalciteException e) {
+ ok[0] = false;
+ }
+ return null;
+ }
+ });
+ return ok[0] && ok[1];
+ }
+
+ /**
+ * Returns whether an expression contains a sub-query.
+ */
+ private static boolean containsSubQuery(SqlNode node) {
+ final boolean[] found = {false};
+ node.accept(new SqlBasicVisitor<Void>() {
+ @Override public Void visit(SqlCall call) {
+ if (call.getKind().belongsTo(SqlKind.QUERY)) {
+ found[0] = true;
+ return null;
+ }
+ return super.visit(call);
+ }
+ });
+ return found[0];
+ }
+
+ /**
+ * Returns whether an identifier resolves to a scope which is not the
+ * supplied one.
+ */
+ private boolean isOuterReference(SqlValidatorScope scope, SqlIdentifier id) {
+ final SqlQualified fqId = scope.fullyQualify(id);
+ if (fqId.prefixLength <= 0) {
+ return false;
+ }
+ final SqlValidatorScope.ResolvedImpl resolved = new
SqlValidatorScope.ResolvedImpl();
+ scope.resolve(fqId.prefix(), catalogReader.nameMatcher(), false, resolved);
+ return resolved.count() == 1 && !resolved.only().scope.isWithin(scope);
+ }
+
/**
* Validates an expression.
*
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 27e52cbce9..f4f16d7326 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -303,6 +303,7 @@ IntervalMustBeNonNegative=Interval must be non-negative
''{0}''
CannotUseWithinWithoutOrderBy=Must contain an ORDER BY clause when WITHIN is
used
FirstColumnOfOrderByMustBeTimestamp=First column of ORDER BY must be of type
TIMESTAMP
ExtendNotAllowed=Extended columns not allowed under the current SQL
conformance level
+CorrelatedAggregateNotAllowed=Aggregate function referencing outer column is
not allowed under the current SQL conformance level
RolledUpNotAllowed=Rolled up column ''{0}'' is not allowed in {1}
SchemaExists=Schema ''{0}'' already exists
SchemaInvalidType=Invalid schema type ''{0}''; valid values: {1}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index efa86f9c0b..228d90026b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8469,6 +8469,44 @@ void testGroupExpressionEquivalenceParams() {
.columnType("INTEGER NOT NULL");
}
+ @Test void testCorrelatedAggregateConformance() {
+ final String sql = "select (select ^sum(sal)^ from dept) from emp";
+ // LENIENT and BABEL allow the non-standard correlated-aggregate construct.
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
+ sql(sql).withConformance(SqlConformanceEnum.BABEL).ok();
+ // DEFAULT and STRICT_2003 do not.
+ final String err =
+ "Aggregate function referencing outer column is not allowed under the
current SQL conformance level";
+ sql(sql).withConformance(SqlConformanceEnum.DEFAULT).fails(err);
+ sql(sql).withConformance(SqlConformanceEnum.STRICT_2003).fails(err);
+
+ // A non-numeric aggregate over an outer column (MAX of a VARCHAR) is also
+ // lifted to the outer query; the CASE-based rewrite is type-agnostic.
+ final String nonNumeric =
+ "select (select max(ename) from dept) from emp";
+ sql(nonNumeric).withConformance(SqlConformanceEnum.LENIENT).ok();
+ sql(nonNumeric).withConformance(SqlConformanceEnum.BABEL).ok();
+
+ // An expression over aggregates of outer columns is lifted to the outer
+ // query as a whole, and is subject to the same conformance rules as a bare
+ // aggregate.
+ final String twoAggs =
+ "select (select ^sum(sal) + sum(comm)^ from dept) from emp";
+ sql(twoAggs).withConformance(SqlConformanceEnum.LENIENT).ok();
+ sql(twoAggs).withConformance(SqlConformanceEnum.BABEL).ok();
+ sql(twoAggs).withConformance(SqlConformanceEnum.DEFAULT).fails(err);
+ sql(twoAggs).withConformance(SqlConformanceEnum.STRICT_2003).fails(err);
+
+ // Mixed case: an expression aggregating both an outer column (sal) and an
+ // inner column (deptno) references an inner column, so it is not lifted
and
+ // is not rejected under any conformance level.
+ final String mixed =
+ "select (select sum(sal) + sum(deptno) from dept) from emp";
+ sql(mixed).withConformance(SqlConformanceEnum.LENIENT).ok();
+ sql(mixed).withConformance(SqlConformanceEnum.DEFAULT).ok();
+ sql(mixed).withConformance(SqlConformanceEnum.STRICT_2003).ok();
+ }
+
@Test void testAggregateInOrderByFails() {
sql("select empno from emp order by ^sum(empno)^")
.fails(ERR_AGG_IN_ORDER_BY);
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 5478f6bb6e..b637a6cbc1 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -4301,4 +4301,231 @@ GROUP BY GROUPING SETS ((deptno), ());
!ok
+# [CALCITE-6104] Aggregate function that references outer column should be
evaluated in outer query
+# Correlated aggregates are a non-standard extension.
+# They are rejected under DEFAULT conformance.
+!use scott
+
+SELECT (SELECT sum(sal) FROM dept) FROM emp;
+Aggregate function referencing outer column is not allowed under the current
SQL conformance level
+!error
+
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT count(a) FROM xx LIMIT 1) AS ca
+FROM aa;
+Aggregate function referencing outer column is not allowed under the current
SQL conformance level
+!error
+
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa,
+ (SELECT count(a) FROM xx LIMIT 1) AS ca
+FROM aa;
+Aggregate function referencing outer column is not allowed under the current
SQL conformance level
+!error
+
+# Allowed under LENIENT conformance.
+!use scott-lenient
+
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa
+FROM aa;
++----+
+| SA |
++----+
+| 6 |
++----+
+(1 row)
+
+!ok
+
+# A non-numeric aggregate (MAX over a VARCHAR outer column) is also lifted to
+# the outer query. The CASE-based rewrite is type-agnostic, so the aggregate is
+# evaluated over the outer rows and produces a single row.
+SELECT (SELECT max(ename) FROM dept) AS m FROM emp;
++------+
+| M |
++------+
+| WARD |
++------+
+(1 row)
+
+!ok
+
+# Inner table is empty: the scalar sub-query contributes NULL
+WITH aa (a) AS (VALUES 1, 2, 3),
+ empty_xx (x) AS (SELECT 1 FROM emp WHERE 1 = 0)
+SELECT (SELECT sum(a) FROM empty_xx LIMIT 1) AS sa
+FROM aa;
++----+
+| SA |
++----+
+| |
++----+
+(1 row)
+
+!ok
+
+# Outer table is empty: the outer aggregate has no input rows
+WITH empty_aa (a) AS (SELECT 1 FROM emp WHERE 1 = 0),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa
+FROM empty_aa;
++----+
+| SA |
++----+
+| |
++----+
+(1 row)
+
+!ok
+
+# Aggregated column contains a mix of values and NULLs
+WITH aa (a) AS (VALUES 1, NULL, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa
+FROM aa;
++----+
+| SA |
++----+
+| 4 |
++----+
+(1 row)
+
+!ok
+
+# Aggregate with no column references aggregates at the innermost level
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(1) FROM xx LIMIT 1) AS s
+FROM aa;
++---+
+| S |
++---+
+| 3 |
+| 3 |
+| 3 |
++---+
+(3 rows)
+
+!ok
+
+# Aggregate referencing only inner column aggregates at the inner level
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(x) FROM xx LIMIT 1) AS s
+FROM aa;
++----+
+| S |
++----+
+| 60 |
+| 60 |
+| 60 |
++----+
+(3 rows)
+
+!ok
+
+# An expression over aggregates of different outer columns is lifted to the
+# outer query as a whole: sum(a) = 1+2+3 = 6, sum(b) = 10+20+30 = 60, so the
+# result is a single row 66.
+WITH aa (a, b) AS (VALUES (1, 10), (2, 20), (3, 30)),
+ xx (x) AS (VALUES 100, 200, 300)
+SELECT (SELECT sum(a) + sum(b) FROM xx LIMIT 1) AS s
+FROM aa;
++----+
+| S |
++----+
+| 66 |
++----+
+(1 row)
+
+!ok
+
+# A single aggregate over an arithmetic expression of outer columns is also
+# lifted: sum(a + b) = (1+10) + (2+20) + (3+30) = 66.
+WITH aa (a, b) AS (VALUES (1, 10), (2, 20), (3, 30)),
+ xx (x) AS (VALUES 100, 200, 300)
+SELECT (SELECT sum(a + b) FROM xx LIMIT 1) AS s
+FROM aa;
++----+
+| S |
++----+
+| 66 |
++----+
+(1 row)
+
+!ok
+
+# Lifted expression with an empty inner table: the guard sub-query yields no
+# row, so the whole expression is NULL.
+WITH aa (a, b) AS (VALUES (1, 10), (2, 20), (3, 30)),
+ empty_xx (x) AS (SELECT 1 FROM emp WHERE 1 = 0)
+SELECT (SELECT sum(a) + sum(b) FROM empty_xx LIMIT 1) AS s
+FROM aa;
++---+
+| S |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
+# Mixed case: the expression aggregates an outer column (a) and an inner
+# column (x). It references an inner column, so it is not lifted and keeps the
+# pre-existing correlated-sub-query behaviour (evaluated once per outer row).
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) + sum(x) FROM xx LIMIT 1) AS s
+FROM aa;
++----+
+| S |
++----+
+| 63 |
+| 66 |
+| 69 |
++----+
+(3 rows)
+
+!ok
+
+# Two scalar sub-queries with outer-only aggregates are both lifted to the
outer query
+WITH aa (a) AS (VALUES 1, 2, 3),
+ xx (x) AS (VALUES 10, 20, 30)
+SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa,
+ (SELECT count(a) FROM xx LIMIT 1) AS ca
+FROM aa;
++----+----+
+| SA | CA |
++----+----+
+| 6 | 3 |
++----+----+
+(1 row)
+
+!ok
+
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)],
expr#4=[null:INTEGER], expr#5=[CASE($t3, $t0, $t4)], expr#6=[null:BIGINT],
expr#7=[CASE($t3, $t1, $t6)], SA=[$t5], CA=[$t7])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[left])
+ EnumerableAggregate(group=[{}], agg#0=[SUM($0)], agg#1=[COUNT()])
+ EnumerableValues(tuples=[[{ 1 }, { 2 }, { 3 }]])
+ EnumerableLimit(fetch=[1])
+ EnumerableValues(tuples=[[{ 1 }, { 1 }, { 1 }]])
+!plan
+
+SELECT (SELECT sum(sal) FROM dept) AS sum_sal
+FROM emp;
++----------+
+| SUM_SAL |
++----------+
+| 29025.00 |
++----------+
+(1 row)
+
+!ok
+
+!use scott
+
# End agg.iq
