This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new b61b5076990 [Enhencement](Nereids) reduce child output rows if agg
child is literal (#32188)
b61b5076990 is described below
commit b61b5076990cab0b8b89aaadf91b4967ff8f02f9
Author: LiBinfeng <[email protected]>
AuthorDate: Wed Apr 3 12:23:41 2024 +0800
[Enhencement](Nereids) reduce child output rows if agg child is literal
(#32188)
with group by:
select max(1) from t1 group by c1; -> select 1 from (select c1 from t1
group by c1);
without group by:
select max(1) from t1; -> select max(1) from (select 1 from t1 limit 1) tmp;
---
.../doris/nereids/jobs/executor/Rewriter.java | 2 +
.../org/apache/doris/nereids/rules/RuleType.java | 1 +
.../rewrite/ReduceAggregateChildOutputRows.java | 89 ++
.../eliminate_aggregate_constant.out | 993 +++++++++++++++++++++
.../eliminate_aggregate_constant.groovy | 285 ++++++
5 files changed, 1370 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index a68c7510965..e8223524367 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -117,6 +117,7 @@ import
org.apache.doris.nereids.rules.rewrite.PushFilterInsideJoin;
import org.apache.doris.nereids.rules.rewrite.PushProjectIntoOneRowRelation;
import org.apache.doris.nereids.rules.rewrite.PushProjectIntoUnion;
import org.apache.doris.nereids.rules.rewrite.PushProjectThroughUnion;
+import org.apache.doris.nereids.rules.rewrite.ReduceAggregateChildOutputRows;
import org.apache.doris.nereids.rules.rewrite.ReorderJoin;
import org.apache.doris.nereids.rules.rewrite.RewriteCteChildren;
import org.apache.doris.nereids.rules.rewrite.SplitLimit;
@@ -203,6 +204,7 @@ public class Rewriter extends AbstractBatchJobExecutor {
new EliminateLimit(),
new EliminateFilter(),
new EliminateAggregate(),
+ new ReduceAggregateChildOutputRows(),
new EliminateJoinCondition(),
new EliminateAssertNumRows(),
new EliminateSemiJoin()
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
index aa38af04ec5..3a43e1ad672 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
@@ -239,6 +239,7 @@ public enum RuleType {
MATERIALIZED_INDEX_PROJECT_SCAN(RuleTypeClass.REWRITE),
MATERIALIZED_INDEX_PROJECT_FILTER_SCAN(RuleTypeClass.REWRITE),
MATERIALIZED_INDEX_FILTER_PROJECT_SCAN(RuleTypeClass.REWRITE),
+ REDUCE_AGGREGATE_CHILD_OUTPUT_ROWS(RuleTypeClass.REWRITE),
OLAP_SCAN_PARTITION_PRUNE(RuleTypeClass.REWRITE),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReduceAggregateChildOutputRows.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReduceAggregateChildOutputRows.java
new file mode 100644
index 00000000000..94109879e27
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ReduceAggregateChildOutputRows.java
@@ -0,0 +1,89 @@
+// 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.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.Max;
+import org.apache.doris.nereids.trees.expressions.functions.agg.Min;
+import org.apache.doris.nereids.trees.plans.LimitPhase;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.Set;
+
+/** ReduceAggregateChildOutputRows
+ * with group by:
+ * select max(1) from t1 group by c1; -> select 1 from (select c1 from t1
group by c1);
+ * without group by:
+ * select max(1) from t1; -> select max(1) from (select 1 from t1 limit 1) tmp;
+ * */
+public class ReduceAggregateChildOutputRows extends OneRewriteRuleFactory {
+ @Override
+ public Rule build() {
+ return logicalAggregate().then(agg -> {
+ Set<AggregateFunction> aggFunctions = agg.getAggregateFunctions();
+ // check whether we have aggregate(constant) in all
aggregateFunctions
+ if (!(agg.child() instanceof LogicalLimit && ((LogicalLimit)
agg.child()).getLimit() == 1)
+ || aggFunctions.isEmpty() ||
!aggFunctions.stream().allMatch(
+ f -> (f instanceof Min || f instanceof Max)
+ && (f.arity() == 1 && f.child(0).isConstant()))) {
+ return null;
+ }
+
+ ImmutableList.Builder<NamedExpression> newOutput =
ImmutableList.builder();
+ for (int i = 0; i < agg.getOutputExpressions().size(); i++) {
+ NamedExpression expr = agg.getOutputExpressions().get(i);
+ if (expr instanceof Alias && expr.child(0) instanceof
AggregateFunction) {
+ AggregateFunction f = (AggregateFunction) expr.child(0);
+ if (f instanceof Min || f instanceof Max) {
+ newOutput.add(new Alias(expr.getExprId(), f.child(0),
expr.getName()));
+ } else {
+ throw new AnalysisException("Unexpected aggregate
function: " + f);
+ }
+ } else {
+ newOutput.add(expr);
+ }
+ }
+
+ if (agg.getGroupByExpressions().isEmpty()) {
+ LogicalAggregate newAgg = new
LogicalAggregate<>(agg.getGroupByExpressions(),
+ agg.getOutputExpressions(), new LogicalLimit(1, 0,
LimitPhase.ORIGIN,
+ new LogicalProject<>(newOutput.build(),
agg.child())));
+ return newAgg;
+ } else {
+ ImmutableList.Builder<NamedExpression> childOutput =
+
ImmutableList.builderWithExpectedSize(agg.getGroupByExpressions().size());
+ for (Expression expr : agg.getGroupByExpressions()) {
+ childOutput.add((NamedExpression) expr);
+ }
+ return new LogicalProject<>(newOutput.build(),
+ new
LogicalAggregate<>(agg.getGroupByExpressions(), childOutput.build(),
agg.child()));
+ }
+ }).toRule(RuleType.REDUCE_AGGREGATE_CHILD_OUTPUT_ROWS);
+ }
+
+}
diff --git
a/regression-test/data/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.out
b/regression-test/data/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.out
new file mode 100644
index 00000000000..c0d8526bc65
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.out
@@ -0,0 +1,993 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !basic_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_2 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_4 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_6 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_8 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_2_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_2 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_4 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_6 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_2_8 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_3_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_2 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_4 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_6 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_3_8 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_4_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_2 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_4 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_6 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_4_8 --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_add_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_add_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !basic_add_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_add_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !basic_add_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_add_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !basic_add_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !basic_add_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !basic_add_2_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_add_2_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !basic_add_2_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_add_2_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !basic_add_2_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_add_2_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !basic_add_2_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !basic_add_2_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !basic_add_3_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_add_3_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !basic_add_3_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_add_3_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !basic_add_3_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_add_3_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !basic_add_3_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !basic_add_3_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !basic_add_4_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_add_4_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !basic_add_4_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_add_4_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !basic_add_4_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_add_4_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !basic_add_4_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !basic_add_4_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !add_sum_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !add_sum_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !add_sum_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !add_sum_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !add_sum_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !add_sum_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !add_sum_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t1]
+
+-- !add_sum_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !add_sum_2_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !add_sum_2_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !add_sum_2_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !add_sum_2_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !add_sum_2_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !add_sum_2_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !add_sum_2_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t2]
+
+-- !add_sum_2_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
+
+-- !add_sum_3_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !add_sum_3_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !add_sum_3_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !add_sum_3_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !add_sum_3_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !add_sum_3_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !add_sum_3_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t3]
+
+-- !add_sum_3_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t3]
+
+-- !add_sum_4_1 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !add_sum_4_2 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !add_sum_4_3 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !add_sum_4_4 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !add_sum_4_5 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !add_sum_4_6 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !add_sum_4_7 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[LOCAL]
+------PhysicalProject
+--------PhysicalOlapScan[t4]
+
+-- !add_sum_4_8 --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t4]
+
+-- !basic_1 --
+1
+
+-- !basic_2 --
+1
+
+-- !basic_3 --
+1
+
+-- !basic_4 --
+1
+
+-- !basic_5 --
+1
+
+-- !basic_6 --
+1
+
+-- !basic_7 --
+1.0
+
+-- !basic_8 --
+1.0
+
+-- !basic_2_1 --
+1
+
+-- !basic_2_2 --
+1
+
+-- !basic_2_3 --
+1
+
+-- !basic_2_4 --
+1
+
+-- !basic_2_5 --
+1
+
+-- !basic_2_6 --
+1
+
+-- !basic_2_7 --
+1.0
+
+-- !basic_2_8 --
+1.0
+
+-- !basic_3_1 --
+
+-- !basic_3_2 --
+\N
+
+-- !basic_3_3 --
+
+-- !basic_3_4 --
+\N
+
+-- !basic_3_5 --
+
+-- !basic_3_6 --
+\N
+
+-- !basic_3_7 --
+
+-- !basic_3_8 --
+\N
+
+-- !basic_4_1 --
+1
+1
+
+-- !basic_4_2 --
+1
+
+-- !basic_4_3 --
+1
+1
+
+-- !basic_4_4 --
+1
+
+-- !basic_4_5 --
+1
+1
+
+-- !basic_4_6 --
+2
+
+-- !basic_4_7 --
+1.0
+1.0
+
+-- !basic_4_8 --
+1.0
+
+-- !basic_add_1 --
+2
+
+-- !basic_add_2 --
+2
+
+-- !basic_add_3 --
+2
+
+-- !basic_add_4 --
+2
+
+-- !basic_add_5 --
+2
+
+-- !basic_add_6 --
+2
+
+-- !basic_add_7 --
+2.0
+
+-- !basic_add_8 --
+2.0
+
+-- !basic_add_2_1 --
+2
+
+-- !basic_add_2_2 --
+2
+
+-- !basic_add_2_3 --
+2
+
+-- !basic_add_2_4 --
+2
+
+-- !basic_add_2_5 --
+2
+
+-- !basic_add_2_6 --
+2
+
+-- !basic_add_2_7 --
+2.0
+
+-- !basic_add_2_8 --
+2.0
+
+-- !basic_add_3_1 --
+
+-- !basic_add_3_2 --
+\N
+
+-- !basic_add_3_3 --
+
+-- !basic_add_3_4 --
+\N
+
+-- !basic_add_3_5 --
+
+-- !basic_add_3_6 --
+\N
+
+-- !basic_add_3_7 --
+
+-- !basic_add_3_8 --
+\N
+
+-- !basic_add_4_1 --
+2
+2
+
+-- !basic_add_4_2 --
+2
+
+-- !basic_add_4_3 --
+2
+2
+
+-- !basic_add_4_4 --
+2
+
+-- !basic_add_4_5 --
+2
+2
+
+-- !basic_add_4_6 --
+3
+
+-- !basic_add_4_7 --
+2.0
+2.0
+
+-- !basic_add_4_8 --
+2.0
+
+-- !add_sum_1 --
+3
+
+-- !add_sum_2 --
+3
+
+-- !add_sum_3 --
+3
+
+-- !add_sum_4 --
+3
+
+-- !add_sum_5 --
+3
+
+-- !add_sum_6 --
+3
+
+-- !add_sum_7 --
+3.0
+
+-- !add_sum_8 --
+3.0
+
+-- !add_sum_2_1 --
+3
+
+-- !add_sum_2_2 --
+3
+
+-- !add_sum_2_3 --
+3
+
+-- !add_sum_2_4 --
+3
+
+-- !add_sum_2_5 --
+3
+
+-- !add_sum_2_6 --
+3
+
+-- !add_sum_2_7 --
+3.0
+
+-- !add_sum_2_8 --
+3.0
+
+-- !add_sum_3_1 --
+
+-- !add_sum_3_2 --
+\N
+
+-- !add_sum_3_3 --
+
+-- !add_sum_3_4 --
+\N
+
+-- !add_sum_3_5 --
+
+-- !add_sum_3_6 --
+\N
+
+-- !add_sum_3_7 --
+
+-- !add_sum_3_8 --
+\N
+
+-- !add_sum_4_1 --
+3
+3
+
+-- !add_sum_4_2 --
+5
+
+-- !add_sum_4_3 --
+3
+3
+
+-- !add_sum_4_4 --
+5
+
+-- !add_sum_4_5 --
+3
+3
+
+-- !add_sum_4_6 --
+6
+
+-- !add_sum_4_7 --
+3.0
+3.0
+
+-- !add_sum_4_8 --
+5.0
+
diff --git
a/regression-test/suites/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.groovy
b/regression-test/suites/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.groovy
new file mode 100644
index 00000000000..e22b4087000
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/eliminate_aggregate_constant/eliminate_aggregate_constant.groovy
@@ -0,0 +1,285 @@
+// 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.
+
+suite("eliminate_aggregate_constant") {
+ sql "SET enable_nereids_planner=true"
+ sql "set runtime_filter_mode=OFF"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "SET ignore_shape_nodes='PhysicalDistribute'"
+ sql 'DROP DATABASE IF EXISTS test_aggregate_constant'
+ sql 'CREATE DATABASE IF NOT EXISTS test_aggregate_constant'
+ sql 'use test_aggregate_constant'
+
+ // create tables
+ sql """drop table if exists t1;"""
+ sql """drop table if exists t2;"""
+ sql """drop table if exists t3;"""
+ sql """drop table if exists t4;"""
+
+ sql """create table t1 (c1 int, c11 int) distributed by hash(c1) buckets 3
properties('replication_num' = '1');"""
+ sql """create table t2 (c2 int, c22 int) distributed by hash(c2) buckets 3
properties('replication_num' = '1');"""
+ sql """create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3
properties('replication_num' = '1');"""
+ sql """create table t4 (c4 int, c44 int) distributed by hash(c4) buckets 3
properties('replication_num' = '1');"""
+
+ sql "insert into t1 values (101, 101)"
+ sql "insert into t2 values (null, null)"
+ sql "insert into t4 values (102, 102)"
+ sql "insert into t4 values (103, 103)"
+
+ /* ******** with one row ******** */
+ qt_basic_1 """explain shape plan select max(1) from t1 group by c1;"""
+ qt_basic_2 """explain shape plan select max(1) from t1;"""
+ qt_basic_3 """explain shape plan select min(1) from t1 group by c1;"""
+ qt_basic_4 """explain shape plan select min(1) from t1;"""
+ qt_basic_5 """explain shape plan select sum(1) from t1 group by c1;"""
+ qt_basic_6 """explain shape plan select sum(1) from t1;"""
+ qt_basic_7 """explain shape plan select avg(1) from t1 group by c1;"""
+ qt_basic_8 """explain shape plan select avg(1) from t1;"""
+
+ /* ******** with one row "null" ******** */
+ qt_basic_2_1 """explain shape plan select max(1) from t2 group by c2;"""
+ qt_basic_2_2 """explain shape plan select max(1) from t2;"""
+ qt_basic_2_3 """explain shape plan select min(1) from t2 group by c2;"""
+ qt_basic_2_4 """explain shape plan select min(1) from t2;"""
+ qt_basic_2_5 """explain shape plan select sum(1) from t2 group by c2;"""
+ qt_basic_2_6 """explain shape plan select sum(1) from t2;"""
+ qt_basic_2_7 """explain shape plan select avg(1) from t2 group by c2;"""
+ qt_basic_2_8 """explain shape plan select avg(1) from t2;"""
+
+ /* ******** with empty table ******** */
+ qt_basic_3_1 """explain shape plan select max(1) from t3 group by c3;"""
+ qt_basic_3_2 """explain shape plan select max(1) from t3;"""
+ qt_basic_3_3 """explain shape plan select min(1) from t3 group by c3;"""
+ qt_basic_3_4 """explain shape plan select min(1) from t3;"""
+ qt_basic_3_5 """explain shape plan select sum(1) from t3 group by c3;"""
+ qt_basic_3_6 """explain shape plan select sum(1) from t3;"""
+ qt_basic_3_7 """explain shape plan select avg(1) from t3 group by c3;"""
+ qt_basic_3_8 """explain shape plan select avg(1) from t3;"""
+
+ /* ******** with different group table ******** */
+ qt_basic_4_1 """explain shape plan select max(1) from t4 group by c4;"""
+ qt_basic_4_2 """explain shape plan select max(1) from t4;"""
+ qt_basic_4_3 """explain shape plan select min(1) from t4 group by c4;"""
+ qt_basic_4_4 """explain shape plan select min(1) from t4;"""
+ qt_basic_4_5 """explain shape plan select sum(1) from t4 group by c4;"""
+ qt_basic_4_6 """explain shape plan select sum(1) from t4;"""
+ qt_basic_4_7 """explain shape plan select avg(1) from t4 group by c4;"""
+ qt_basic_4_8 """explain shape plan select avg(1) from t4;"""
+
+ /* ******** with one row ******** */
+ qt_basic_add_1 """explain shape plan select max(1) + 1 from t1 group by
c1;"""
+ qt_basic_add_2 """explain shape plan select max(1) + 1 from t1;"""
+ qt_basic_add_3 """explain shape plan select min(1) + 1 from t1 group by
c1;"""
+ qt_basic_add_4 """explain shape plan select min(1) + 1 from t1;"""
+ qt_basic_add_5 """explain shape plan select sum(1) + 1 from t1 group by
c1;"""
+ qt_basic_add_6 """explain shape plan select sum(1) + 1 from t1;"""
+ qt_basic_add_7 """explain shape plan select avg(1) + 1 from t1 group by
c1;"""
+ qt_basic_add_8 """explain shape plan select avg(1) + 1 from t1;"""
+
+ /* ******** with one row "null" ******** */
+ qt_basic_add_2_1 """explain shape plan select max(1) + 1 from t2 group by
c2;"""
+ qt_basic_add_2_2 """explain shape plan select max(1) + 1 from t2;"""
+ qt_basic_add_2_3 """explain shape plan select min(1) + 1 from t2 group by
c2;"""
+ qt_basic_add_2_4 """explain shape plan select min(1) + 1 from t2;"""
+ qt_basic_add_2_5 """explain shape plan select sum(1) + 1 from t2 group by
c2;"""
+ qt_basic_add_2_6 """explain shape plan select sum(1) + 1 from t2;"""
+ qt_basic_add_2_7 """explain shape plan select avg(1) + 1 from t2 group by
c2;"""
+ qt_basic_add_2_8 """explain shape plan select avg(1) + 1 from t2;"""
+
+ /* ******** with empty table ******** */
+ qt_basic_add_3_1 """explain shape plan select max(1) + 1 from t3 group by
c3;"""
+ qt_basic_add_3_2 """explain shape plan select max(1) + 1 from t3;"""
+ qt_basic_add_3_3 """explain shape plan select min(1) + 1 from t3 group by
c3;"""
+ qt_basic_add_3_4 """explain shape plan select min(1) + 1 from t3;"""
+ qt_basic_add_3_5 """explain shape plan select sum(1) + 1 from t3 group by
c3;"""
+ qt_basic_add_3_6 """explain shape plan select sum(1) + 1 from t3;"""
+ qt_basic_add_3_7 """explain shape plan select avg(1) + 1 from t3 group by
c3;"""
+ qt_basic_add_3_8 """explain shape plan select avg(1) + 1 from t3;"""
+
+ /* ******** with different group table ******** */
+ qt_basic_add_4_1 """explain shape plan select max(1) + 1 from t4 group by
c4;"""
+ qt_basic_add_4_2 """explain shape plan select max(1) + 1 from t4;"""
+ qt_basic_add_4_3 """explain shape plan select min(1) + 1 from t4 group by
c4;"""
+ qt_basic_add_4_4 """explain shape plan select min(1) + 1 from t4;"""
+ qt_basic_add_4_5 """explain shape plan select sum(1) + 1 from t4 group by
c4;"""
+ qt_basic_add_4_6 """explain shape plan select sum(1) + 1 from t4;"""
+ qt_basic_add_4_7 """explain shape plan select avg(1) + 1 from t4 group by
c4;"""
+ qt_basic_add_4_8 """explain shape plan select avg(1) + 1 from t4;"""
+
+ /* ******** with one row ******** */
+ qt_add_sum_1 """explain shape plan select max(1) + sum(2) from t1 group by
c1;"""
+ qt_add_sum_2 """explain shape plan select max(1) + sum(2) from t1;"""
+ qt_add_sum_3 """explain shape plan select min(1) + sum(2) from t1 group by
c1;"""
+ qt_add_sum_4 """explain shape plan select min(1) + sum(2) from t1;"""
+ qt_add_sum_5 """explain shape plan select sum(1) + sum(2) from t1 group by
c1;"""
+ qt_add_sum_6 """explain shape plan select sum(1) + sum(2) from t1;"""
+ qt_add_sum_7 """explain shape plan select avg(1) + sum(2) from t1 group by
c1;"""
+ qt_add_sum_8 """explain shape plan select avg(1) + sum(2) from t1;"""
+
+ /* ******** with one row "null" ******** */
+ qt_add_sum_2_1 """explain shape plan select max(1) + sum(2) from t2 group
by c2;"""
+ qt_add_sum_2_2 """explain shape plan select max(1) + sum(2) from t2;"""
+ qt_add_sum_2_3 """explain shape plan select min(1) + sum(2) from t2 group
by c2;"""
+ qt_add_sum_2_4 """explain shape plan select min(1) + sum(2) from t2;"""
+ qt_add_sum_2_5 """explain shape plan select sum(1) + sum(2) from t2 group
by c2;"""
+ qt_add_sum_2_6 """explain shape plan select sum(1) + sum(2) from t2;"""
+ qt_add_sum_2_7 """explain shape plan select avg(1) + sum(2) from t2 group
by c2;"""
+ qt_add_sum_2_8 """explain shape plan select avg(1) + sum(2) from t2;"""
+
+ /* ******** with empty table ******** */
+ qt_add_sum_3_1 """explain shape plan select max(1) + sum(2) from t3 group
by c3;"""
+ qt_add_sum_3_2 """explain shape plan select max(1) + sum(2) from t3;"""
+ qt_add_sum_3_3 """explain shape plan select min(1) + sum(2) from t3 group
by c3;"""
+ qt_add_sum_3_4 """explain shape plan select min(1) + sum(2) from t3;"""
+ qt_add_sum_3_5 """explain shape plan select sum(1) + sum(2) from t3 group
by c3;"""
+ qt_add_sum_3_6 """explain shape plan select sum(1) + sum(2) from t3;"""
+ qt_add_sum_3_7 """explain shape plan select avg(1) + sum(2) from t3 group
by c3;"""
+ qt_add_sum_3_8 """explain shape plan select avg(1) + sum(2) from t3;"""
+
+ /* ******** with different group table ******** */
+ qt_add_sum_4_1 """explain shape plan select max(1) + sum(2) from t4 group
by c4;"""
+ qt_add_sum_4_2 """explain shape plan select max(1) + sum(2) from t4;"""
+ qt_add_sum_4_3 """explain shape plan select min(1) + sum(2) from t4 group
by c4;"""
+ qt_add_sum_4_4 """explain shape plan select min(1) + sum(2) from t4;"""
+ qt_add_sum_4_5 """explain shape plan select sum(1) + sum(2) from t4 group
by c4;"""
+ qt_add_sum_4_6 """explain shape plan select sum(1) + sum(2) from t4;"""
+ qt_add_sum_4_7 """explain shape plan select avg(1) + sum(2) from t4 group
by c4;"""
+ qt_add_sum_4_8 """explain shape plan select avg(1) + sum(2) from t4;"""
+
+
+ /* ******** Output ******** */
+
+ /* ******** with one row ******** */
+ order_qt_basic_1 """select max(1) from t1 group by c1;"""
+ order_qt_basic_2 """select max(1) from t1;"""
+ order_qt_basic_3 """select min(1) from t1 group by c1;"""
+ order_qt_basic_4 """select min(1) from t1;"""
+ order_qt_basic_5 """select sum(1) from t1 group by c1;"""
+ order_qt_basic_6 """select sum(1) from t1;"""
+ order_qt_basic_7 """select avg(1) from t1 group by c1;"""
+ order_qt_basic_8 """select avg(1) from t1;"""
+
+ /* ******** with one row "null" ******** */
+ order_qt_basic_2_1 """select max(1) from t2 group by c2;"""
+ order_qt_basic_2_2 """select max(1) from t2;"""
+ order_qt_basic_2_3 """select min(1) from t2 group by c2;"""
+ order_qt_basic_2_4 """select min(1) from t2;"""
+ order_qt_basic_2_5 """select sum(1) from t2 group by c2;"""
+ order_qt_basic_2_6 """select sum(1) from t2;"""
+ order_qt_basic_2_7 """select avg(1) from t2 group by c2;"""
+ order_qt_basic_2_8 """select avg(1) from t2;"""
+
+ /* ******** with empty table ******** */
+ order_qt_basic_3_1 """select max(1) from t3 group by c3;"""
+ order_qt_basic_3_2 """select max(1) from t3;"""
+ order_qt_basic_3_3 """select min(1) from t3 group by c3;"""
+ order_qt_basic_3_4 """select min(1) from t3;"""
+ order_qt_basic_3_5 """select sum(1) from t3 group by c3;"""
+ order_qt_basic_3_6 """select sum(1) from t3;"""
+ order_qt_basic_3_7 """select avg(1) from t3 group by c3;"""
+ order_qt_basic_3_8 """select avg(1) from t3;"""
+
+ /* ******** with different group table ******** */
+ order_qt_basic_4_1 """select max(1) from t4 group by c4;"""
+ order_qt_basic_4_2 """select max(1) from t4;"""
+ order_qt_basic_4_3 """select min(1) from t4 group by c4;"""
+ order_qt_basic_4_4 """select min(1) from t4;"""
+ order_qt_basic_4_5 """select sum(1) from t4 group by c4;"""
+ order_qt_basic_4_6 """select sum(1) from t4;"""
+ order_qt_basic_4_7 """select avg(1) from t4 group by c4;"""
+ order_qt_basic_4_8 """select avg(1) from t4;"""
+
+ /* ******** with one row ******** */
+ order_qt_basic_add_1 """select max(1) + 1 from t1 group by c1;"""
+ order_qt_basic_add_2 """select max(1) + 1 from t1;"""
+ order_qt_basic_add_3 """select min(1) + 1 from t1 group by c1;"""
+ order_qt_basic_add_4 """select min(1) + 1 from t1;"""
+ order_qt_basic_add_5 """select sum(1) + 1 from t1 group by c1;"""
+ order_qt_basic_add_6 """select sum(1) + 1 from t1;"""
+ order_qt_basic_add_7 """select avg(1) + 1 from t1 group by c1;"""
+ order_qt_basic_add_8 """select avg(1) + 1 from t1;"""
+
+ /* ******** with one row "null" ******** */
+ order_qt_basic_add_2_1 """select max(1) + 1 from t2 group by c2;"""
+ order_qt_basic_add_2_2 """select max(1) + 1 from t2;"""
+ order_qt_basic_add_2_3 """select min(1) + 1 from t2 group by c2;"""
+ order_qt_basic_add_2_4 """select min(1) + 1 from t2;"""
+ order_qt_basic_add_2_5 """select sum(1) + 1 from t2 group by c2;"""
+ order_qt_basic_add_2_6 """select sum(1) + 1 from t2;"""
+ order_qt_basic_add_2_7 """select avg(1) + 1 from t2 group by c2;"""
+ order_qt_basic_add_2_8 """select avg(1) + 1 from t2;"""
+
+ /* ******** with empty table ******** */
+ order_qt_basic_add_3_1 """select max(1) + 1 from t3 group by c3;"""
+ order_qt_basic_add_3_2 """select max(1) + 1 from t3;"""
+ order_qt_basic_add_3_3 """select min(1) + 1 from t3 group by c3;"""
+ order_qt_basic_add_3_4 """select min(1) + 1 from t3;"""
+ order_qt_basic_add_3_5 """select sum(1) + 1 from t3 group by c3;"""
+ order_qt_basic_add_3_6 """select sum(1) + 1 from t3;"""
+ order_qt_basic_add_3_7 """select avg(1) + 1 from t3 group by c3;"""
+ order_qt_basic_add_3_8 """select avg(1) + 1 from t3;"""
+
+ /* ******** with different group table ******** */
+ order_qt_basic_add_4_1 """select max(1) + 1 from t4 group by c4;"""
+ order_qt_basic_add_4_2 """select max(1) + 1 from t4;"""
+ order_qt_basic_add_4_3 """select min(1) + 1 from t4 group by c4;"""
+ order_qt_basic_add_4_4 """select min(1) + 1 from t4;"""
+ order_qt_basic_add_4_5 """select sum(1) + 1 from t4 group by c4;"""
+ order_qt_basic_add_4_6 """select sum(1) + 1 from t4;"""
+ order_qt_basic_add_4_7 """select avg(1) + 1 from t4 group by c4;"""
+ order_qt_basic_add_4_8 """select avg(1) + 1 from t4;"""
+
+ /* ******** with one row ******** */
+ order_qt_add_sum_1 """select max(1) + sum(2) from t1 group by c1;"""
+ order_qt_add_sum_2 """select max(1) + sum(2) from t1;"""
+ order_qt_add_sum_3 """select min(1) + sum(2) from t1 group by c1;"""
+ order_qt_add_sum_4 """select min(1) + sum(2) from t1;"""
+ order_qt_add_sum_5 """select sum(1) + sum(2) from t1 group by c1;"""
+ order_qt_add_sum_6 """select sum(1) + sum(2) from t1;"""
+ order_qt_add_sum_7 """select avg(1) + sum(2) from t1 group by c1;"""
+ order_qt_add_sum_8 """select avg(1) + sum(2) from t1;"""
+
+ /* ******** with one row "null" ******** */
+ order_qt_add_sum_2_1 """select max(1) + sum(2) from t2 group by c2;"""
+ order_qt_add_sum_2_2 """select max(1) + sum(2) from t2;"""
+ order_qt_add_sum_2_3 """select min(1) + sum(2) from t2 group by c2;"""
+ order_qt_add_sum_2_4 """select min(1) + sum(2) from t2;"""
+ order_qt_add_sum_2_5 """select sum(1) + sum(2) from t2 group by c2;"""
+ order_qt_add_sum_2_6 """select sum(1) + sum(2) from t2;"""
+ order_qt_add_sum_2_7 """select avg(1) + sum(2) from t2 group by c2;"""
+ order_qt_add_sum_2_8 """select avg(1) + sum(2) from t2;"""
+
+ /* ******** with empty table ******** */
+ order_qt_add_sum_3_1 """select max(1) + sum(2) from t3 group by c3;"""
+ order_qt_add_sum_3_2 """select max(1) + sum(2) from t3;"""
+ order_qt_add_sum_3_3 """select min(1) + sum(2) from t3 group by c3;"""
+ order_qt_add_sum_3_4 """select min(1) + sum(2) from t3;"""
+ order_qt_add_sum_3_5 """select sum(1) + sum(2) from t3 group by c3;"""
+ order_qt_add_sum_3_6 """select sum(1) + sum(2) from t3;"""
+ order_qt_add_sum_3_7 """select avg(1) + sum(2) from t3 group by c3;"""
+ order_qt_add_sum_3_8 """select avg(1) + sum(2) from t3;"""
+
+ /* ******** with different group table ******** */
+ order_qt_add_sum_4_1 """select max(1) + sum(2) from t4 group by c4;"""
+ order_qt_add_sum_4_2 """select max(1) + sum(2) from t4;"""
+ order_qt_add_sum_4_3 """select min(1) + sum(2) from t4 group by c4;"""
+ order_qt_add_sum_4_4 """select min(1) + sum(2) from t4;"""
+ order_qt_add_sum_4_5 """select sum(1) + sum(2) from t4 group by c4;"""
+ order_qt_add_sum_4_6 """select sum(1) + sum(2) from t4;"""
+ order_qt_add_sum_4_7 """select avg(1) + sum(2) from t4 group by c4;"""
+ order_qt_add_sum_4_8 """select avg(1) + sum(2) from t4;"""
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
