[CALCITE-7116] Optimize queries with GROUPING SETS by converting them into equivalent UNION ALL of GROUP BY operations
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateGroupingSetsToUnionRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateGroupingSetsToUnionRule.java
new file mode 100644
index 0000000..ea3d9b2
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateGroupingSetsToUnionRule.java
@@ -0,0 +1,180 @@
+/*
+ * 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.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.Values;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.ImmutableBitSet;
+
+import com.google.common.collect.ImmutableList;
+
+import org.immutables.value.Value;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Rule that converts a {@link org.apache.calcite.rel.core.Aggregate} with
+ * {@code GROUPING SETS} into a {@code UNION ALL} of simpler aggregates.
+ *
+ * <p>Example transformation:
+ * <pre>{@code
+ * SELECT a, b, c FROM t GROUP BY GROUPING SETS ((a,b), (a,c))
+ * }</pre>
+ *
+ * <p>Transformed to:
+ *
+ * <pre>{@code
+ * SELECT a, b, NULL AS c FROM t GROUP BY a, b
+ * UNION ALL
+ * SELECT a, NULL AS b, c FROM t GROUP BY a, c
+ * }</pre>
+ */
+@Value.Enclosing
+public class AggregateGroupingSetsToUnionRule
+ extends RelRule<AggregateGroupingSetsToUnionRule.Config>
+ implements SubstitutionRule {
+
+ /** Creates an AggregateGroupingSetsToUnionRule. */
+ protected AggregateGroupingSetsToUnionRule(Config config) {
+ super(config);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Aggregate aggregate = call.rel(0);
+
+ if (Aggregate.isSimple(aggregate)) {
+ return;
+ }
+
+ final RelBuilder relBuilder = call.builder();
+ final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder();
+
+ final RelNode input = aggregate.getInput();
+ final RelDataType rowType = aggregate.getRowType();
+ final ImmutableBitSet oriGroupSet = aggregate.getGroupSet();
+ final List<RelNode> unionInputs = new ArrayList<>();
+
+ for (ImmutableBitSet subGroupSet : aggregate.getGroupSets()) {
+ relBuilder.push(input);
+ final List<RexNode> subProjects = new ArrayList<>();
+
+ // Process aggregate group set
+ RelDataType subAggregateType =
+ Aggregate.deriveRowType(relBuilder.getTypeFactory(), relBuilder.peek().getRowType(),
+ false, subGroupSet, ImmutableList.of(subGroupSet), ImmutableList.of());
+ for (int i = 0; i < oriGroupSet.cardinality(); i++) {
+ int groupKey = oriGroupSet.nth(i);
+ if (subGroupSet.get(groupKey)) {
+ subProjects.add(
+ RexInputRef.of(
+ subGroupSet.indexOf(groupKey),
+ subAggregateType));
+ } else {
+ // If the groupKey is not in the GroupSet, use null as a placeholder.
+ subProjects.add(rexBuilder.makeNullLiteral(relBuilder.field(groupKey).getType()));
+ }
+ }
+
+ // Process aggregate calls
+ List<AggregateCall> subAggCalls = new ArrayList<>();
+ for (AggregateCall aggCall : aggregate.getAggCallList()) {
+ switch (aggCall.getAggregation().getKind()) {
+ case GROUPING:
+ int groupingValue = evaluateGroupingFunction(subGroupSet, aggCall.getArgList());
+ subProjects.add(
+ rexBuilder.makeLiteral(groupingValue, aggCall.getType(), true));
+ break;
+ case GROUP_ID:
+ // GROUP_ID is removed during RelNode conversion, no handling needed here.
+ return;
+ case GROUPING_ID:
+ // The GROUPING_ID aggregate function has been marked as deprecated
+ // and is no longer supported.
+ return;
+ default:
+ subProjects.add(
+ new RexInputRef(
+ subGroupSet.cardinality() + subAggCalls.size(),
+ aggCall.getType()));
+ subAggCalls.add(aggCall);
+ break;
+ }
+ }
+
+ relBuilder.aggregate(relBuilder.groupKey(subGroupSet), subAggCalls)
+ .project(subProjects, rowType.getFieldNames());
+
+ unionInputs.add(relBuilder.build());
+ }
+
+ relBuilder.pushAll(unionInputs)
+ .union(true, unionInputs.size());
+
+ call.transformTo(relBuilder.build());
+ }
+
+ private static int evaluateGroupingFunction(ImmutableBitSet groupSet, List<Integer> argIndices) {
+ final int argCount = argIndices.size();
+ if (argCount >= Integer.SIZE) {
+ throw new IllegalArgumentException(
+ "Too many grouping keys. Maximum is " + (Integer.SIZE - 1) + " for grouping functions.");
+ }
+
+ int result = 0;
+ for (int k = 0; k < argCount; k++) {
+ int index = argIndices.get(argCount - 1 - k);
+ if (!groupSet.get(index)) {
+ result |= 1 << k;
+ }
+ }
+ return result;
+ }
+
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableAggregateGroupingSetsToUnionRule.Config.of()
+ .withOperandFor(Aggregate.class, Values.class);
+
+ @Override default AggregateGroupingSetsToUnionRule toRule() {
+ return new AggregateGroupingSetsToUnionRule(this);
+ }
+
+ /** Defines an operand tree for the given classes. */
+ default Config withOperandFor(Class<? extends Aggregate> aggregateClass,
+ Class<? extends Values> valuesClass) {
+ return withOperandSupplier(b0 ->
+ b0.operand(aggregateClass)
+ .predicate(aggregate -> aggregate.getGroupType() != Aggregate.Group.SIMPLE)
+ .anyInputs())
+ .as(Config.class);
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 436a77a..938df46 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -940,4 +940,9 @@ private CoreRules() {}
/** Rule that remove duplicate {@link Sort} keys. */
public static final SortRemoveDuplicateKeysRule SORT_REMOVE_DUPLICATE_KEYS =
SortRemoveDuplicateKeysRule.Config.DEFAULT.toRule();
+
+ /** Rule that converts GROUPING SETS of {@link Aggregate}
+ * into equivalent {@link Union} ALL of GROUP BY operations. */
+ public static final AggregateGroupingSetsToUnionRule AGGREGATE_GROUPING_SETS_TO_UNION =
+ AggregateGroupingSetsToUnionRule.Config.DEFAULT.toRule();
}
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 5f846ed..01a68fe 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -34,6 +34,7 @@
import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.logical.LogicalAggregate;
import org.apache.calcite.rel.logical.LogicalFilter;
+import org.apache.calcite.rel.rules.AggregateGroupingSetsToUnionRule;
import org.apache.calcite.rel.rules.AggregateJoinTransposeRule;
import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
import org.apache.calcite.rel.rules.CoreRules;
@@ -10626,6 +10627,48 @@ private void checkLiteral2(String expression, String expected) {
relFn(relFn).ok(expected);
}
+ /** Test case of
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7116">[CALCITE-7116]
+ * Optimize queries with GROUPING SETS by converting them
+ * into equivalent UNION ALL of GROUP BY operations</a>. */
+ @Test void testAggregateGroupingSetsToUnionRule() {
+ final String query = "SELECT deptno, job, sal, SUM(comm),\n"
+ + " GROUPING(deptno) AS deptno_flag,\n"
+ + " GROUPING(job) AS job_flag,\n"
+ + " GROUPING(sal) AS sal_flag,\n"
+ + " GROUP_ID() AS group_id\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS ((deptno, job), (deptno, sal), (deptno, job))";
+ final String expected = "SELECT \"DEPTNO\", \"JOB\", \"SAL\", \"EXPR$3\", \"DEPTNO_FLAG\","
+ + " \"JOB_FLAG\", \"SAL_FLAG\", 0 AS \"GROUP_ID\"\nFROM (SELECT \"DEPTNO\", \"JOB\","
+ + " CAST(NULL AS DECIMAL(7, 2)) AS \"SAL\", SUM(\"COMM\") AS \"EXPR$3\","
+ + " 0 AS \"DEPTNO_FLAG\", 0 AS \"JOB_FLAG\", 1 AS \"SAL_FLAG\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "GROUP BY \"DEPTNO\", \"JOB\"\n"
+ + "UNION ALL\n"
+ + "SELECT \"DEPTNO\", CAST(NULL AS VARCHAR(9) CHARACTER SET \"ISO-8859-1\") AS \"JOB\","
+ + " \"SAL\", SUM(\"COMM\") AS \"EXPR$3\", 0 AS \"DEPTNO_FLAG\", 1 AS \"JOB_FLAG\","
+ + " 0 AS \"SAL_FLAG\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\nGROUP BY \"DEPTNO\", \"SAL\") AS \"t5\"\n"
+ + "UNION ALL\nSELECT \"DEPTNO\", \"JOB\", CAST(NULL AS DECIMAL(7, 2)) AS \"SAL\","
+ + " SUM(\"COMM\"), GROUPING(\"DEPTNO\") AS \"DEPTNO_FLAG\","
+ + " GROUPING(\"JOB\") AS \"JOB_FLAG\", 1 AS \"SAL_FLAG\", 1 AS \"GROUP_ID\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "GROUP BY \"DEPTNO\", \"JOB\"";
+
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(AggregateGroupingSetsToUnionRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ RuleSet rules =
+ RuleSets.ofList(CoreRules.AGGREGATE_GROUPING_SETS_TO_UNION);
+
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withCalcite()
+ .optimize(rules, hepPlanner)
+ .ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
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 32626d9..67320f6 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -11195,4 +11195,40 @@ private void checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
.withRule(CoreRules.SORT_REMOVE_DUPLICATE_KEYS)
.check();
}
+
+ /** Test case of
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7116">[CALCITE-7116]
+ * Optimize queries with GROUPING SETS by converting them
+ * into equivalent UNION ALL of GROUP BY operations</a>. */
+ @Test void testAggregateGroupingSetsToUnionRule() {
+ final String sql = "SELECT deptno, job, sal, SUM(comm)\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS ((deptno, job), (deptno, sal))";
+ sql(sql)
+ .withRule(CoreRules.AGGREGATE_GROUPING_SETS_TO_UNION)
+ .check();
+ }
+
+ /** Test case of
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7116">[CALCITE-7116]
+ * Optimize queries with GROUPING SETS by converting them
+ * into equivalent UNION ALL of GROUP BY operations</a>. */
+ @Test void testAggregateGroupingSetsToUnionRuleGroupingGrouId() {
+ final String sql = "SELECT deptno, job, sal, SUM(comm),\n"
+ + " GROUPING(deptno) AS deptno_flag,\n"
+ + " GROUPING(job) AS job_flag,\n"
+ + " GROUPING(sal) AS sal_flag,\n"
+ + " GROUPING(deptno, sal) AS deptno_sal_flag,\n"
+ + " GROUP_ID() AS group_id\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS ((deptno, job),(deptno, job, sal),(deptno, sal))";
+
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(CoreRules.AGGREGATE_GROUPING_SETS_TO_UNION)
+ .build();
+
+ sql(sql)
+ .withProgram(program)
+ .check();
+ }
}
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 708e612..a404c11 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -344,6 +344,71 @@
]]>
</Resource>
</TestCase>
+ <TestCase name="testAggregateGroupingSetsToUnionRule">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, job, sal, SUM(comm)
+FROM emp
+GROUP BY GROUPING SETS ((deptno, job), (deptno, sal))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}]], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0], JOB=[$1], SAL=[null:INTEGER], EXPR$3=[$2])
+ LogicalAggregate(group=[{0, 1}], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0], JOB=[null:VARCHAR(10)], SAL=[$1], EXPR$3=[$2])
+ LogicalAggregate(group=[{0, 2}], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAggregateGroupingSetsToUnionRuleGroupingGrouId">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, job, sal, SUM(comm),
+ GROUPING(deptno) AS deptno_flag,
+ GROUPING(job) AS job_flag,
+ GROUPING(sal) AS sal_flag,
+ GROUPING(deptno, sal) AS deptno_sal_flag,
+ GROUP_ID() AS group_id
+FROM emp
+GROUP BY GROUPING SETS ((deptno, job),(deptno, job, sal),(deptno, sal))]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], JOB=[$1], SAL=[$2], EXPR$3=[$3], DEPTNO_FLAG=[$4], JOB_FLAG=[$5], SAL_FLAG=[$6], DEPTNO_SAL_FLAG=[$7], GROUP_ID=[0:BIGINT])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0, 2}]], EXPR$3=[SUM($3)], DEPTNO_FLAG=[GROUPING($0)], JOB_FLAG=[GROUPING($1)], SAL_FLAG=[GROUPING($2)], DEPTNO_SAL_FLAG=[GROUPING($0, $2)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], JOB=[$1], SAL=[$2], EXPR$3=[$3], DEPTNO_FLAG=[$4], JOB_FLAG=[$5], SAL_FLAG=[$6], DEPTNO_SAL_FLAG=[$7], GROUP_ID=[0:BIGINT])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0], JOB=[$1], SAL=[$2], EXPR$3=[$3], DEPTNO_FLAG=[0:BIGINT], JOB_FLAG=[0:BIGINT], SAL_FLAG=[0:BIGINT], DEPTNO_SAL_FLAG=[0:BIGINT])
+ LogicalAggregate(group=[{0, 1, 2}], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0], JOB=[$1], SAL=[null:INTEGER], EXPR$3=[$2], DEPTNO_FLAG=[0:BIGINT], JOB_FLAG=[0:BIGINT], SAL_FLAG=[1:BIGINT], DEPTNO_SAL_FLAG=[1:BIGINT])
+ LogicalAggregate(group=[{0, 1}], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0], JOB=[null:VARCHAR(10)], SAL=[$1], EXPR$3=[$2], DEPTNO_FLAG=[0:BIGINT], JOB_FLAG=[1:BIGINT], SAL_FLAG=[0:BIGINT], DEPTNO_SAL_FLAG=[0:BIGINT])
+ LogicalAggregate(group=[{0, 2}], EXPR$3=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5], COMM=[$6])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testAggregateJoinRemove1">
<Resource name="sql">
<![CDATA[select distinct e.deptno from sales.emp e
diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq
index 3852b29..f563e0b 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -4012,4 +4012,118 @@
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-7116] Optimize queries with GROUPING SETS by converting them into equivalent UNION ALL of GROUP BY operations
+# Before SQL
+SELECT deptno, job, sal, SUM(comm),
+ GROUPING(deptno) AS deptno_flag,
+ GROUPING(job) AS job_flag,
+ GROUPING(sal) AS sal_flag,
+ GROUP_ID() AS group_id
+FROM emp
+GROUP BY GROUPING SETS ((deptno, job), (deptno, sal), (deptno, job));
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+| DEPTNO | JOB | SAL | EXPR$3 | DEPTNO_FLAG | JOB_FLAG | SAL_FLAG | GROUP_ID |
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+| 10 | CLERK | | | 0 | 0 | 1 | 0 |
+| 10 | CLERK | | | 0 | 0 | 1 | 1 |
+| 10 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 10 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 | 0 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 | 1 |
+| 10 | | 1300.00 | | 0 | 1 | 0 | 0 |
+| 10 | | 2450.00 | | 0 | 1 | 0 | 0 |
+| 10 | | 5000.00 | | 0 | 1 | 0 | 0 |
+| 20 | ANALYST | | | 0 | 0 | 1 | 0 |
+| 20 | ANALYST | | | 0 | 0 | 1 | 1 |
+| 20 | CLERK | | | 0 | 0 | 1 | 0 |
+| 20 | CLERK | | | 0 | 0 | 1 | 1 |
+| 20 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 20 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 20 | | 1100.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 2975.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 3000.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 800.00 | | 0 | 1 | 0 | 0 |
+| 30 | CLERK | | | 0 | 0 | 1 | 0 |
+| 30 | CLERK | | | 0 | 0 | 1 | 1 |
+| 30 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 30 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 | 0 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 | 1 |
+| 30 | | 1250.00 | 1900.00 | 0 | 1 | 0 | 0 |
+| 30 | | 1500.00 | 0.00 | 0 | 1 | 0 | 0 |
+| 30 | | 1600.00 | 300.00 | 0 | 1 | 0 | 0 |
+| 30 | | 2850.00 | | 0 | 1 | 0 | 0 |
+| 30 | | 950.00 | | 0 | 1 | 0 | 0 |
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+(30 rows)
+
+!ok
+
+# After SQL
+# This SQL comes from RelToSqlConverterTest#testAggregateGroupingSetsToUnionRule(). This result is the same as the result of Before SQL.
+SELECT DEPTNO, JOB, SAL, EXPR$3, DEPTNO_FLAG, JOB_FLAG, SAL_FLAG, 0 AS GROUP_ID
+FROM (
+ SELECT DEPTNO, JOB, CAST(NULL AS DECIMAL(7, 2)) AS SAL,
+ SUM(COMM) AS EXPR$3,
+ 0 AS DEPTNO_FLAG,
+ 0 AS JOB_FLAG,
+ 1 AS SAL_FLAG
+ FROM EMP
+ GROUP BY DEPTNO, JOB
+ UNION ALL
+ SELECT DEPTNO, CAST(NULL AS VARCHAR(9)) AS JOB, SAL,
+ SUM(COMM) AS EXPR$3,
+ 0 AS DEPTNO_FLAG,
+ 1 AS JOB_FLAG,
+ 0 AS SAL_FLAG
+ FROM EMP
+ GROUP BY DEPTNO, SAL
+) t5
+UNION ALL
+SELECT DEPTNO, JOB, CAST(NULL AS DECIMAL(7, 2)) AS SAL,
+ SUM(COMM),
+ GROUPING(DEPTNO) AS DEPTNO_FLAG,
+ GROUPING(JOB) AS JOB_FLAG,
+ 1 AS SAL_FLAG,
+ 1 AS GROUP_ID
+FROM EMP
+GROUP BY DEPTNO, JOB;
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+| DEPTNO | JOB | SAL | EXPR$3 | DEPTNO_FLAG | JOB_FLAG | SAL_FLAG | GROUP_ID |
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+| 10 | CLERK | | | 0 | 0 | 1 | 0 |
+| 10 | CLERK | | | 0 | 0 | 1 | 1 |
+| 10 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 10 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 | 0 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 | 1 |
+| 10 | | 1300.00 | | 0 | 1 | 0 | 0 |
+| 10 | | 2450.00 | | 0 | 1 | 0 | 0 |
+| 10 | | 5000.00 | | 0 | 1 | 0 | 0 |
+| 20 | ANALYST | | | 0 | 0 | 1 | 0 |
+| 20 | ANALYST | | | 0 | 0 | 1 | 1 |
+| 20 | CLERK | | | 0 | 0 | 1 | 0 |
+| 20 | CLERK | | | 0 | 0 | 1 | 1 |
+| 20 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 20 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 20 | | 1100.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 2975.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 3000.00 | | 0 | 1 | 0 | 0 |
+| 20 | | 800.00 | | 0 | 1 | 0 | 0 |
+| 30 | CLERK | | | 0 | 0 | 1 | 0 |
+| 30 | CLERK | | | 0 | 0 | 1 | 1 |
+| 30 | MANAGER | | | 0 | 0 | 1 | 0 |
+| 30 | MANAGER | | | 0 | 0 | 1 | 1 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 | 0 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 | 1 |
+| 30 | | 1250.00 | 1900.00 | 0 | 1 | 0 | 0 |
+| 30 | | 1500.00 | 0.00 | 0 | 1 | 0 | 0 |
+| 30 | | 1600.00 | 300.00 | 0 | 1 | 0 | 0 |
+| 30 | | 2850.00 | | 0 | 1 | 0 | 0 |
+| 30 | | 950.00 | | 0 | 1 | 0 | 0 |
++--------+-----------+---------+---------+-------------+----------+----------+----------+
+(30 rows)
+
+!ok
+
# End agg.iq
