This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 37a3ecc2c0 [CALCITE-5465] Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES
produces an incorrect plan when sql has distinct agg-call with rollup
37a3ecc2c0 is described below
commit 37a3ecc2c04bafb3b2a657a0e15c47e98e6cad7c
Author: Zhen Chen <[email protected]>
AuthorDate: Sat Nov 22 10:19:13 2025 +0800
[CALCITE-5465] Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES produces an
incorrect plan when sql has distinct agg-call with rollup
---
.../AggregateExpandDistinctAggregatesRule.java | 375 +++++++++++++++++----
.../org/apache/calcite/test/RelOptRulesTest.java | 39 +++
.../org/apache/calcite/test/RelOptRulesTest.xml | 164 ++++++---
core/src/test/resources/sql/agg.iq | 135 +++++++-
core/src/test/resources/sql/sub-query.iq | 8 +-
.../org/apache/calcite/test/SparkAdapterTest.java | 8 +-
6 files changed, 612 insertions(+), 117 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
index 0c17d87d71..b25eceb38e 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
@@ -31,7 +31,6 @@
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.runtime.PairList;
-import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlSumEmptyIsZeroAggFunction;
@@ -52,6 +51,7 @@
import java.util.ArrayList;
import java.util.Collection;
+import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
@@ -61,6 +61,7 @@
import java.util.NavigableSet;
import java.util.Set;
import java.util.TreeSet;
+import java.util.function.BiConsumer;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@@ -425,39 +426,116 @@ private static RelBuilder
convertSingletonDistinct(RelBuilder relBuilder,
return relBuilder;
}
+ /**
+ * Rewrite aggregates that use GROUPING SETS. The following SQL/plan example
+ * serves as the concrete blueprint, starting from the original statement and
+ * plan-before outputs and then rebuilding the plan-after tree from the
bottom
+ * (line 7) back to the top (line 1):
+ *
+ * <p>Original SQL:
+ * <pre>{@code
+ * SELECT deptno, COUNT(DISTINCT sal)
+ * FROM emp
+ * GROUP BY ROLLUP(deptno)
+ * }</pre>
+ *
+ * <p>Plan before rewrite:
+ * <pre>{@code
+ * LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT
$1)])
+ * LogicalProject(DEPTNO=[$7], SAL=[$5])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * }</pre>
+ *
+ * <p>Plan after rewrite (lines referenced below):
+ * <pre>{@code
+ * 1 LogicalProject(DEPTNO=[$0],
+ * EXPR$1=[CAST(CASE(=($5, 0), $1, =($5, 1), $2, null:BIGINT)):BIGINT
NOT NULL])
+ * 2 LogicalFilter(condition=[OR(AND(=($5, 0), >($3, 0)), =($5, 1))])
+ * 3 LogicalAggregate(group=[{0}], groups=[[{0}, {}]],
+ * EXPR$1_g0=[COUNT($1) FILTER $2],
+ * EXPR$1_g1=[COUNT($1) FILTER $4],
+ * $g_present_0=[COUNT() FILTER $3],
+ * $g_present_1=[COUNT() FILTER $5],
+ * $g_final=[GROUPING($0)])
+ * 4 LogicalProject(DEPTNO=[$0], SAL=[$1],
+ * $g_0=[=($2, 0)], $g_1=[=($2, 1)],
+ * $g_2=[=($2, 2)], $g_3=[=($2, 3)])
+ * 5 LogicalAggregate(group=[{0, 1}],
+ * groups=[[{0, 1}, {0}, {1}, {}]], $g=[GROUPING($0, $1)])
+ * 6 LogicalProject(DEPTNO=[$7], SAL=[$5])
+ * 7 LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * }</pre>
+ *
+ * <p>The method performs the following actions:
+ * <ul>
+ * <li>Reuse the incoming scan and projection (lines 7 and 6) by pushing the
+ * original aggregate input onto the builder.</li>
+ * <li>Enumerate all grouping-set combinations and run the "bottom" aggregate
+ * over {@code fullGroupSet} to materialize line 5, including the internal
+ * {@code GROUPING()} value.</li>
+ * <li>Project the boolean selector columns that compare {@code GROUPING()}
+ * outputs to the required combinations, which surfaces line 4.</li>
+ * <li>Build the "upper" grouping-set aggregates with per-set FILTER clauses,
+ * reproducing line 3 and retaining presence counters / grouping ids.</li>
+ * <li>Assemble {@code keepConditions} so we can emit the filter of line 2
that
+ * drops internal-only rows.</li>
+ * <li>Produce the final projection (line 1) that routes each aggregate
result
+ * to the user-visible columns.</li>
+ * </ul>
+ */
private static void rewriteUsingGroupingSets(RelOptRuleCall call,
Aggregate aggregate) {
+ final ImmutableBitSet aggregateGroupSet = aggregate.getGroupSet();
+ final ImmutableList<ImmutableBitSet> aggregateGroupingSets =
aggregate.getGroupSets();
+
final Set<ImmutableBitSet> groupSetTreeSet =
new TreeSet<>(ImmutableBitSet.ORDERING);
- // GroupSet to distinct filter arg map,
- // filterArg will be -1 for non-distinct agg call.
- // Using `Set` here because it's possible that two agg calls
- // have different filterArgs but same groupSet.
+ // Map from a set of group keys -> which filter args (if any) contributed
+ // to that combination. Used to generate boolean marker columns later which
+ // indicate whether a bottom-row should be considered for a particular
+ // (grouping-set, filter) combination.
final Map<ImmutableBitSet, Set<Integer>> distinctFilterArgMap = new
HashMap<>();
+
+ // Enumerating every required grouping-set combination, including distinct
+ // args or filter columns relied on by the downstream projection(line 4).
+ BiConsumer<ImmutableBitSet, Integer> addGroupSet = (groupSet, filterArg)
-> {
+ groupSetTreeSet.add(groupSet);
+ distinctFilterArgMap.computeIfAbsent(groupSet, g -> new
HashSet<>()).add(filterArg);
+ };
+
+ // Always include the base group set and each declared grouping set. -1
means "no filter".
+ addGroupSet.accept(aggregateGroupSet, -1);
+ for (ImmutableBitSet groupingSet : aggregateGroupingSets) {
+ addGroupSet.accept(groupingSet, -1);
+ }
+
+ // For each DISTINCT aggregate, include grouping-set combinations:
+ // (distinct args) ∪ (grouping set). This ensures we compute bottom rows
+ // at a granularity sufficient to evaluate DISTINCT per grouping set. If
+ // the DISTINCT agg has a FILTER, include that filter column in the
+ // grouping so that the downstream boolean selector can distinguish
+ // filtered rows. For example, with COUNT(DISTINCT sal) and grouping sets
+ // (deptno) and (), we add {deptno, sal} and {sal}.
for (AggregateCall aggCall : aggregate.getAggCallList()) {
- ImmutableBitSet groupSet;
- int filterArg;
if (!aggCall.isDistinct()) {
- filterArg = -1;
- groupSet = aggregate.getGroupSet();
- groupSetTreeSet.add(aggregate.getGroupSet());
- } else {
- filterArg = aggCall.filterArg;
- groupSet =
- ImmutableBitSet.of(aggCall.getArgList())
- .setIf(filterArg, filterArg >= 0)
- .union(aggregate.getGroupSet());
- groupSetTreeSet.add(groupSet);
+ continue;
+ }
+ final ImmutableBitSet args = ImmutableBitSet.of(aggCall.getArgList());
+ for (ImmutableBitSet groupingSet : aggregateGroupingSets) {
+ ImmutableBitSet groupSet = args.union(groupingSet);
+ if (aggCall.filterArg >= 0) {
+ groupSet = groupSet.set(aggCall.filterArg);
+ }
+ addGroupSet.accept(groupSet, aggCall.filterArg);
}
- Set<Integer> filterList = distinctFilterArgMap
- .computeIfAbsent(groupSet, g -> new HashSet<>());
- filterList.add(filterArg);
}
final ImmutableList<ImmutableBitSet> groupSets =
ImmutableList.copyOf(groupSetTreeSet);
+ // fullGroupSet is the union of all bits that appear in any grouping set.
final ImmutableBitSet fullGroupSet = ImmutableBitSet.union(groupSets);
+ // Whether the bottom aggregate must account for an "empty" grouping set.
final boolean bottomHasEmptyGroup =
groupSets.contains(ImmutableBitSet.of());
final List<AggregateCall> distinctAggCalls = new ArrayList<>();
@@ -472,12 +550,19 @@ private static void
rewriteUsingGroupingSets(RelOptRuleCall call,
}
final RelBuilder relBuilder = call.builder();
+ final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder();
+ // Lines 7 & 6: reuse the existing scan+projection feeding the original
aggregate.
relBuilder.push(aggregate.getInput());
- final int groupCount = fullGroupSet.cardinality();
-
- // Get the base ordinal of filter args for different groupSets.
+ final int bottomGroupCount = fullGroupSet.cardinality();
+
+ // Map each (groupSet, filterArg) pair to an output field index in the
+ // bottom projection. These fields become boolean/marker columns used to
+ // implement FILTER(...) and to detect whether a grouping set had rows.
+ // The numbering starts after the bottom group fields and the distinct
+ // aggregate columns; 'z' is the running output field index for these
+ // selector markers.
final Map<Pair<ImmutableBitSet, Integer>, Integer> filters = new
LinkedHashMap<>();
- int z = groupCount + distinctAggCalls.size();
+ int z = bottomGroupCount + distinctAggCalls.size();
for (ImmutableBitSet groupSet : groupSets) {
Set<Integer> filterArgList = distinctFilterArgMap.get(groupSet);
for (Integer filterArg : requireNonNull(filterArgList, "filterArgList"))
{
@@ -492,12 +577,14 @@ private static void
rewriteUsingGroupingSets(RelOptRuleCall call,
null, RelCollations.EMPTY,
bottomHasEmptyGroup, relBuilder.peek(), null, "$g"));
+ // Line 5: bottom aggregate materializes every grouping-set combination and
+ // produces the GROUPING() value needed by later steps.
relBuilder.aggregate(
relBuilder.groupKey(fullGroupSet, groupSets),
distinctAggCalls);
- // GROUPING returns an integer (0 or 1). Add a project to convert those
- // values to BOOLEAN.
+ // Line 4: convert GROUPING() into named selector columns ($g_*) that pick
+ // rows for each grouping-set/filter combination.
if (!filters.isEmpty()) {
final List<RexNode> nodes = new ArrayList<>(relBuilder.fields());
final RexNode nodeZ = nodes.remove(nodes.size() - 1);
@@ -520,43 +607,208 @@ private static void
rewriteUsingGroupingSets(RelOptRuleCall call,
relBuilder.project(nodes);
}
- int x = groupCount;
- final ImmutableBitSet groupSet = aggregate.getGroupSet();
- final List<AggregateCall> newCalls = new ArrayList<>();
- for (AggregateCall aggCall : aggregate.getAggCallList()) {
- final int newFilterArg;
- final List<Integer> newArgList;
- final SqlAggFunction aggregation;
+ // Compute the remapped top-group key and grouping sets. The top-group key
+ // selects which fields of the bottom result correspond to the original
+ // aggregate's group-by columns. Upper aggregates(line 3) will group by
this key.
+ final ImmutableBitSet topGroupKey = remap(fullGroupSet, aggregateGroupSet);
+ final ImmutableList<ImmutableBitSet> topGroupingSets =
+ remap(fullGroupSet, aggregate.getGroupSets());
+ final int topGroupCount = topGroupKey.cardinality();
+ final boolean needsGroupingIndicators = aggregate.getGroupType() !=
Group.SIMPLE;
+ final List<Integer> groupingIndicatorOrdinals;
+ if (needsGroupingIndicators) {
+ groupingIndicatorOrdinals =
+ new ArrayList<>(Collections.nCopies(aggregateGroupingSets.size(),
-1));
+ } else {
+ groupingIndicatorOrdinals = ImmutableList.of();
+ }
+
+ int valueIndex = bottomGroupCount;
+ // line 3 will be built from this list
+ final List<AggregateCall> upperAggCalls = new ArrayList<>();
+ final List<List<Integer>> aggCallOrdinals = new ArrayList<>();
+ final List<AggregateCall> aggCalls = aggregate.getAggCallList();
+
+ // The first part of line 3: Build upper aggregates per declared grouping
set.
+ // For each original aggCall we create one upper agg per declared grouping
set.
+ // The upper aggregate groups by {@code topGroupKey} and uses the boolean
marker
+ // columns (placed at known ordinals) as the FILTER argument for the
+ // corresponding per-group aggregation. The list {@code aggCallOrdinals}
+ // records, for each original aggCall, the output field ordinals of the
+ // corresponding upper-aggregate results (one per grouping set).
+ for (AggregateCall aggCall : aggCalls) {
+ final List<Integer> ordinals = new ArrayList<>();
if (!aggCall.isDistinct()) {
- aggregation = SqlStdOperatorTable.MIN;
- newArgList = ImmutableIntList.of(x++);
- newFilterArg =
- requireNonNull(filters.get(Pair.of(groupSet, -1)),
- "filters.get(Pair.of(groupSet, -1))");
+ final int inputIndex = valueIndex++;
+ final List<Integer> args = ImmutableIntList.of(inputIndex);
+ for (int g = 0; g < aggregateGroupingSets.size(); g++) {
+ final ImmutableBitSet groupingSet = aggregateGroupingSets.get(g);
+ final int newFilterArg =
+ requireNonNull(filters.get(Pair.of(groupingSet, -1)),
+ () -> "filters.get(" + groupingSet + ", -1)");
+ final String upperAggName = upperAggCallName(aggCall, g);
+ // Each filtered grouping set emits exactly one row per group,
+ // so MIN just passes that value through without re-aggregation
+ final AggregateCall newCall =
+ AggregateCall.create(aggCall.getParserPosition(),
+ SqlStdOperatorTable.MIN, false, aggCall.isApproximate(),
+ aggCall.ignoreNulls(), aggCall.rexList, args, newFilterArg,
+ aggCall.distinctKeys, aggCall.collation,
aggregate.hasEmptyGroup(),
+ relBuilder.peek(), null, upperAggName);
+ upperAggCalls.add(newCall);
+ ordinals.add(topGroupCount + upperAggCalls.size() - 1);
+ }
} else {
- aggregation = aggCall.getAggregation();
- newArgList = remap(fullGroupSet, aggCall.getArgList());
- final ImmutableBitSet newGroupSet =
ImmutableBitSet.of(aggCall.getArgList())
- .setIf(aggCall.filterArg, aggCall.filterArg >= 0)
- .union(groupSet);
- newFilterArg =
- requireNonNull(filters.get(Pair.of(newGroupSet,
aggCall.filterArg)),
- "filters.get(of(newGroupSet, aggCall.filterArg))");
+ final List<Integer> newArgList = remap(fullGroupSet,
aggCall.getArgList());
+ for (int g = 0; g < aggregateGroupingSets.size(); g++) {
+ final ImmutableBitSet groupingSet = aggregateGroupingSets.get(g);
+ final ImmutableBitSet newGroupSet =
ImmutableBitSet.of(aggCall.getArgList())
+ .setIf(aggCall.filterArg, aggCall.filterArg >= 0)
+ .union(groupingSet);
+ final int newFilterArg =
+ requireNonNull(filters.get(Pair.of(newGroupSet,
aggCall.filterArg)),
+ () -> "filters.get(" + newGroupSet + ", " +
aggCall.filterArg + ")");
+ final String upperAggName = upperAggCallName(aggCall, g);
+ final AggregateCall newCall =
+ AggregateCall.create(aggCall.getParserPosition(),
aggCall.getAggregation(), false,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
+ aggCall.rexList, newArgList, newFilterArg,
+ aggCall.distinctKeys, aggCall.collation,
+ aggregate.hasEmptyGroup(), relBuilder.peek(), null,
upperAggName);
+ upperAggCalls.add(newCall);
+ ordinals.add(topGroupCount + upperAggCalls.size() - 1);
+ }
}
- final AggregateCall newCall =
- AggregateCall.create(aggCall.getParserPosition(), aggregation, false,
- aggCall.isApproximate(), aggCall.ignoreNulls(),
- aggCall.rexList, newArgList, newFilterArg,
- aggCall.distinctKeys, aggCall.collation,
- aggregate.hasEmptyGroup(), relBuilder.peek(), null,
aggCall.name);
- newCalls.add(newCall);
+ aggCallOrdinals.add(ordinals);
}
+ // The second part of line 3: If grouping indicators are needed
+ // (ROLLUP/CUBE/GROUPING SETS with more than one grouping set), add
+ // COUNT(...) presence calls which are later used to determine whether
+ // a grouping set produced any rows. These calls implement the
+ // semantics where empty grouping sets must still produce a result.
+ if (needsGroupingIndicators) {
+ for (int g = 0; g < aggregateGroupingSets.size(); g++) {
+ final ImmutableBitSet groupingSet = aggregateGroupingSets.get(g);
+ final Integer filterField = filters.get(Pair.of(groupingSet, -1));
+ if (filterField == null) {
+ continue;
+ }
+ final AggregateCall presenceCall =
+ AggregateCall.create(SqlStdOperatorTable.COUNT, false, false,
false,
+ ImmutableList.of(), ImmutableIntList.of(), filterField, null,
+ RelCollations.EMPTY, aggregate.hasEmptyGroup(),
relBuilder.peek(), null,
+ "$g_present_" + g);
+ upperAggCalls.add(presenceCall);
+ groupingIndicatorOrdinals.set(g, topGroupCount + upperAggCalls.size()
- 1);
+ }
+ }
+
+ // The third part of line 3: If there are multiple declared grouping sets,
+ // then we need a GROUPING() value in the upper aggregate so we can later
+ // route results to the correct output using CASE expressions. Compute and
+ // append that grouping-call if required.
+ final boolean needsGroupingId = aggregateGroupingSets.size() > 1;
+ final int groupingIdOrdinal;
+ if (needsGroupingId) {
+ final ImmutableBitSet remappedGroupSet = remap(fullGroupSet,
aggregateGroupSet);
+ final AggregateCall groupingCall =
+ AggregateCall.create(SqlStdOperatorTable.GROUPING, false, false,
false,
+ ImmutableList.of(),
ImmutableIntList.copyOf(remappedGroupSet.asList()), -1, null,
+ RelCollations.EMPTY, aggregate.hasEmptyGroup(),
relBuilder.peek(), null, "$g_final");
+ upperAggCalls.add(groupingCall);
+ groupingIdOrdinal = topGroupCount + upperAggCalls.size() - 1;
+ } else {
+ groupingIdOrdinal = -1;
+ }
+
+ // The final part of line 3: build the upper aggregate layer, grouping by
the
+ // original keys and applying FILTERs (and presence/grouping columns) per
+ // declared set.
relBuilder.aggregate(
- relBuilder.groupKey(
- remap(fullGroupSet, groupSet),
- remap(fullGroupSet, aggregate.getGroupSets())),
- newCalls);
+ relBuilder.groupKey(topGroupKey, topGroupingSets),
+ upperAggCalls);
+
+ final ImmutableList<Integer> groupingIdColumns =
+ ImmutableList.copyOf(Util.range(topGroupCount));
+ final RexNode groupingIdRef = needsGroupingId ?
relBuilder.field(groupingIdOrdinal) : null;
+
+ if (needsGroupingIndicators) {
+ final List<RexNode> keepConditions = new ArrayList<>();
+ for (int g = 0; g < aggregateGroupingSets.size(); g++) {
+ final int indicatorOrdinal = groupingIndicatorOrdinals.get(g);
+ if (indicatorOrdinal < 0) {
+ continue;
+ }
+ final ImmutableBitSet groupingSet = aggregateGroupingSets.get(g);
+ final RexNode requiredRows;
+ if (groupingSet.isEmpty()) {
+ // Empty grouping sets must still produce a row even if the input is
+ // empty, so do not require any contributing tuples.
+ requiredRows = relBuilder.literal(true);
+ } else {
+ requiredRows =
+ relBuilder.greaterThan(relBuilder.field(indicatorOrdinal),
+ relBuilder.literal(0));
+ }
+
+ final RexNode groupingMatches;
+ if (needsGroupingId) {
+ final long groupingValue =
+ groupValue(groupingIdColumns, remap(aggregateGroupSet,
groupingSet));
+ groupingMatches =
+ relBuilder.equals(requireNonNull(groupingIdRef, "groupingIdRef"),
+ relBuilder.literal(groupingValue));
+ } else {
+ groupingMatches = relBuilder.literal(true);
+ }
+ keepConditions.add(relBuilder.and(groupingMatches, requiredRows));
+ }
+
+ // Line 2: filter away rows produced solely for internal combinations.
+ if (!keepConditions.isEmpty()) {
+ RexNode condition = keepConditions.get(0);
+ for (int i = 1; i < keepConditions.size(); i++) {
+ condition = relBuilder.or(condition, keepConditions.get(i));
+ }
+ relBuilder.filter(condition);
+ }
+ }
+
+ // Assemble the projections for line 1 here
+ final List<RexNode> projects = new ArrayList<>();
+ final List<String> finalFieldNames =
aggregate.getRowType().getFieldNames();
+ for (int i = 0; i < topGroupCount; i++) {
+ projects.add(relBuilder.field(i));
+ }
+
+ for (int i = 0; i < aggCalls.size(); i++) {
+ final AggregateCall aggCall = aggCalls.get(i);
+ final List<Integer> ordinals = aggCallOrdinals.get(i);
+ if (!needsGroupingId || ordinals.size() == 1) {
+ projects.add(relBuilder.field(ordinals.get(0)));
+ continue;
+ }
+
+ final List<RexNode> caseOperands = new ArrayList<>();
+ for (int g = 0; g < aggregateGroupingSets.size(); g++) {
+ final ImmutableBitSet groupingSet = aggregateGroupingSets.get(g);
+ final long groupingValue =
+ groupValue(groupingIdColumns, remap(aggregateGroupSet,
groupingSet));
+ caseOperands.add(
+ relBuilder.equals(requireNonNull(groupingIdRef, "groupingIdRef"),
+ relBuilder.literal(groupingValue)));
+ caseOperands.add(relBuilder.field(ordinals.get(g)));
+ }
+ caseOperands.add(rexBuilder.makeNullLiteral(aggCall.getType()));
+ projects.add(
+ relBuilder.call(SqlStdOperatorTable.CASE,
+ caseOperands.toArray(new RexNode[0])));
+ }
+
+ // Line 1: final projection routes per-set aggregates back into the
original
+ // output schema (including CASE routing when needed).
+ relBuilder.project(projects, finalFieldNames);
relBuilder.convert(aggregate.getRowType(), true);
call.transformTo(relBuilder.build());
}
@@ -612,6 +864,15 @@ private static int remap(ImmutableBitSet groupSet, int
arg) {
return arg < 0 ? -1 : groupSet.indexOf(arg);
}
+ private static String upperAggCallName(AggregateCall aggCall,
+ int groupingSetIndex) {
+ String baseName = aggCall.getName();
+ if (baseName == null || baseName.isEmpty()) {
+ baseName = aggCall.getAggregation().getName();
+ }
+ return baseName + "_g" + groupingSetIndex;
+ }
+
/**
* Converts an aggregate relational expression that contains just one
* distinct aggregate function (or perhaps several over the same arguments)
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 53e4b5feef..4369727f3f 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -2395,6 +2395,45 @@ private void
checkSemiOrAntiJoinProjectTranspose(JoinRelType type) {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5465";>[CALCITE-5465]
+ * Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES produces an incorrect plan
+ * when sql has distinct agg-call with rollup</a>. */
+ @Test void testDistinctNonDistinctAggregatesWithGroupingSets() {
+ final String sql = "SELECT deptno, COUNT(DISTINCT sal)\n"
+ + "FROM emp\n"
+ + "GROUP BY ROLLUP(deptno)";
+ sql(sql)
+ .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5465";>[CALCITE-5465]
+ * Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES produces an incorrect plan
+ * when sql has distinct agg-call with rollup</a>. */
+ @Test void testDistinctNonDistinctAggregatesWithGroupingSets2() {
+ final String sql = "SELECT deptno, COUNT(DISTINCT sal), SUM(sal)\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS ((deptno), ())";
+ sql(sql)
+ .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5465";>[CALCITE-5465]
+ * Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES produces an incorrect plan
+ * when sql has distinct agg-call with rollup</a>. */
+ @Test void testDistinctNonDistinctAggregatesWithGroupingSets3() {
+ final String sql = "SELECT deptno, COUNT(DISTINCT sal), SUM(DISTINCT sal),
COUNT(*)\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS ((deptno), ())";
+ sql(sql)
+ .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES)
+ .check();
+ }
+
@Test void testDistinctNonDistinctAggregates() {
final String sql = "select emp.empno, count(*), avg(distinct
dept.deptno)\n"
+ "from sales.emp emp inner join sales.dept dept\n"
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 a9b4bc81a0..c63602eae6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -1800,9 +1800,9 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM(DISTINCT $1)],
EXPR$2=[SUM(DISTINCT $2
<Resource name="planAfter">
<![CDATA[
LogicalProject(NAME=[$0], EXPR$1=[CAST($1):BIGINT NOT NULL],
EXPR$2=[CAST($2):INTEGER NOT NULL])
- LogicalAggregate(group=[{0}], EXPR$1=[SUM($1) FILTER $3], EXPR$2=[SUM($2)
FILTER $4])
+ LogicalAggregate(group=[{0}], EXPR$1_g0=[SUM($1) FILTER $3],
EXPR$2_g0=[SUM($2) FILTER $4])
LogicalProject(NAME=[$0], CN=[$1], SM=[$2], $g_1=[=($3, 1)], $g_2=[=($3,
2)])
- LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}]],
$g=[GROUPING($0, $1, $2)])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0, 2}, {0}]],
$g=[GROUPING($0, $1, $2)])
LogicalAggregate(group=[{0}], CN=[COUNT()], SM=[SUM($1)])
LogicalProject(NAME=[$1], DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -3215,7 +3215,7 @@ LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT
$1)], EXPR$2=[SUM($2)])
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[$1], EXPR$2=[CAST($2):INTEGER NOT NULL])
- LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $3], EXPR$2=[MIN($2)
FILTER $4])
+ LogicalAggregate(group=[{0}], EXPR$1_g0=[COUNT($1) FILTER $3],
EXPR$2_g0=[MIN($2) FILTER $4])
LogicalProject(DEPTNO=[$0], ENAME=[$1], EXPR$2=[$2], $g_0=[=($3, 0)],
$g_1=[=($3, 1)])
LogicalProject(DEPTNO=[$1], ENAME=[$0], EXPR$2=[$2], $g=[$3])
LogicalAggregate(group=[{1, 7}], groups=[[{1, 7}, {7}]],
EXPR$2=[SUM($5)], $g=[GROUPING($7, $1)])
@@ -3239,7 +3239,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$2])
<![CDATA[
LogicalProject(EXPR$0=[$1], EXPR$1=[$2])
LogicalProject(DEPTNO=[$0], EXPR$0=[$1], EXPR$1=[CAST($2):INTEGER NOT NULL])
- LogicalAggregate(group=[{0}], EXPR$0=[COUNT($0) FILTER $2],
EXPR$1=[MIN($1) FILTER $2])
+ LogicalAggregate(group=[{0}], EXPR$0_g0=[COUNT($0) FILTER $2],
EXPR$1_g0=[MIN($1) FILTER $2])
LogicalProject(DEPTNO=[$0], EXPR$1=[$1], $g_0=[=($2, 0)])
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)], $g=[GROUPING($0)])
LogicalProject(DEPTNO=[$7], SAL=[$5])
@@ -3261,11 +3261,13 @@ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0},
{}]], EXPR$2=[COUNT(DISTI
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT($2)
FILTER $3])
- LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], $g_0=[=($3, 0)])
- LogicalAggregate(group=[{0, 1, 2}], $g=[GROUPING($0, $1, $2)])
- LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[CAST(CASE(=($8, 0), $2, =($8,
1), $3, =($8, 3), $4, null:BIGINT)):BIGINT NOT NULL])
+ LogicalFilter(condition=[OR(AND(=($8, 0), >($5, 0)), AND(=($8, 1), >($6,
0)), =($8, 3))])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]],
EXPR$2_g0=[COUNT($2) FILTER $3], EXPR$2_g1=[COUNT($2) FILTER $5],
EXPR$2_g2=[COUNT($2) FILTER $7], $g_present_0=[COUNT() FILTER $4],
$g_present_1=[COUNT() FILTER $6], $g_present_2=[COUNT() FILTER $8],
$g_final=[GROUPING($0, $1)])
+ LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], $g_0=[=($3, 0)],
$g_1=[=($3, 1)], $g_2=[=($3, 2)], $g_3=[=($3, 3)], $g_6=[=($3, 6)], $g_7=[=($3,
7)])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0,
2}, {0}, {2}, {}]], $g=[GROUPING($0, $1, $2)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3283,11 +3285,13 @@ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0},
{}]], EXPR$2=[COUNT(DISTI
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], EXPR$2=[COUNT($2)
FILTER $4], EXPR$3=[MIN($3) FILTER $5])
- LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], EXPR$3=[$3], $g_0=[=($4,
0)], $g_1=[=($4, 1)])
- LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}]],
EXPR$3=[SUM($3)], $g=[GROUPING($0, $1, $2)])
- LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(DEPTNO=[$0], JOB=[$1], EXPR$2=[CAST(CASE(=($11, 0), $2, =($11,
1), $3, =($11, 3), $4, null:BIGINT)):BIGINT NOT NULL], EXPR$3=[CASE(=($11, 0),
$5, =($11, 1), $6, =($11, 3), $7, null:INTEGER)])
+ LogicalFilter(condition=[OR(AND(=($11, 0), >($8, 0)), AND(=($11, 1), >($9,
0)), =($11, 3))])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]],
EXPR$2_g0=[COUNT($2) FILTER $4], EXPR$2_g1=[COUNT($2) FILTER $6],
EXPR$2_g2=[COUNT($2) FILTER $8], EXPR$3_g0=[MIN($3) FILTER $5],
EXPR$3_g1=[MIN($3) FILTER $7], EXPR$3_g2=[MIN($3) FILTER $9],
$g_present_0=[COUNT() FILTER $5], $g_present_1=[COUNT() FILTER $7],
$g_present_2=[COUNT() FILTER $9], $g_final=[GROUPING($0, $1)])
+ LogicalProject(DEPTNO=[$0], JOB=[$1], ENAME=[$2], EXPR$3=[$3],
$g_0=[=($4, 0)], $g_1=[=($4, 1)], $g_2=[=($4, 2)], $g_3=[=($4, 3)], $g_6=[=($4,
6)], $g_7=[=($4, 7)])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1, 2}, {0, 1}, {0,
2}, {0}, {2}, {}]], EXPR$3=[SUM($3)], $g=[GROUPING($0, $1, $2)])
+ LogicalProject(DEPTNO=[$7], JOB=[$2], ENAME=[$1], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3307,7 +3311,7 @@ LogicalAggregate(group=[{0}], CDDJ=[COUNT(DISTINCT $0,
$1)], S=[SUM($2)])
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], CDDJ=[$1], S=[CAST($2):INTEGER NOT NULL])
- LogicalAggregate(group=[{0}], CDDJ=[COUNT($0, $1) FILTER $3], S=[MIN($2)
FILTER $4])
+ LogicalAggregate(group=[{0}], CDDJ_g0=[COUNT($0, $1) FILTER $3],
S_g0=[MIN($2) FILTER $4])
LogicalProject(DEPTNO=[$0], JOB=[$1], S=[$2], $g_0=[=($3, 0)], $g_1=[=($3,
1)])
LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], S=[SUM($2)],
$g=[GROUPING($0, $1)])
LogicalProject(DEPTNO=[$7], JOB=[$2], SAL=[$5])
@@ -3333,7 +3337,7 @@ LogicalAggregate(group=[{0}], CDE=[COUNT(DISTINCT $1)],
CDJE=[COUNT(DISTINCT $2,
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], CDE=[$1], CDJE=[$2], CDDJ=[$3],
S=[CAST($4):INTEGER NOT NULL])
- LogicalAggregate(group=[{0}], CDE=[COUNT($1) FILTER $5], CDJE=[COUNT($2, $1)
FILTER $4], CDDJ=[COUNT($0, $2) FILTER $6], S=[MIN($3) FILTER $7])
+ LogicalAggregate(group=[{0}], CDE_g0=[COUNT($1) FILTER $5],
CDJE_g0=[COUNT($2, $1) FILTER $4], CDDJ_g0=[COUNT($0, $2) FILTER $6],
S_g0=[MIN($3) FILTER $7])
LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], S=[$3], $g_0=[=($4, 0)],
$g_1=[=($4, 1)], $g_2=[=($4, 2)], $g_3=[=($4, 3)])
LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 2, 7}, {1, 7}, {2, 7},
{7}]], S=[SUM($5)], $g=[GROUPING($7, $1, $2)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -3388,11 +3392,12 @@ LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT
$1)], EXPR$2=[COUNT(DISTINC
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $3], EXPR$2=[COUNT($2)
FILTER $4])
- LogicalProject(DEPTNO=[$0], ENAME=[$1], JOB=[$2], $g_1=[=($3, 1)],
$g_2=[=($3, 2)])
- LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], $g=[$3])
- LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 7}, {2, 7}]],
$g=[GROUPING($7, $1, $2)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(DEPTNO=[$0], EXPR$1=[$1], EXPR$2=[$2])
+ LogicalAggregate(group=[{0}], EXPR$1_g0=[COUNT($1) FILTER $3],
EXPR$2_g0=[COUNT($2) FILTER $4])
+ LogicalProject(DEPTNO=[$0], ENAME=[$1], JOB=[$2], $g_1=[=($3, 1)],
$g_2=[=($3, 2)])
+ LogicalProject(DEPTNO=[$2], ENAME=[$0], JOB=[$1], $g=[$3])
+ LogicalAggregate(group=[{1, 2, 7}], groups=[[{1, 7}, {2, 7}, {7}]],
$g=[GROUPING($7, $1, $2)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3410,10 +3415,11 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT(DISTINCT
$0)], EXPR$1=[COUNT(DISTINCT
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $2], EXPR$1=[COUNT($1)
FILTER $3])
- LogicalProject(ENAME=[$0], JOB=[$1], $g_1=[=($2, 1)], $g_2=[=($2, 2)])
- LogicalAggregate(group=[{1, 2}], groups=[[{1}, {2}]], $g=[GROUPING($1,
$2)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalAggregate(group=[{}], EXPR$0_g0=[COUNT($0) FILTER $2],
EXPR$1_g0=[COUNT($1) FILTER $3])
+ LogicalProject(ENAME=[$0], JOB=[$1], $g_1=[=($2, 1)], $g_2=[=($2, 2)])
+ LogicalAggregate(group=[{1, 2}], groups=[[{1}, {2}, {}]],
$g=[GROUPING($1, $2)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3486,10 +3492,11 @@ LogicalAggregate(group=[{}], EXPR$0=[MAX($0)],
EXPR$1=[COUNT(DISTINCT $1)])
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], EXPR$0=[MIN($1) FILTER $3], EXPR$1=[COUNT($0)
FILTER $2])
- LogicalProject(ENAME=[$0], EXPR$0=[$1], $g_0=[=($2, 0)], $g_1=[=($2, 1)])
- LogicalAggregate(group=[{1}], groups=[[{1}, {}]], EXPR$0=[MAX($7)],
$g=[GROUPING($1)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalAggregate(group=[{}], EXPR$0_g0=[MIN($1) FILTER $3],
EXPR$1_g0=[COUNT($0) FILTER $2])
+ LogicalProject(ENAME=[$0], EXPR$0=[$1], $g_0=[=($2, 0)], $g_1=[=($2, 1)])
+ LogicalAggregate(group=[{1}], groups=[[{1}, {}]], EXPR$0=[MAX($7)],
$g=[GROUPING($1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3562,6 +3569,81 @@ LogicalAggregate(group=[{0}], EXPR$1=[$SUM0($2)],
EXPR$2=[SUM($1)])
LogicalAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
LogicalProject(DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctNonDistinctAggregatesWithGroupingSets">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, COUNT(DISTINCT sal)
+FROM emp
+GROUP BY ROLLUP(deptno)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[CAST(CASE(=($5, 0), $1, =($5, 1), $2,
null:BIGINT)):BIGINT NOT NULL])
+ LogicalFilter(condition=[OR(AND(=($5, 0), >($3, 0)), =($5, 1))])
+ LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1_g0=[COUNT($1)
FILTER $2], EXPR$1_g1=[COUNT($1) FILTER $4], $g_present_0=[COUNT() FILTER $3],
$g_present_1=[COUNT() FILTER $5], $g_final=[GROUPING($0)])
+ LogicalProject(DEPTNO=[$0], SAL=[$1], $g_0=[=($2, 0)], $g_1=[=($2, 1)],
$g_2=[=($2, 2)], $g_3=[=($2, 3)])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
$g=[GROUPING($0, $1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctNonDistinctAggregatesWithGroupingSets2">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, COUNT(DISTINCT sal), SUM(sal)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), ())]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)],
EXPR$2=[SUM($1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[CAST(CASE(=($7, 0), $1, =($7, 1), $2,
null:BIGINT)):BIGINT NOT NULL], EXPR$2=[CASE(=($7, 0), $3, =($7, 1), $4,
null:INTEGER)])
+ LogicalFilter(condition=[OR(AND(=($7, 0), >($5, 0)), =($7, 1))])
+ LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1_g0=[COUNT($1)
FILTER $3], EXPR$1_g1=[COUNT($1) FILTER $5], EXPR$2_g0=[MIN($2) FILTER $4],
EXPR$2_g1=[MIN($2) FILTER $6], $g_present_0=[COUNT() FILTER $4],
$g_present_1=[COUNT() FILTER $6], $g_final=[GROUPING($0)])
+ LogicalProject(DEPTNO=[$0], SAL=[$1], EXPR$2=[$2], $g_0=[=($3, 0)],
$g_1=[=($3, 1)], $g_2=[=($3, 2)], $g_3=[=($3, 3)])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$2=[SUM($1)], $g=[GROUPING($0, $1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testDistinctNonDistinctAggregatesWithGroupingSets3">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, COUNT(DISTINCT sal), SUM(DISTINCT sal), COUNT(*)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), ())]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)],
EXPR$2=[SUM(DISTINCT $1)], EXPR$3=[COUNT()])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[CAST(CASE(=($9, 0), $1, =($9, 1), $2,
null:BIGINT)):BIGINT NOT NULL], EXPR$2=[CASE(=($9, 0), $3, =($9, 1), $4,
null:INTEGER)], EXPR$3=[CAST(CASE(=($9, 0), $5, =($9, 1), $6,
null:BIGINT)):BIGINT NOT NULL])
+ LogicalFilter(condition=[OR(AND(=($9, 0), >($7, 0)), =($9, 1))])
+ LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1_g0=[COUNT($1)
FILTER $3], EXPR$1_g1=[COUNT($1) FILTER $5], EXPR$2_g0=[SUM($1) FILTER $3],
EXPR$2_g1=[SUM($1) FILTER $5], EXPR$3_g0=[MIN($2) FILTER $4],
EXPR$3_g1=[MIN($2) FILTER $6], $g_present_0=[COUNT() FILTER $4],
$g_present_1=[COUNT() FILTER $6], $g_final=[GROUPING($0)])
+ LogicalProject(DEPTNO=[$0], SAL=[$1], EXPR$3=[$2], $g_0=[=($3, 0)],
$g_1=[=($3, 1)], $g_2=[=($3, 2)], $g_3=[=($3, 3)])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {1}, {}]],
EXPR$3=[COUNT()], $g=[GROUPING($0, $1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3602,11 +3684,12 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT(DISTINCT $0)
FILTER $1], EXPR$1=[COUN
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $3], EXPR$1=[COUNT($1)
FILTER $2])
- LogicalProject(C=[$0], D=[$1], $g_0_f_0=[AND(=($2, 0), $0)],
$g_0_f_1=[AND(=($2, 0), $1)])
- LogicalAggregate(group=[{0, 1}], $g=[GROUPING($0, $1)])
- LogicalProject(C=[>($5, 1000)], D=[<($5, 500)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalAggregate(group=[{}], EXPR$0_g0=[COUNT($0) FILTER $3],
EXPR$1_g0=[COUNT($1) FILTER $2])
+ LogicalProject(C=[$0], D=[$1], $g_0_f_0=[AND(=($2, 0), IS TRUE($0))],
$g_0_f_1=[AND(=($2, 0), IS TRUE($1))])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {}]], $g=[GROUPING($0,
$1)])
+ LogicalProject(C=[>($5, 1000)], D=[<($5, 500)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3626,7 +3709,7 @@ LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)],
EXPR$2=[COUNT(DISTINCT $2) FILTE
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], EXPR$1=[CAST($1):INTEGER NOT NULL], EXPR$2=[$2])
- LogicalAggregate(group=[{0}], EXPR$1=[MIN($2) FILTER $4], EXPR$2=[COUNT($1)
FILTER $3])
+ LogicalAggregate(group=[{0}], EXPR$1_g0=[MIN($2) FILTER $4],
EXPR$2_g0=[COUNT($1) FILTER $3])
LogicalProject(DEPTNO=[$0], SAL=[$1], EXPR$1=[$3], $g_0_f_2=[AND(=($4, 0),
IS TRUE($2))], $g_3=[=($4, 3)])
LogicalAggregate(group=[{0, 2, 3}], groups=[[{0, 2, 3}, {0}]],
EXPR$1=[SUM($1)], $g=[GROUPING($0, $2, $3)])
LogicalProject(DEPTNO=[$7], COMM=[$6], SAL=[$5], $f3=[>($5, 1000)])
@@ -3648,11 +3731,12 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)],
EXPR$1=[COUNT(DISTINCT $1) FILTER
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalAggregate(group=[{}], EXPR$0=[MIN($1) FILTER $3], EXPR$1=[COUNT($0)
FILTER $2])
- LogicalProject(SAL=[$0], EXPR$0=[$2], $g_0_f_1=[AND(=($3, 0), IS TRUE($1))],
$g_3=[=($3, 3)])
- LogicalAggregate(group=[{1, 2}], groups=[[{1, 2}, {}]], EXPR$0=[SUM($0)],
$g=[GROUPING($1, $2)])
- LogicalProject(COMM=[$6], SAL=[$5], $f2=[>($5, 1000)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalAggregate(group=[{}], EXPR$0_g0=[MIN($1) FILTER $3],
EXPR$1_g0=[COUNT($0) FILTER $2])
+ LogicalProject(SAL=[$0], EXPR$0=[$2], $g_0_f_1=[AND(=($3, 0), IS
TRUE($1))], $g_3=[=($3, 3)])
+ LogicalAggregate(group=[{1, 2}], groups=[[{1, 2}, {}]],
EXPR$0=[SUM($0)], $g=[GROUPING($1, $2)])
+ LogicalProject(COMM=[$6], SAL=[$5], $f2=[>($5, 1000)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 9afc119d27..3c8afd63ff 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -1838,14 +1838,14 @@ group by deptno;
select count(distinct deptno) as cd, count(*) as c
from "scott".emp
group by cube(deptno);
-+----+---+
-| CD | C |
-+----+---+
-| 1 | 3 |
-| 1 | 5 |
-| 1 | 6 |
-| 3 | 3 |
-+----+---+
++----+----+
+| CD | C |
++----+----+
+| 1 | 3 |
+| 1 | 5 |
+| 1 | 6 |
+| 3 | 14 |
++----+----+
(4 rows)
!ok
@@ -2823,7 +2823,7 @@ select count(distinct EMPNO), COUNT(SAL), MIN(SAL),
MAX(SAL) from "scott".emp;
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):BIGINT NOT NULL],
EXPR$0=[$t0], EXPR$1=[$t4], EXPR$2=[$t2], EXPR$3=[$t3])
- EnumerableAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $4],
EXPR$1=[MIN($1) FILTER $5], EXPR$2=[MIN($2) FILTER $5], EXPR$3=[MIN($3) FILTER
$5])
+ EnumerableAggregate(group=[{}], EXPR$0_g0=[COUNT($0) FILTER $4],
EXPR$1_g0=[MIN($1) FILTER $5], EXPR$2_g0=[MIN($2) FILTER $5],
EXPR$3_g0=[MIN($3) FILTER $5])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t4, $t5)],
expr#7=[1], expr#8=[=($t4, $t7)], proj#0..3=[{exprs}], $g_0=[$t6], $g_1=[$t8])
EnumerableAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT($5)],
EXPR$2=[MIN($5)], EXPR$3=[MAX($5)], $g=[GROUPING($0)])
EnumerableTableScan(table=[[scott, EMP]])
@@ -2841,7 +2841,7 @@ select count(distinct DEPTNO), COUNT(JOB), MIN(SAL),
MAX(SAL) from "scott".emp;
!ok
EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):BIGINT NOT NULL],
EXPR$0=[$t0], EXPR$1=[$t4], EXPR$2=[$t2], EXPR$3=[$t3])
- EnumerableAggregate(group=[{}], EXPR$0=[COUNT($0) FILTER $4],
EXPR$1=[MIN($1) FILTER $5], EXPR$2=[MIN($2) FILTER $5], EXPR$3=[MIN($3) FILTER
$5])
+ EnumerableAggregate(group=[{}], EXPR$0_g0=[COUNT($0) FILTER $4],
EXPR$1_g0=[MIN($1) FILTER $5], EXPR$2_g0=[MIN($2) FILTER $5],
EXPR$3_g0=[MIN($3) FILTER $5])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t4, $t5)],
expr#7=[1], expr#8=[=($t4, $t7)], proj#0..3=[{exprs}], $g_0=[$t6], $g_1=[$t8])
EnumerableAggregate(group=[{7}], groups=[[{7}, {}]], EXPR$1=[COUNT($2)],
EXPR$2=[MIN($5)], EXPR$3=[MAX($5)], $g=[GROUPING($7)])
EnumerableTableScan(table=[[scott, EMP]])
@@ -2865,7 +2865,7 @@ select MGR, count(distinct DEPTNO), COUNT(JOB), MIN(SAL),
MAX(SAL) from "scott".
!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):BIGINT NOT NULL],
proj#0..1=[{exprs}], EXPR$2=[$t5], EXPR$3=[$t3], EXPR$4=[$t4])
- EnumerableAggregate(group=[{0}], EXPR$1=[COUNT($1) FILTER $5],
EXPR$2=[MIN($2) FILTER $6], EXPR$3=[MIN($3) FILTER $6], EXPR$4=[MIN($4) FILTER
$6])
+ EnumerableAggregate(group=[{0}], EXPR$1_g0=[COUNT($1) FILTER $5],
EXPR$2_g0=[MIN($2) FILTER $6], EXPR$3_g0=[MIN($3) FILTER $6],
EXPR$4_g0=[MIN($4) FILTER $6])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5, $t6)],
expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7], $g_1=[$t9])
EnumerableAggregate(group=[{3, 7}], groups=[[{3, 7}, {3}]],
EXPR$2=[COUNT($2)], EXPR$3=[MIN($5)], EXPR$4=[MAX($5)], $g=[GROUPING($3, $7)])
EnumerableTableScan(table=[[scott, EMP]])
@@ -2888,7 +2888,7 @@ select MGR, count(distinct DEPTNO, JOB), MIN(SAL),
MAX(SAL) from "scott".emp gro
!ok
-EnumerableAggregate(group=[{1}], EXPR$1=[COUNT($2, $0) FILTER $5],
EXPR$2=[MIN($3) FILTER $6], EXPR$3=[MIN($4) FILTER $6])
+EnumerableAggregate(group=[{1}], EXPR$1_g0=[COUNT($2, $0) FILTER $5],
EXPR$2_g0=[MIN($3) FILTER $6], EXPR$3_g0=[MIN($4) FILTER $6])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5, $t6)],
expr#8=[5], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7], $g_5=[$t9])
EnumerableAggregate(group=[{2, 3, 7}], groups=[[{2, 3, 7}, {3}]],
EXPR$2=[MIN($5)], EXPR$3=[MAX($5)], $g=[GROUPING($2, $3, $7)])
EnumerableTableScan(table=[[scott, EMP]])
@@ -4187,4 +4187,115 @@ EnumerableCalc(expr#0..8=[{inputs}], DEPTNO=[$t0],
ENAME=[$t2], SAL_COL=[$t3], F
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-5465] Rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES produces an
incorrect plan when sql has distinct agg-call with rollup
+!use scott
+WITH t1 (id, c1) AS (
+ VALUES
+ ('1', 'A1'),
+ ('2', 'A2'),
+ ('3', 'A3'),
+ ('3', 'A3'),
+ ('3', 'A2'),
+ (NULL, 'A4')
+)
+SELECT id, COUNT(DISTINCT c1)
+FROM t1
+GROUP BY ROLLUP(id);
++----+--------+
+| ID | EXPR$1 |
++----+--------+
+| 1 | 1 |
+| 2 | 1 |
+| 3 | 2 |
+| | 1 |
+| | 4 |
++----+--------+
+(5 rows)
+
+!ok
+
+SELECT deptno, job, COUNT(DISTINCT ename)
+FROM "scott".emp
+GROUP BY ROLLUP(deptno, job);
++--------+-----------+--------+
+| DEPTNO | JOB | EXPR$2 |
++--------+-----------+--------+
+| 10 | CLERK | 1 |
+| 10 | MANAGER | 1 |
+| 10 | PRESIDENT | 1 |
+| 10 | | 3 |
+| 20 | ANALYST | 2 |
+| 20 | CLERK | 2 |
+| 20 | MANAGER | 1 |
+| 20 | | 5 |
+| 30 | CLERK | 1 |
+| 30 | MANAGER | 1 |
+| 30 | SALESMAN | 4 |
+| 30 | | 6 |
+| | | 14 |
++--------+-----------+--------+
+(13 rows)
+
+!ok
+
+SELECT deptno, COUNT(DISTINCT sal)
+FROM "scott".emp
+GROUP BY GROUPING SETS ((deptno), ());
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | 3 |
+| 20 | 4 |
+| 30 | 5 |
+| | 12 |
++--------+--------+
+(4 rows)
+
+!ok
+
+SELECT deptno, COUNT(DISTINCT sal), SUM(sal)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), ());
++--------+--------+----------+
+| DEPTNO | EXPR$1 | EXPR$2 |
++--------+--------+----------+
+| 10 | 3 | 8750.00 |
+| 20 | 4 | 10875.00 |
+| 30 | 5 | 9400.00 |
+| | 12 | 29025.00 |
++--------+--------+----------+
+(4 rows)
+
+!ok
+
+SELECT deptno, COUNT(DISTINCT sal), COUNT(sal)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), ());
++--------+--------+--------+
+| DEPTNO | EXPR$1 | EXPR$2 |
++--------+--------+--------+
+| 10 | 3 | 3 |
+| 20 | 4 | 5 |
+| 30 | 5 | 6 |
+| | 12 | 14 |
++--------+--------+--------+
+(4 rows)
+
+!ok
+
+SELECT deptno, COUNT(DISTINCT sal), SUM(DISTINCT sal), COUNT(*)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), ());
++--------+--------+----------+--------+
+| DEPTNO | EXPR$1 | EXPR$2 | EXPR$3 |
++--------+--------+----------+--------+
+| 10 | 3 | 8750.00 | 3 |
+| 20 | 4 | 7875.00 | 5 |
+| 30 | 5 | 8150.00 | 6 |
+| | 12 | 24775.00 | 14 |
++--------+--------+----------+--------+
+(4 rows)
+
+!ok
+
# End agg.iq
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 1dd2edf4df..ab9a52fa79 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -2814,7 +2814,7 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t10,
$t9)], expr#15=[1], expr
EnumerableAggregate(group=[{7}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT
NULL], expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
DEPTNO=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
- EnumerableAggregate(group=[{1}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($0) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
+ EnumerableAggregate(group=[{1}], c_g0=[MIN($2) FILTER $7],
d_g0=[MIN($3) FILTER $7], dd_g0=[COUNT($0) FILTER $6], m_g0=[MIN($4) FILTER
$7], trueLiteral_g0=[MIN(true, $5) FILTER $7])
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[2], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_2=[$t10])
EnumerableAggregate(group=[{6, 7}], groups=[[{6, 7}, {7}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($6, $7)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
@@ -2871,7 +2871,7 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t9,
$t8)], expr#15=[1], expr#
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT
NULL], expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT
NULL], DEPTNO0=[$t0], c=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6],
dd=[COUNT($1) FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4)
FILTER $6])
+ EnumerableAggregate(group=[{0}], c_g0=[MIN($2) FILTER $6],
dd_g0=[COUNT($1) FILTER $5], m_g0=[MIN($3) FILTER $6],
trueLiteral_g0=[MIN(true, $4) FILTER $6])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
EnumerableCalc(expr#0..2=[{inputs}],
expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
@@ -2922,7 +2922,7 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t9,
$t8)], expr#15=[1], expr#
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT
NULL], expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT
NULL], DEPTNO0=[$t0], c=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6],
dd=[COUNT($1) FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4)
FILTER $6])
+ EnumerableAggregate(group=[{0}], c_g0=[MIN($2) FILTER $6],
dd_g0=[COUNT($1) FILTER $5], m_g0=[MIN($3) FILTER $6],
trueLiteral_g0=[MIN(true, $4) FILTER $6])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
EnumerableCalc(expr#0..2=[{inputs}],
expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
@@ -2973,7 +2973,7 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t10,
$t9)], expr#15=[1], expr
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT
NULL], expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
SAL=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($1) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
+ EnumerableAggregate(group=[{0}], c_g0=[MIN($2) FILTER $7],
d_g0=[MIN($3) FILTER $7], dd_g0=[COUNT($1) FILTER $6], m_g0=[MIN($4) FILTER
$7], trueLiteral_g0=[MIN(true, $5) FILTER $7])
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_1=[$t10])
EnumerableAggregate(group=[{5, 6}], groups=[[{5, 6}, {5}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($5, $6)])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t5)], proj#0..7=[{exprs}], $condition=[$t8])
diff --git a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
index 39d1bb39bc..37ac09469b 100644
--- a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
+++ b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
@@ -122,7 +122,7 @@ private CalciteAssert.AssertQuery sql(String sql) {
final String plan = "PLAN="
+ "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):INTEGER NOT
NULL], expr#7=[CAST($t2):CHAR(1) NOT NULL], expr#8=[CAST($t3):CHAR(1) NOT
NULL], expr#9=[CAST($t4):BIGINT NOT NULL], SUM_X=[$t6], MIN_Y=[$t7],
MAX_Y=[$t8], CNT_Y=[$t9], CNT_DIST_Y=[$t5])\n"
- + " EnumerableAggregate(group=[{0}], SUM_X=[MIN($2) FILTER $7],
MIN_Y=[MIN($3) FILTER $7], MAX_Y=[MIN($4) FILTER $7], CNT_Y=[MIN($5) FILTER
$7], CNT_DIST_Y=[COUNT($1) FILTER $6])\n"
+ + " EnumerableAggregate(group=[{0}], SUM_X_g0=[MIN($2) FILTER $7],
MIN_Y_g0=[MIN($3) FILTER $7], MAX_Y_g0=[MIN($4) FILTER $7], CNT_Y_g0=[MIN($5)
FILTER $7], CNT_DIST_Y_g0=[COUNT($1) FILTER $6])\n"
+ " EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_1=[$t10])\n"
+ " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
SUM_X=[$SUM0($0)], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()],
$g=[GROUPING($0, $1)])\n"
+ " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b'
}, { 2, 'c' }, { 2, 'c' }]])\n";
@@ -143,7 +143,7 @@ private CalciteAssert.AssertQuery sql(String sql) {
final String plan = "PLAN="
+ "EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t3):BIGINT NOT
NULL], proj#0..2=[{exprs}], CNT_Y=[$t5], CNT_DIST_Y=[$t4])\n"
- + " EnumerableAggregate(group=[{}], SUM_X=[MIN($1) FILTER $6],
MIN_Y=[MIN($2) FILTER $6], MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER
$6], CNT_DIST_Y=[COUNT($0) FILTER $5])\n"
+ + " EnumerableAggregate(group=[{}], SUM_X_g0=[MIN($1) FILTER $6],
MIN_Y_g0=[MIN($2) FILTER $6], MAX_Y_g0=[MIN($3) FILTER $6], CNT_Y_g0=[MIN($4)
FILTER $6], CNT_DIST_Y_g0=[COUNT($0) FILTER $5])\n"
+ " EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t2,
$t6)], expr#8=[null:INTEGER], expr#9=[CASE($t7, $t8, $t1)], expr#10=[=($t5,
$t6)], expr#11=[1], expr#12=[=($t5, $t11)], Y=[$t0], SUM_X=[$t9], MIN_Y=[$t3],
MAX_Y=[$t4], CNT_Y=[$t2], $g_0=[$t10], $g_1=[$t12])\n"
+ " EnumerableAggregate(group=[{1}], groups=[[{1}, {}]],
SUM_X=[$SUM0($0)], agg#1=[COUNT()], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)],
$g=[GROUPING($1)])\n"
+ " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b'
}, { 2, 'c' }, { 2, 'c' }]])\n";
@@ -179,7 +179,7 @@ private CalciteAssert.AssertQuery sql(String sql) {
final String plan = "PLAN="
+ "EnumerableSort(sort0=[$0], dir0=[ASC])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1)
NOT NULL], expr#6=[CAST($t2):CHAR(1) NOT NULL], expr#7=[CAST($t3):BIGINT NOT
NULL], X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
- + " EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6],
MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1)
FILTER $5])\n"
+ + " EnumerableAggregate(group=[{0}], MIN_Y_g0=[MIN($2) FILTER $6],
MAX_Y_g0=[MIN($3) FILTER $6], CNT_Y_g0=[MIN($4) FILTER $6],
CNT_DIST_Y_g0=[COUNT($1) FILTER $5])\n"
+ " EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0],
expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}],
$g_0=[$t7], $g_1=[$t9])\n"
+ " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
+ " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1,
'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
@@ -201,7 +201,7 @@ private CalciteAssert.AssertQuery sql(String sql) {
final String plan = "PLAN="
+ "EnumerableSort(sort0=[$0], dir0=[DESC])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1)
NOT NULL], expr#6=[CAST($t2):CHAR(1) NOT NULL], expr#7=[CAST($t3):BIGINT NOT
NULL], X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
- + " EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6],
MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1)
FILTER $5])\n"
+ + " EnumerableAggregate(group=[{0}], MIN_Y_g0=[MIN($2) FILTER $6],
MAX_Y_g0=[MIN($3) FILTER $6], CNT_Y_g0=[MIN($4) FILTER $6],
CNT_DIST_Y_g0=[COUNT($1) FILTER $5])\n"
+ " EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0],
expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}],
$g_0=[$t7], $g_1=[$t9])\n"
+ " EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
+ " EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1,
'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
