This is an automated email from the ASF dual-hosted git repository.
hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 463ab84 [CALCITE-1172] Add rule to flatten two Aggregate operators
into one
463ab84 is described below
commit 463ab84d2e889a12011a2395ee64dba5cf3e1028
Author: Haisheng Yuan <[email protected]>
AuthorDate: Mon Mar 4 15:40:35 2019 -0600
[CALCITE-1172] Add rule to flatten two Aggregate operators into one
---
.../calcite/plan/volcano/VolcanoPlanner.java | 2 +
.../calcite/rel/rules/AggregateMergeRule.java | 152 ++++++++++++++++++
.../calcite/sql/SqlSplittableAggFunction.java | 48 ++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 171 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 171 +++++++++++++++++++++
5 files changed, 544 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java
b/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java
index 6500110..eb4561a 100644
--- a/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java
+++ b/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java
@@ -49,6 +49,7 @@ import
org.apache.calcite.rel.metadata.JaninoRelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.rules.AggregateJoinTransposeRule;
+import org.apache.calcite.rel.rules.AggregateMergeRule;
import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
import org.apache.calcite.rel.rules.AggregateRemoveRule;
import org.apache.calcite.rel.rules.CalcRemoveRule;
@@ -909,6 +910,7 @@ public class VolcanoPlanner extends AbstractRelOptPlanner {
addRule(JoinAssociateRule.INSTANCE);
}
addRule(AggregateRemoveRule.INSTANCE);
+ addRule(AggregateMergeRule.INSTANCE);
addRule(UnionToDistinctRule.INSTANCE);
addRule(ProjectRemoveRule.INSTANCE);
addRule(AggregateJoinTransposeRule.INSTANCE);
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateMergeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateMergeRule.java
new file mode 100644
index 0000000..8daca31
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateMergeRule.java
@@ -0,0 +1,152 @@
+/*
+ * 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.rel.rules;
+
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptRuleOperand;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.Aggregate.Group;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.sql.SqlSplittableAggFunction;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.calcite.util.ImmutableBitSet;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
+
+/**
+ * Planner rule that matches an {@link Aggregate} on a {@link Aggregate}
+ * and the top aggregate's group key is a subset of the lower aggregate's
+ * group key, and the aggregates are expansions of rollups, then it would
+ * convert into a single aggregate.
+ *
+ * <p>For example, SUM of SUM becomes SUM; SUM of COUNT becomes COUNT;
+ * MAX of MAX becomes MAX; MIN of MIN becomes MIN. AVG of AVG would not
+ * match, nor would COUNT of COUNT.
+ */
+public class AggregateMergeRule extends RelOptRule {
+ public static final AggregateMergeRule INSTANCE =
+ new AggregateMergeRule();
+
+ private AggregateMergeRule() {
+ this(
+ operand(Aggregate.class,
+ operandJ(Aggregate.class, null,
+ agg -> Aggregate.isSimple(agg), any())),
+ RelFactories.LOGICAL_BUILDER);
+ }
+
+ /** Creates an AggregateMergeRule. */
+ public AggregateMergeRule(RelOptRuleOperand operand,
+ RelBuilderFactory relBuilderFactory) {
+ super(operand, relBuilderFactory, null);
+ }
+
+ private boolean isAggregateSupported(AggregateCall aggCall) {
+ if (aggCall.isDistinct()
+ || aggCall.hasFilter()
+ || aggCall.isApproximate()
+ || aggCall.getArgList().size() > 1) {
+ return false;
+ }
+ SqlSplittableAggFunction splitter = aggCall.getAggregation()
+ .unwrap(SqlSplittableAggFunction.class);
+ return splitter != null;
+ }
+
+ public void onMatch(RelOptRuleCall call) {
+ final Aggregate topAgg = call.rel(0);
+ final Aggregate bottomAgg = call.rel(1);
+ if (topAgg.getGroupCount() > bottomAgg.getGroupCount()) {
+ return;
+ }
+
+ final ImmutableBitSet bottomGroupSet = bottomAgg.getGroupSet();
+ final Map<Integer, Integer> map = new HashMap<>();
+ bottomGroupSet.forEach(v -> map.put(map.size(), v));
+ for (int k : topAgg.getGroupSet()) {
+ if (!map.containsKey(k)) {
+ return;
+ }
+ }
+
+ // top aggregate keys must be subset of lower aggregate keys
+ final ImmutableBitSet topGroupSet = topAgg.getGroupSet().permute(map);
+ if (!bottomGroupSet.contains(topGroupSet)) {
+ return;
+ }
+
+ boolean hasEmptyGroup = topAgg.getGroupSets()
+ .stream().anyMatch(n -> n.isEmpty());
+
+ final List<AggregateCall> finalCalls = new ArrayList<>();
+ for (AggregateCall topCall : topAgg.getAggCallList()) {
+ if (!isAggregateSupported(topCall)
+ || topCall.getArgList().size() == 0) {
+ return;
+ }
+ // Make sure top aggregate argument refers to one of the aggregate
+ int bottomIndex = topCall.getArgList().get(0) -
bottomGroupSet.cardinality();
+ if (bottomIndex >= bottomAgg.getAggCallList().size()
+ || bottomIndex < 0) {
+ return;
+ }
+ AggregateCall bottomCall = bottomAgg.getAggCallList().get(bottomIndex);
+ // Should not merge if top agg with empty group keys and the lower agg
+ // function is COUNT, because in case of empty input for lower agg,
+ // the result is empty, if we merge them, we end up with 1 result with
+ // 0, which is wrong.
+ if (!isAggregateSupported(bottomCall)
+ || (bottomCall.getAggregation() == SqlStdOperatorTable.COUNT
+ && hasEmptyGroup)) {
+ return;
+ }
+ SqlSplittableAggFunction splitter = Objects.requireNonNull(
+ bottomCall.getAggregation().unwrap(SqlSplittableAggFunction.class));
+ AggregateCall finalCall = splitter.merge(topCall, bottomCall);
+ // fail to merge the aggregate call, bail out
+ if (finalCall == null) {
+ return;
+ }
+ finalCalls.add(finalCall);
+ }
+
+ // re-map grouping sets
+ ImmutableList<ImmutableBitSet> newGroupingSets = null;
+ if (topAgg.getGroupType() != Group.SIMPLE) {
+ newGroupingSets =
+ ImmutableBitSet.ORDERING.immutableSortedCopy(
+ ImmutableBitSet.permute(topAgg.getGroupSets(), map));
+ }
+
+ final Aggregate finalAgg =
+ topAgg.copy(topAgg.getTraitSet(), bottomAgg.getInput(),
+ topAgg.indicator, topGroupSet,
+ newGroupingSets, finalCalls);
+ call.transformTo(finalAgg);
+ }
+}
+
+// End AggregateMergeRule.java
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
index da843f3..213c646 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
@@ -95,6 +95,20 @@ public interface SqlSplittableAggFunction {
RexNode singleton(RexBuilder rexBuilder, RelDataType inputRowType,
AggregateCall aggregateCall);
+ /**
+ * Merge top and bottom aggregate calls into a single aggregate call,
+ * if they are legit to merge.
+ *
+ * <p>SUM of SUM becomes SUM; SUM of COUNT becomes COUNT;
+ * MAX of MAX becomes MAX; MIN of MIN becomes MIN.
+ * AVG of AVG would not match, nor would COUNT of COUNT.
+ *
+ * @param top top aggregate call
+ * @param bottom bottom aggregate call
+ * @return Merged aggregate call, null if fails to merge aggregate calls
+ */
+ AggregateCall merge(AggregateCall top, AggregateCall bottom);
+
/** Collection in which one can register an element. Registering may return
* a reference to an existing element.
*
@@ -180,6 +194,17 @@ public interface SqlSplittableAggFunction {
rexBuilder.makeExactLiteral(BigDecimal.ZERO));
}
}
+
+ public AggregateCall merge(AggregateCall top, AggregateCall bottom) {
+ if (bottom.getAggregation().getKind() == SqlKind.COUNT
+ && top.getAggregation().getKind() == SqlKind.SUM) {
+ return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
+ bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
+ bottom.getCollation(), bottom.getType(), top.getName());
+ } else {
+ return null;
+ }
+ }
}
/** Aggregate function that splits into two applications of itself.
@@ -213,6 +238,16 @@ public interface SqlSplittableAggFunction {
return aggregateCall.copy(ImmutableIntList.of(arg), -1,
RelCollations.EMPTY);
}
+
+ public AggregateCall merge(AggregateCall top, AggregateCall bottom) {
+ if (top.getAggregation().getKind() == bottom.getAggregation().getKind())
{
+ return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
+ bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
+ bottom.getCollation(), bottom.getType(), top.getName());
+ } else {
+ return null;
+ }
+ }
}
/** Common splitting strategy for {@code SUM} and {@code SUM0} functions. */
@@ -269,6 +304,19 @@ public interface SqlSplittableAggFunction {
aggregateCall.type, aggregateCall.name);
}
+ public AggregateCall merge(AggregateCall top, AggregateCall bottom) {
+ SqlKind topKind = top.getAggregation().getKind();
+ if (topKind == bottom.getAggregation().getKind()
+ && (topKind == SqlKind.SUM
+ || topKind == SqlKind.SUM0)) {
+ return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
+ bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
+ bottom.getCollation(), bottom.getType(), top.getName());
+ } else {
+ return null;
+ }
+ }
+
protected abstract SqlAggFunction getMergeAggFunctionOfTopSplit();
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 600f4b7..341b891 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -57,6 +57,7 @@ import
org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule;
import org.apache.calcite.rel.rules.AggregateExtractProjectRule;
import org.apache.calcite.rel.rules.AggregateFilterTransposeRule;
import org.apache.calcite.rel.rules.AggregateJoinTransposeRule;
+import org.apache.calcite.rel.rules.AggregateMergeRule;
import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
import org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule;
import org.apache.calcite.rel.rules.AggregateReduceFunctionsRule;
@@ -3644,6 +3645,176 @@ public class RelOptRulesTest extends RelOptTestBase {
checkPlanUnchanged(new HepPlanner(program), sql);
}
+ /**
+ * Test case for AggregateMergeRule, should merge 2 aggregates
+ * into a single aggregate.
+ */
+ @Test public void testAggregateMerge1() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select deptno c, min(y), max(z) z,\n"
+ + "sum(r), sum(m) n, sum(x) sal from (\n"
+ + " select deptno, ename, sum(sal) x, max(sal) z,\n"
+ + " min(sal) y, count(hiredate) m, count(mgr) r\n"
+ + " from sales.emp group by deptno, ename) t\n"
+ + "group by deptno";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should merge 2 aggregates
+ * into a single aggregate, top aggregate is not simple aggregate.
+ */
+ @Test public void testAggregateMerge2() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select deptno, empno, sum(x), sum(y)\n"
+ + "from (\n"
+ + " select ename, empno, deptno, sum(sal) x, count(mgr) y\n"
+ + " from sales.emp\n"
+ + " group by deptno, ename, empno) t\n"
+ + "group by grouping sets(deptno, empno)";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should not merge 2 aggregates
+ * into a single aggregate, since lower aggregate is not simple aggregate.
+ */
+ @Test public void testAggregateMerge3() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select deptno, sum(x) from (\n"
+ + " select ename, deptno, sum(sal) x from\n"
+ + " sales.emp group by cube(deptno, ename)) t\n"
+ + "group by deptno";
+ sql(sql).withPre(preProgram).with(program)
+ .checkUnchanged();
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should not merge 2 aggregates
+ * into a single aggregate, since it contains distinct aggregate
+ * function.
+ */
+ @Test public void testAggregateMerge4() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select deptno, sum(x) from (\n"
+ + " select ename, deptno, count(distinct sal) x\n"
+ + " from sales.emp group by deptno, ename) t\n"
+ + "group by deptno";
+ sql(sql).withPre(preProgram).with(program)
+ .checkUnchanged();
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should not merge 2 aggregates
+ * into a single aggregate, since AVG doesn't support splitting.
+ */
+ @Test public void testAggregateMerge5() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select deptno, avg(x) from (\n"
+ + " select mgr, deptno, avg(sal) x from\n"
+ + " sales.emp group by deptno, mgr) t\n"
+ + "group by deptno";
+ sql(sql).withPre(preProgram).with(program)
+ .checkUnchanged();
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should not merge 2 aggregates
+ * into a single aggregate, since top agg has no group key, and
+ * lower agg function is COUNT.
+ */
+ @Test public void testAggregateMerge6() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select sum(x) from (\n"
+ + "select mgr, deptno, count(sal) x from\n"
+ + "sales.emp group by deptno, mgr) t";
+ sql(sql).withPre(preProgram).with(program)
+ .checkUnchanged();
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should not merge 2 aggregates
+ * into a single aggregate, since top agg contains empty grouping set,
+ * and lower agg function is COUNT.
+ */
+ @Test public void testAggregateMerge7() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select mgr, deptno, sum(x) from (\n"
+ + " select mgr, deptno, count(sal) x from\n"
+ + " sales.emp group by deptno, mgr) t\n"
+ + "group by cube(mgr, deptno)";
+ sql(sql).withPre(preProgram).with(program)
+ .checkUnchanged();
+ }
+
+ /**
+ * Test case for AggregateMergeRule, should merge 2 aggregates
+ * into a single aggregate, since both top and bottom aggregates
+ * contains empty grouping set and they are mergable.
+ */
+ @Test public void testAggregateMerge8() {
+ final HepProgram preProgram = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(ProjectMergeRule.INSTANCE)
+ .build();
+ final HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(AggregateProjectMergeRule.INSTANCE)
+ .addRuleInstance(AggregateMergeRule.INSTANCE)
+ .build();
+ final String sql = "select sum(x) x, min(y) z from (\n"
+ + " select sum(sal) x, min(sal) y from sales.emp)";
+ checkPlanning(tester, preProgram, new HepPlanner(program), sql);
+ }
+
@Test public void testSwapOuterJoin() {
final HepProgram program = new HepProgramBuilder()
.addMatchLimit(1)
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index ffa5377..39835de 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6916,6 +6916,177 @@ LogicalProject(NAME=[$3], SUM_SAL=[$1], C=[$2])
]]>
</Resource>
</TestCase>
+ <TestCase name="testAggregateMerge1">
+ <Resource name="sql">
+ <![CDATA[select deptno c, min(y), max(x), max(z) z from (
+ select deptno, ename, max(sal) x, max(sal) z, min(sal) y from
+ sales.emp group by deptno, ename) t group by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[MIN($4)], Z=[MAX($3)], EXPR$3=[SUM($6)],
N=[SUM($5)], SAL=[SUM($2)])
+ LogicalAggregate(group=[{1, 7}], X=[SUM($5)], Z=[MAX($5)], Y=[MIN($5)],
M=[COUNT()], R=[COUNT($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{7}], EXPR$1=[MIN($5)], Z=[MAX($5)],
EXPR$3=[COUNT($3)], N=[COUNT()], SAL=[SUM($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge2">
+ <Resource name="sql">
+ <![CDATA[select deptno, empno, sum(x), sum(y) from (
+ select ename, empno, deptno, sum(sal) x, count(mgr) y from
+ sales.emp group by deptno, ename, empno) t
+ group by grouping sets(deptno, empno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$1], EMPNO=[$0], EXPR$2=[$2], EXPR$3=[$3])
+ LogicalAggregate(group=[{0, 2}], groups=[[{0}, {2}]], EXPR$2=[SUM($3)],
EXPR$3=[SUM($4)])
+ LogicalAggregate(group=[{0, 1, 7}], X=[SUM($5)], Y=[COUNT($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$1], EMPNO=[$0], EXPR$2=[$2], EXPR$3=[$3])
+ LogicalAggregate(group=[{0, 7}], groups=[[{0}, {7}]], EXPR$2=[SUM($5)],
EXPR$3=[COUNT($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge3">
+ <Resource name="sql">
+ <![CDATA[select deptno, sum(x) from (
+ select ename, deptno, sum(sal) x from
+ sales.emp group by cube(deptno, ename)) t
+ group by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[SUM($2)])
+ LogicalAggregate(group=[{1, 7}], groups=[[{1, 7}, {1}, {7}, {}]],
X=[SUM($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[SUM($2)])
+ LogicalAggregate(group=[{1, 7}], groups=[[{1, 7}, {1}, {7}, {}]],
X=[SUM($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge4">
+ <Resource name="sql">
+ <![CDATA[select deptno, sum(x) from (
+ select ename, deptno, count(distinct sal) x from
+ sales.emp group by deptno, ename) t
+ group by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[SUM($2)])
+ LogicalAggregate(group=[{1, 7}], X=[COUNT(DISTINCT $5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[SUM($2)])
+ LogicalAggregate(group=[{1, 7}], X=[COUNT(DISTINCT $5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge5">
+ <Resource name="sql">
+ <![CDATA[select deptno, avg(x) from (
+ select mgr, deptno, avg(sal) x from
+ sales.emp group by deptno, mgr) t
+ group by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[AVG($2)])
+ LogicalAggregate(group=[{3, 7}], X=[AVG($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{1}], EXPR$1=[AVG($2)])
+ LogicalAggregate(group=[{3, 7}], X=[AVG($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge6">
+ <Resource name="sql">
+ <![CDATA[select sum(x) from (
+ select mgr, deptno, count(sal) x from
+ sales.emp group by deptno, mgr) t]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($2)])
+ LogicalAggregate(group=[{3, 7}], X=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[SUM($2)])
+ LogicalAggregate(group=[{3, 7}], X=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge7">
+ <Resource name="sql">
+ <![CDATA[select mgr, deptno, sum(x) from (
+ select mgr, deptno, count(sal) x from
+ sales.emp group by deptno, mgr) t
+ group by cube(mgr, deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$2=[SUM($2)])
+ LogicalAggregate(group=[{3, 7}], X=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$2=[SUM($2)])
+ LogicalAggregate(group=[{3, 7}], X=[COUNT()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateMerge8">
+ <Resource name="sql">
+ <![CDATA[select sum(x) x, min(y) z from (
+ select sum(sal) x, min(sal) y from sales.emp)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{}], X=[SUM($0)], Z=[MIN($1)])
+ LogicalAggregate(group=[{}], X=[SUM($5)], Y=[MIN($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{}], X=[SUM($5)], Z=[MIN($5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testReduceNullableCase2">
<Resource name="sql">
<![CDATA[SELECT deptno, ename, CASE WHEN 1=2 THEN substring(ename,
1, cast(2 as int)) ELSE NULL end from emp group by deptno, ename, case when 1=2
then substring(ename,1, cast(2 as int)) else null end]]>
