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 7e646c9c20 [CALCITE-7493] Support constant-result aggregates (e.g.,
STDDEV_POP, STDDEV) over GROUP BY keys
7e646c9c20 is described below
commit 7e646c9c20fd29f41ad3ec393dc923c7cb1da6aa
Author: Yu Xu <[email protected]>
AuthorDate: Sun May 24 17:59:18 2026 +0800
[CALCITE-7493] Support constant-result aggregates (e.g., STDDEV_POP,
STDDEV) over GROUP BY keys
---
.../AggregateReduceFunctionsOnGroupKeysRule.java | 69 ++++-
.../calcite/sql/SqlConstantValueAggFunction.java | 55 ++++
.../apache/calcite/sql/fun/SqlAvgAggFunction.java | 27 +-
...ggregateReduceFunctionsOnGroupKeysRuleTest.java | 114 +++++++++
...AggregateReduceFunctionsOnGroupKeysRuleTest.xml | 283 +++++++++++++++++++++
core/src/test/resources/sql/agg-reduce.iq | 174 +++++++++++++
6 files changed, 716 insertions(+), 6 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsOnGroupKeysRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsOnGroupKeysRule.java
index 987a25fea2..c31e731c07 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsOnGroupKeysRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsOnGroupKeysRule.java
@@ -31,7 +31,9 @@
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlConstantValueAggFunction;
import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.tools.RelBuilder;
import org.checkerframework.checker.nullness.qual.Nullable;
@@ -52,12 +54,19 @@
* arguments exist in the aggregate's group set or are deterministic
* expressions involving only group set columns and constants:
* <ul>
- * <li>{@code MAX}</li>
- * <li>{@code MIN}</li>
- * <li>{@code AVG}</li>
- * <li>{@code ANY_VALUE}</li>
+ * <li>{@code MAX} - the GROUP BY key value itself</li>
+ * <li>{@code MIN} - the GROUP BY key value itself</li>
+ * <li>{@code AVG} - the GROUP BY key value itself</li>
+ * <li>{@code ANY_VALUE} - the GROUP BY key value itself</li>
+ * <li>Functions implementing {@link SqlConstantValueAggFunction} such as
+ * {@code STDDEV_POP}, {@code STDDEV_SAMP}, {@code VAR_POP}, {@code
VAR_SAMP}
+ * - return their constant result</li>
* </ul>
*
+ * <p>Aggregate functions that implement {@link SqlConstantValueAggFunction}
+ * declare what value to return when applied to constant (GROUP BY key)
arguments.
+ * This enables the rule to optimize them without hard-coded type checks.
+ *
* <p>Note: This optimization preserves NULL semantics correctly. For aggregate
* functions like MAX, MIN, and ANY_VALUE, NULL values in the source columns or
* expressions are handled the same way before and after the transformation:
@@ -144,6 +153,8 @@ protected AggregateReduceFunctionsOnGroupKeysRule(Config
config) {
return null;
}
final SqlKind kind = call.getAggregation().getKind();
+ final boolean isConstantValueAgg =
+ call.getAggregation() instanceof SqlConstantValueAggFunction;
switch (kind) {
case AVG:
case MAX:
@@ -151,7 +162,9 @@ protected AggregateReduceFunctionsOnGroupKeysRule(Config
config) {
case ANY_VALUE:
break;
default:
- return null;
+ if (!isConstantValueAgg) {
+ return null;
+ }
}
final List<Integer> argList = call.getArgList();
if (argList.size() != 1) {
@@ -163,6 +176,29 @@ protected AggregateReduceFunctionsOnGroupKeysRule(Config
config) {
if (aggregate.getGroupSet().get(arg)) {
final int groupIndex = aggregate.getGroupSet().asList().indexOf(arg);
RexNode ref = RexInputRef.of(groupIndex,
aggregate.getRowType().getFieldList());
+
+ // For functions that return a constant value when applied to constant
(GROUP BY key)
+ // arguments, delegate to the function's own implementation
+ if (isConstantValueAgg) {
+ final @Nullable RexNode constantResult =
+ ((SqlConstantValueAggFunction) call.getAggregation())
+ .getConstantResult(rexBuilder, call.getType());
+ if (constantResult != null) {
+ // Handle NULL semantics: if the GROUP BY key is nullable and the
constant value
+ // is non-null (e.g., 0 for STDDEV functions), wrap in CASE to
return NULL when
+ // the key is NULL, since aggregate functions skip NULL inputs
+ if (ref.getType().isNullable()) {
+ return rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, ref),
+ rexBuilder.makeNullLiteral(call.getType()),
+ constantResult);
+ }
+ return constantResult;
+ }
+ }
+
+ // For other aggregate functions (MAX, MIN, AVG, ANY_VALUE),
+ // the value of a constant is the constant itself
if (!ref.getType().equals(call.getType())) {
ref = rexBuilder.makeCast(call.getParserPosition(), call.getType(),
ref);
}
@@ -192,6 +228,29 @@ protected AggregateReduceFunctionsOnGroupKeysRule(Config
config) {
if (translated == null) {
return null;
}
+
+ // For functions that return a constant value when applied to constant
expressions,
+ // delegate to the function's own implementation
+ if (isConstantValueAgg) {
+ final @Nullable RexNode constantResult =
+ ((SqlConstantValueAggFunction) call.getAggregation())
+ .getConstantResult(rexBuilder, call.getType());
+ if (constantResult != null) {
+ // Handle NULL semantics: if the expression is nullable and the
constant value
+ // is non-null (e.g., 0 for STDDEV functions), wrap in CASE to return
NULL when
+ // the expression evaluates to NULL, since aggregate functions skip
NULL inputs
+ if (translated.getType().isNullable()) {
+ return rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, translated),
+ rexBuilder.makeNullLiteral(call.getType()),
+ constantResult);
+ }
+ return constantResult;
+ }
+ }
+
+ // For other aggregate functions (MAX, MIN, AVG, ANY_VALUE),
+ // return the translated expression
if (!translated.getType().equals(call.getType())) {
return rexBuilder.makeCast(call.getParserPosition(), call.getType(),
translated);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlConstantValueAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/SqlConstantValueAggFunction.java
new file mode 100644
index 0000000000..6091e77636
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlConstantValueAggFunction.java
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexNode;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+/**
+ * Aggregate function that returns a constant value when applied to constant
+ * (GROUP BY key) arguments.
+ *
+ * <p>For example, statistical functions like STDDEV_POP, STDDEV_SAMP, VAR_POP,
+ * VAR_SAMP always return 0 when applied to a constant value, since there is
+ * no variation in a set of identical values.
+ *
+ * <p>This interface allows optimization rules to identify and reduce such
+ * aggregate functions without hard-coded checks for specific function types.
+ */
+public interface SqlConstantValueAggFunction {
+ /**
+ * Generates the constant result expression when this aggregate function is
+ * applied to arguments that are constant within each group (i.e., GROUP BY
keys
+ * or expressions derived only from GROUP BY keys).
+ *
+ * <p>For example:
+ * <ul>
+ * <li>{@code STDDEV_POP(constant)} returns {@code 0}
+ * <li>{@code VAR_SAMP(constant)} returns {@code 0}
+ * <li>{@code STDDEV_SAMP(constant)} returns {@code 0}
+ * </ul>
+ *
+ * @param rexBuilder Rex builder for creating the result expression
+ * @param returnType The return type of the aggregate function
+ * @return An expression representing the constant result, or null if this
function
+ * does not return a constant value for constant arguments
+ */
+ @Nullable RexNode getConstantResult(RexBuilder rexBuilder, RelDataType
returnType);
+}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java
index 97358c8dc4..93ccca5141 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java
@@ -17,13 +17,18 @@
package org.apache.calcite.sql.fun;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlAggFunction;
+import org.apache.calcite.sql.SqlConstantValueAggFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.util.Optionality;
+import org.checkerframework.checker.nullness.qual.Nullable;
+
import static com.google.common.base.Preconditions.checkArgument;
/**
@@ -31,8 +36,13 @@
* which go into it. It has precisely one argument of numeric type
* (<code>int</code>, <code>long</code>, <code>float</code>, <code>
* double</code>), and the result is the same type.
+ *
+ * <p>For statistical functions (STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP),
+ * this function implements {@link SqlConstantValueAggFunction} to support
+ * optimization when applied to constant GROUP BY keys.
*/
-public class SqlAvgAggFunction extends SqlAggFunction {
+public class SqlAvgAggFunction extends SqlAggFunction
+ implements SqlConstantValueAggFunction {
//~ Constructors -----------------------------------------------------------
@@ -86,4 +96,19 @@ public enum Subtype {
VAR_POP,
VAR_SAMP
}
+
+ @Override public @Nullable RexNode getConstantResult(RexBuilder rexBuilder,
+ RelDataType returnType) {
+ // Only statistical functions (variance and standard deviation) return 0
for constant values.
+ // AVG and other functions should not be optimized through this interface.
+ switch (kind) {
+ case STDDEV_POP:
+ case STDDEV_SAMP:
+ case VAR_POP:
+ case VAR_SAMP:
+ return rexBuilder.makeLiteral(0, returnType, true);
+ default:
+ return null;
+ }
+ }
}
diff --git
a/core/src/test/java/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.java
b/core/src/test/java/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.java
index d4de2092aa..c3a7570f06 100644
---
a/core/src/test/java/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.java
+++
b/core/src/test/java/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.java
@@ -149,6 +149,120 @@ private static RelOptFixture sql(String sql) {
sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).checkUnchanged();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7493";>[CALCITE-7493]
+ * Support constant-result aggregates (e.g., STDDEV_POP, STDDEV) over GROUP
BY keys</a>. */
+ @Test void testStatisticalFunctionStddevSampOfGroupByKey() {
+ // STDDEV_SAMP of a constant (GROUP BY key) is 0.
+ String sql = "select sal, stddev_samp(sal) as sd\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionStddevPopOfGroupByKey() {
+ // STDDEV_POP of a constant (GROUP BY key) is 0.
+ String sql = "select sal, stddev_pop(sal) as sdp\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionVarPopOfGroupByKey() {
+ // VAR_POP of a constant (GROUP BY key) is 0.
+ String sql = "select sal, var_pop(sal) as vp\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionVarSampOfGroupByKey() {
+ // VAR_SAMP of a constant (GROUP BY key) is 0
+ // (variance of all identical values is 0).
+ String sql = "select sal, var_samp(sal) as vs\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testMultipleStatisticalFunctions() {
+ // Test multiple statistical functions together.
+ String sql = "select sal, stddev_samp(sal) as sd, stddev_pop(sal) as
sdp,\n"
+ + "var_pop(sal) as vp, var_samp(sal) as vs\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionWithBinaryExpression() {
+ // Variance of binary expression (sal + deptno) where all operands are
GROUP BY keys.
+ // Since all values are constant within each group, variance is 0.
+ String sql = "select sal, var_pop(sal + deptno) as vp\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionWithConstantExpression() {
+ // Variance of expression with constant (2*sal + 100) where sal is a GROUP
BY key.
+ // Since all values are constant within each group, variance is 0.
+ String sql = "select sal, stddev_pop(2 * sal + 100) as sdp\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionWithMultipleGroupByKeys() {
+ // Variance of expression combining multiple GROUP BY keys (sal * deptno).
+ // Since all values are constant within each group, variance is 0.
+ String sql = "select sal, var_samp(sal * deptno) as vs\n"
+ + "from emp group by sal, deptno";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionWithNonGroupByColumnNoOptimization() {
+ // Negative test: expression contains only non-GROUP BY column (comm).
+ // The rule should NOT optimize because comm is not a constant within the
group.
+ String sql = "select sal, var_pop(comm) as vp\n"
+ + "from emp group by sal, deptno";
+
sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).checkUnchanged();
+ }
+
+ @Test void testStatisticalFunctionWithMixedColumnsNoOptimization() {
+ // Negative test: expression mixes GROUP BY column (sal) and non-GROUP BY
column (comm).
+ // The rule should NOT optimize because comm is not a constant within the
group.
+ String sql = "select sal, stddev_pop(sal + comm) as sdp\n"
+ + "from emp group by sal, deptno";
+
sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).checkUnchanged();
+ }
+
+ @Test void testStatisticalFunctionWithPartialExpressionNoOptimization() {
+ // Negative test: expression combines a GROUP BY key (sal) with non-GROUP
BY column (empno).
+ // The rule should NOT optimize because empno is not constant within the
group.
+ String sql = "select sal, var_samp(sal * empno) as vs\n"
+ + "from emp group by sal, deptno";
+
sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).checkUnchanged();
+ }
+
+ @Test void testStatisticalFunctionWithComplexMixNoOptimization() {
+ // Negative test: complex expression with only GROUP BY column (sal) but
+ // also referencing non-GROUP BY column (comm) in multiplication.
+ String sql = "select sal, stddev_samp(sal * 2 + comm) as sd\n"
+ + "from emp group by sal, deptno";
+
sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).checkUnchanged();
+ }
+
+ @Test void testStatisticalFunctionNullableGroupKey() {
+ // Test NULL semantics: when GROUP BY key is nullable, STDDEV(key) should
+ // handle NULL correctly. The optimization wraps result in
+ // CASE WHEN key IS NULL THEN NULL ELSE 0 END
+ String sql = "select comm, stddev_pop(comm) as sdp\n"
+ + "from empnullables group by comm";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
+ @Test void testStatisticalFunctionNullableExpression() {
+ // Test NULL semantics for expressions: when expression is nullable,
+ // STDDEV(expr) should return NULL when expr is NULL.
+ // Optimization wraps result in CASE to preserve NULL semantics
+ String sql = "select comm, stddev_samp(comm + 1) as sd\n"
+ + "from empnullables group by comm";
+ sql(sql).withRule(AGGREGATE_REDUCE_FUNCTIONS_ON_GROUP_KEYS).check();
+ }
+
@AfterAll static void checkActualAndReferenceFiles() {
fixture().diffRepos.checkActualAndReferenceFiles();
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.xml
b/core/src/test/resources/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.xml
index 2083a969d7..4f7ac67993 100644
---
a/core/src/test/resources/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/AggregateReduceFunctionsOnGroupKeysRuleTest.xml
@@ -318,6 +318,289 @@ from emp group by sal]]>
LogicalAggregate(group=[{0}], COMM_MAX=[MAX($1)])
LogicalProject(SAL=[$5], COMM=[$6])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMultipleStatisticalFunctions">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_samp(sal) as sd, stddev_pop(sal) as sdp,
+var_pop(sal) as vp, var_samp(sal) as vs
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SD=[$2], SDP=[$3], VP=[$4], VS=[$5])
+ LogicalAggregate(group=[{0, 1}], SD=[STDDEV_SAMP($0)], SDP=[STDDEV_POP($0)],
VP=[VAR_POP($0)], VS=[VAR_SAMP($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], SD=[$2], SDP=[$3], VP=[$4], VS=[$5])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], SD=[CAST(0):INTEGER], SDP=[0], VP=[0],
VS=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionNullableExpression">
+ <Resource name="sql">
+ <![CDATA[select comm, stddev_samp(comm + 1) as sd
+from empnullables group by comm]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], SD=[STDDEV_SAMP($1)])
+ LogicalProject(COMM=[$6], $f1=[+($6, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(COMM=[$0], SD=[CASE(IS NULL($0), null:INTEGER, 0)])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(COMM=[$6], $f1=[+($6, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionNullableGroupKey">
+ <Resource name="sql">
+ <![CDATA[select comm, stddev_pop(comm) as sdp
+from empnullables group by comm]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], SDP=[STDDEV_POP($0)])
+ LogicalProject(COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(COMM=[$0], SDP=[CASE(IS NULL($0), null:INTEGER, 0)])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionStddevPopOfGroupByKey">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_pop(sal) as sdp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SDP=[$2])
+ LogicalAggregate(group=[{0, 1}], SDP=[STDDEV_POP($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], SDP=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], SDP=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionStddevSampOfGroupByKey">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_samp(sal) as sd
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SD=[$2])
+ LogicalAggregate(group=[{0, 1}], SD=[STDDEV_SAMP($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], SD=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], SD=[CAST(0):INTEGER])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionVarPopOfGroupByKey">
+ <Resource name="sql">
+ <![CDATA[select sal, var_pop(sal) as vp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VP=[$2])
+ LogicalAggregate(group=[{0, 1}], VP=[VAR_POP($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], VP=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], VP=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionVarSampOfGroupByKey">
+ <Resource name="sql">
+ <![CDATA[select sal, var_samp(sal) as vs
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VS=[$2])
+ LogicalAggregate(group=[{0, 1}], VS=[VAR_SAMP($0)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], VS=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], VS=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithBinaryExpression">
+ <Resource name="sql">
+ <![CDATA[select sal, var_pop(sal + deptno) as vp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VP=[$2])
+ LogicalAggregate(group=[{0, 1}], VP=[VAR_POP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+($5, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], VP=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], VP=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+($5, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithComplexMixNoOptimization">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_samp(sal * 2 + comm) as sd
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SD=[$2])
+ LogicalAggregate(group=[{0, 1}], SD=[STDDEV_SAMP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+(*($5, 2), $6)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithConstantExpression">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_pop(2 * sal + 100) as sdp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SDP=[$2])
+ LogicalAggregate(group=[{0, 1}], SDP=[STDDEV_POP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+(*(2, $5), 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], SDP=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], SDP=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+(*(2, $5), 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithMixedColumnsNoOptimization">
+ <Resource name="sql">
+ <![CDATA[select sal, stddev_pop(sal + comm) as sdp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], SDP=[$2])
+ LogicalAggregate(group=[{0, 1}], SDP=[STDDEV_POP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[+($5, $6)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithMultipleGroupByKeys">
+ <Resource name="sql">
+ <![CDATA[select sal, var_samp(sal * deptno) as vs
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VS=[$2])
+ LogicalAggregate(group=[{0, 1}], VS=[VAR_SAMP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[*($5, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$0], VS=[$2])
+ LogicalProject(SAL=[$0], DEPTNO=[$1], VS=[0])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[*($5, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithNonGroupByColumnNoOptimization">
+ <Resource name="sql">
+ <![CDATA[select sal, var_pop(comm) as vp
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VP=[$2])
+ LogicalAggregate(group=[{0, 1}], VP=[VAR_POP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testStatisticalFunctionWithPartialExpressionNoOptimization">
+ <Resource name="sql">
+ <![CDATA[select sal, var_samp(sal * empno) as vs
+from emp group by sal, deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$0], VS=[$2])
+ LogicalAggregate(group=[{0, 1}], VS=[VAR_SAMP($2)])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[*($5, $0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg-reduce.iq
b/core/src/test/resources/sql/agg-reduce.iq
new file mode 100644
index 0000000000..f31f333ad7
--- /dev/null
+++ b/core/src/test/resources/sql/agg-reduce.iq
@@ -0,0 +1,174 @@
+# agg-reduce.iq - Tests for aggregate function reduction on GROUP BY keys
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use post
+!set outputformat mysql
+
+# These results were validated by:
+# 1. Quidem tests: end-to-end SQL execution verification (this file)
+# 2. RelOptFixture unit test cases covering query plan transformation
+# 3. PostgreSQL manual verification:
+# - Tests 1-4: Statistical functions return 0 or NULL (NULL when n < 2)
+# - Test 5: Expressions with GROUP BY keys also return 0
+# - Tests 6-8: MAX/MIN/AVG return the GROUP BY key value itself
+# - Test 9: Mix verification - MAX returns key, STDDEV_POP returns 0
+# All results confirmed: optimization is correct, semantics preserved
+
+# Test 1: STDDEV_POP on GROUP BY key should return 0 for constant values
+select deptno, stddev_pop(deptno) as sdp from emp group by deptno order by
deptno;
++--------+-----+
+| DEPTNO | SDP |
++--------+-----+
+| 10 | 0 |
+| 20 | 0 |
+| 30 | 0 |
+| 50 | 0 |
+| 60 | 0 |
+| | |
++--------+-----+
+(6 rows)
+
+!ok
+
+# Test 2: STDDEV_SAMP on GROUP BY key should return 0 or null for constant
values
+select deptno, stddev_samp(deptno) as sds from emp group by deptno order by
deptno;
++--------+-----+
+| DEPTNO | SDS |
++--------+-----+
+| 10 | 0 |
+| 20 | |
+| 30 | 0 |
+| 50 | 0 |
+| 60 | |
+| | |
++--------+-----+
+(6 rows)
+
+!ok
+
+# Test 3: VAR_POP on GROUP BY key should return 0 for constant values
+select deptno, var_pop(deptno) as vp from emp group by deptno order by deptno;
++--------+----+
+| DEPTNO | VP |
++--------+----+
+| 10 | 0 |
+| 20 | 0 |
+| 30 | 0 |
+| 50 | 0 |
+| 60 | 0 |
+| | |
++--------+----+
+(6 rows)
+
+!ok
+
+# Test 4: VAR_SAMP on GROUP BY key should return 0 or null
+select deptno, var_samp(deptno) as vs from emp group by deptno order by deptno;
++--------+----+
+| DEPTNO | VS |
++--------+----+
+| 10 | 0 |
+| 20 | |
+| 30 | 0 |
+| 50 | 0 |
+| 60 | |
+| | |
++--------+----+
+(6 rows)
+
+!ok
+
+# Test 5: Expression with only GROUP BY keys - STDDEV_POP returns 0
+select deptno, stddev_pop(deptno + 1) as sdp from emp group by deptno order by
deptno;
++--------+-----+
+| DEPTNO | SDP |
++--------+-----+
+| 10 | 0 |
+| 20 | 0 |
+| 30 | 0 |
+| 50 | 0 |
+| 60 | 0 |
+| | |
++--------+-----+
+(6 rows)
+
+!ok
+
+# Test 6: MAX on GROUP BY key returns the key value itself
+select deptno, max(deptno) as m from emp group by deptno order by deptno;
++--------+----+
+| DEPTNO | M |
++--------+----+
+| 10 | 10 |
+| 20 | 20 |
+| 30 | 30 |
+| 50 | 50 |
+| 60 | 60 |
+| | |
++--------+----+
+(6 rows)
+
+!ok
+
+# Test 7: MIN on GROUP BY key returns the key value itself
+select deptno, min(deptno) as m from emp group by deptno order by deptno;
++--------+----+
+| DEPTNO | M |
++--------+----+
+| 10 | 10 |
+| 20 | 20 |
+| 30 | 30 |
+| 50 | 50 |
+| 60 | 60 |
+| | |
++--------+----+
+(6 rows)
+
+!ok
+
+# Test 8: AVG on GROUP BY key returns the key value itself
+select deptno, avg(deptno) as a from emp group by deptno order by deptno;
++--------+----+
+| DEPTNO | A |
++--------+----+
+| 10 | 10 |
+| 20 | 20 |
+| 30 | 30 |
+| 50 | 50 |
+| 60 | 60 |
+| | |
++--------+----+
+(6 rows)
+
+!ok
+
+# Test 9: Mix of statistical and regular functions
+select deptno, max(deptno) as m, stddev_pop(deptno) as sdp
+from emp group by deptno order by deptno;
++--------+----+-----+
+| DEPTNO | M | SDP |
++--------+----+-----+
+| 10 | 10 | 0 |
+| 20 | 20 | 0 |
+| 30 | 30 | 0 |
+| 50 | 50 | 0 |
+| 60 | 60 | 0 |
+| | | |
++--------+----+-----+
+(6 rows)
+
+!ok
