This is an automated email from the ASF dual-hosted git repository.
jhyde 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 017c974 [CALCITE-4665] Allow Aggregate.groupKey to be a strict
superset of Aggregate.groupKeys (NobiGo)
017c974 is described below
commit 017c974e07dd48919e6890dea69320e6ffc406ef
Author: NobiGo <[email protected]>
AuthorDate: Mon Jul 12 21:02:58 2021 +0800
[CALCITE-4665] Allow Aggregate.groupKey to be a strict superset of
Aggregate.groupKeys (NobiGo)
Generate correct SQL (by filtering using GROUPING_ID) when
the group key is a superset of the union of the group keys.
Close apache/calcite#2458
---
.../org/apache/calcite/rel/core/Aggregate.java | 5 +
.../calcite/rel/rel2sql/RelToSqlConverter.java | 50 +++++-
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 17 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 20 ++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 172 +++++++++++++++++++++
.../org/apache/calcite/test/RelBuilderTest.java | 34 ++++
core/src/test/resources/sql/agg.iq | 52 +++++++
7 files changed, 336 insertions(+), 14 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
index 493cd8b..abb56ba 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Aggregate.java
@@ -124,6 +124,11 @@ public abstract class Aggregate extends SingleRel
implements Hintable {
* For a simple {@code GROUP BY}, {@code groupSets} is a singleton list
* containing {@code groupSet}.
*
+ * <p>It is allowed for {@code groupSet} to contain bits that are not in any
+ * of the {@code groupSets}, even this does not correspond to valid SQL. See
+ * discussion in
+ * {@link org.apache.calcite.tools.RelBuilder#groupKey(ImmutableBitSet,
Iterable)}.
+ *
* <p>If {@code GROUP BY} is not specified,
* or equivalently if {@code GROUP BY ()} is specified,
* {@code groupSet} will be the empty set,
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 4377e84..b0d980c 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -99,6 +99,7 @@ import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.ArrayDeque;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Deque;
@@ -108,6 +109,7 @@ import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.SortedSet;
+import java.util.TreeSet;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@@ -264,7 +266,7 @@ public class RelToSqlConverter extends SqlImplementor
if (fromPart.getKind() == SqlKind.SELECT) {
existsSqlSelect = (SqlSelect) fromPart;
existsSqlSelect.setSelectList(
- new SqlNodeList(ImmutableList.of(SqlLiteral.createExactNumeric("1",
POS)), POS));
+ new SqlNodeList(ImmutableList.of(ONE), POS));
if (existsSqlSelect.getWhere() != null) {
sqlCondition = SqlStdOperatorTable.AND.createCall(POS,
existsSqlSelect.getWhere(),
@@ -275,7 +277,7 @@ public class RelToSqlConverter extends SqlImplementor
existsSqlSelect =
new SqlSelect(POS, null,
new SqlNodeList(
- ImmutableList.of(SqlLiteral.createExactNumeric("1", POS)),
POS),
+ ImmutableList.of(ONE), POS),
fromPart, sqlCondition, null,
null, null, null, null, null, null);
}
@@ -415,7 +417,9 @@ public class RelToSqlConverter extends SqlImplementor
ImmutableSet.of(Clause.HAVING));
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
- builder.setHaving(builder.context.toSql(null, e.getCondition()));
+ x.asSelect().setHaving(
+ SqlUtil.andExpressions(x.asSelect().getHaving(),
+ builder.context.toSql(null, e.getCondition())));
return builder.result();
} else {
final Result x = visitInput(e, 0, Clause.WHERE);
@@ -496,10 +500,16 @@ public class RelToSqlConverter extends SqlImplementor
private Builder visitAggregate(Aggregate e, List<Integer> groupKeyList,
Clause... clauses) {
+ // groupSet contains at least one column that is not in any groupSet.
+ // Set of clauses that we expect the builder need to add extra
Clause.HAVING
+ // then can add Having filter condition in buildAggregate.
+ final Set<Clause> clauseSet = new TreeSet<>(Arrays.asList(clauses));
+ if (!e.getGroupSet().equals(ImmutableBitSet.union(e.getGroupSets()))) {
+ clauseSet.add(Clause.HAVING);
+ }
// "select a, b, sum(x) from ( ... ) group by a, b"
final boolean ignoreClauses = e.getInput() instanceof Project;
- final Result x = visitInput(e, 0, isAnon(), ignoreClauses,
- ImmutableSet.copyOf(clauses));
+ final Result x = visitInput(e, 0, isAnon(), ignoreClauses, clauseSet);
final Builder builder = x.builder(e);
final List<SqlNode> selectList = new ArrayList<>();
final List<SqlNode> groupByList =
@@ -548,6 +558,21 @@ public class RelToSqlConverter extends SqlImplementor
// as there is at least one aggregate function.
builder.setGroupBy(new SqlNodeList(groupByList, POS));
}
+
+ if (builder.clauses.contains(Clause.HAVING) && !e.getGroupSet()
+ .equals(ImmutableBitSet.union(e.getGroupSets()))) {
+ // groupSet contains at least one column that is not in any groupSets.
+ // To make such columns must appear in the output (their value will
+ // always be NULL), we generate an extra grouping set, then filter
+ // it out using a "HAVING GROUPING(groupSets) <> 0".
+ // We want to generate the
+ final SqlNodeList groupingList = new SqlNodeList(POS);
+ e.getGroupSet().forEach(g ->
+ groupingList.add(builder.context.field(g)));
+ builder.setHaving(
+ SqlStdOperatorTable.NOT_EQUALS.createCall(POS,
+ SqlStdOperatorTable.GROUPING.createCall(groupingList), ZERO));
+ }
return builder;
}
@@ -590,9 +615,19 @@ public class RelToSqlConverter extends SqlImplementor
SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO, groupKeys));
default:
case OTHER:
+ // Make sure that the group sets contains all bits.
+ final List<ImmutableBitSet> groupSets;
+ if (aggregate.getGroupSet()
+ .equals(ImmutableBitSet.union(aggregate.groupSets))) {
+ groupSets = aggregate.getGroupSets();
+ } else {
+ groupSets = new ArrayList<>(aggregate.getGroupSets().size() + 1);
+ groupSets.add(aggregate.getGroupSet());
+ groupSets.addAll(aggregate.getGroupSets());
+ }
return ImmutableList.of(
SqlStdOperatorTable.GROUPING_SETS.createCall(SqlParserPos.ZERO,
- aggregate.getGroupSets().stream()
+ groupSets.stream()
.map(groupSet ->
groupItem(groupKeys, groupSet, aggregate.getGroupSet()))
.collect(Collectors.toList())));
@@ -816,8 +851,7 @@ public class RelToSqlConverter extends SqlImplementor
// Use condition 1=0 since "where false" does not seem to be supported
// on some DB vendors.
return SqlStdOperatorTable.EQUALS.createCall(POS,
- ImmutableList.of(SqlLiteral.createExactNumeric("1", POS),
- SqlLiteral.createExactNumeric("0", POS)));
+ ImmutableList.of(ONE, ZERO));
}
/** Visits a Sort; called by {@link #dispatch} via reflection. */
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index be8ac7c..534dccc 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -65,6 +65,7 @@ import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlMatchRecognize;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOverOperator;
import org.apache.calcite.sql.SqlSelect;
@@ -136,6 +137,14 @@ public abstract class SqlImplementor {
// So we just quote it.
public static final SqlParserPos POS = SqlParserPos.QUOTED_ZERO;
+ /** SQL numeric literal {@code 0}. */
+ static final SqlNumericLiteral ZERO =
+ SqlNumericLiteral.createExactNumeric("0", POS);
+
+ /** SQL numeric literal {@code 1}. */
+ static final SqlNumericLiteral ONE =
+ SqlLiteral.createExactNumeric("1", POS);
+
public final SqlDialect dialect;
protected final Set<String> aliasSet = new LinkedHashSet<>();
@@ -982,8 +991,7 @@ public abstract class SqlImplementor {
// Rewrite "SUM0(x) OVER w" to "COALESCE(SUM(x) OVER w, 0)"
final SqlCall node =
createOverCall(SqlStdOperatorTable.SUM, operands, window,
isDistinct);
- return SqlStdOperatorTable.COALESCE.createCall(POS, node,
- SqlLiteral.createExactNumeric("0", POS));
+ return SqlStdOperatorTable.COALESCE.createCall(POS, node, ZERO);
}
SqlCall aggFunctionCall;
if (isDistinct) {
@@ -1129,8 +1137,7 @@ public abstract class SqlImplementor {
if (op instanceof SqlSumEmptyIsZeroAggFunction) {
final SqlNode node = toSql(SqlStdOperatorTable.SUM, distinct,
operandList, filterArg, collation);
- return SqlStdOperatorTable.COALESCE.createCall(POS, node,
- SqlLiteral.createExactNumeric("0", POS));
+ return SqlStdOperatorTable.COALESCE.createCall(POS, node, ZERO);
}
// Handle filter on dialects that do support FILTER by generating CASE.
@@ -1142,7 +1149,7 @@ public abstract class SqlImplementor {
final SqlNodeList whenList = SqlNodeList.of(field(filterArg));
final SqlNodeList thenList =
SqlNodeList.of(operandList.isEmpty()
- ? SqlLiteral.createExactNumeric("1", POS)
+ ? ONE
: operandList.get(0));
final SqlNode elseList = SqlLiteral.createNull(POS);
final SqlCall caseCall =
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index a5706fa..6ab8e23 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -934,7 +934,25 @@ public class RelBuilder {
*
* <p>This method of creating a group key does not allow you to group on new
* expressions, only column projections, but is efficient, especially when
you
- * are coming from an existing {@link Aggregate}. */
+ * are coming from an existing {@link Aggregate}.
+ *
+ * <p>It is possible for {@code groupSet} to be strict superset of all
+ * {@code groupSets}. For example, in the pseudo SQL
+ *
+ * <pre>{@code
+ * GROUP BY 0, 1, 2
+ * GROUPING SETS ((0, 1), 0)
+ * }</pre>
+ *
+ * <p>column 2 does not appear in either grouping set. This is not valid SQL.
+ * We can approximate in actual SQL by adding an extra grouping set and
+ * filtering out using {@code HAVING}, as follows:
+ *
+ * <pre>{@code
+ * GROUP BY GROUPING SETS ((0, 1, 2), (0, 1), 0)
+ * HAVING GROUPING_ID(0, 1, 2) <> 0
+ * }</pre>
+ */
public GroupKey groupKey(ImmutableBitSet groupSet,
Iterable<? extends ImmutableBitSet> groupSets) {
return groupKey_(groupSet, ImmutableList.copyOf(groupSets));
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 3539c31..3157e88 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
@@ -78,6 +78,7 @@ import org.apache.calcite.tools.Programs;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RuleSet;
import org.apache.calcite.tools.RuleSets;
+import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.TestUtil;
import org.apache.calcite.util.Util;
@@ -444,6 +445,177 @@ class RelToSqlConverterTest {
.withPostgresql().ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4665";>[CALCITE-4665]
+ * Allow Aggregate.groupSet to contain columns not in any of the
+ * groupSets</a>. Generate a redundant grouping set and a HAVING clause to
+ * filter it out. */
+ @Test void testGroupSuperset() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
ImmutableBitSet.of(0))),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .filter(b.equals(b.field("JOB"), b.literal("DEVELOP")))
+ .project(b.field("JOB"))
+ .build();
+ final String expectedSql = "SELECT \"JOB\"\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\", \"JOB\", COUNT(*) AS \"C\","
+ + " SUM(\"SAL\") AS \"S\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\")\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0"
+ + " AND \"JOB\" = 'DEVELOP') AS \"t\"";
+ relFn(relFn).ok(expectedSql);
+ }
+
+ /** As {@link #testGroupSuperset()},
+ * but HAVING has one standalone condition. */
+ @Test void testGroupSuperset2() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
ImmutableBitSet.of(0))),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .filter(
+ b.call(SqlStdOperatorTable.GREATER_THAN, b.field("C"),
+ b.literal(10)))
+ .filter(b.equals(b.field("JOB"), b.literal("DEVELOP")))
+ .project(b.field("JOB"))
+ .build();
+ final String expectedSql = "SELECT \"JOB\"\n"
+ + "FROM (SELECT *\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\", \"JOB\", COUNT(*) AS \"C\","
+ + " SUM(\"SAL\") AS \"S\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\")\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0"
+ + " AND \"C\" > 10) AS \"t\") "
+ + "AS \"t0\"\n"
+ + "WHERE \"JOB\" = 'DEVELOP'";
+ relFn(relFn).ok(expectedSql);
+ }
+
+ /** As {@link #testGroupSuperset()},
+ * but HAVING has one OR condition and the result can add appropriate
+ * parentheses. Also there is an empty grouping set. */
+ @Test void testGroupSuperset3() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
+ ImmutableBitSet.of(0), ImmutableBitSet.of())),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .filter(
+ b.call(SqlStdOperatorTable.OR,
+ b.call(SqlStdOperatorTable.GREATER_THAN, b.field("C"),
+ b.literal(10)),
+ b.call(SqlStdOperatorTable.LESS_THAN, b.field("S"),
+ b.literal(3000))))
+ .filter(b.equals(b.field("JOB"), b.literal("DEVELOP")))
+ .project(b.field("JOB"))
+ .build();
+ final String expectedSql = "SELECT \"JOB\"\n"
+ + "FROM (SELECT *\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\", \"JOB\", COUNT(*) AS \"C\","
+ + " SUM(\"SAL\") AS \"S\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\", ())\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0"
+ + " AND (\"C\" > 10 OR \"S\" < 3000)) AS \"t\") "
+ + "AS \"t0\"\n"
+ + "WHERE \"JOB\" = 'DEVELOP'";
+ relFn(relFn).ok(expectedSql);
+ }
+
+ /** As {@link #testGroupSuperset()}, but with no Filter between the Aggregate
+ * and the Project. */
+ @Test void testGroupSuperset4() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
+ ImmutableBitSet.of(0))),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .project(b.field("JOB"))
+ .build();
+ final String expectedSql = "SELECT \"JOB\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\")\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0";
+ relFn(relFn).ok(expectedSql);
+ }
+
+ /** As {@link #testGroupSuperset()}, but with no Filter between the Aggregate
+ * and the Sort. */
+ @Test void testGroupSuperset5() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
+ ImmutableBitSet.of(0))),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .sort(b.field("C"))
+ .build();
+ final String expectedSql = "SELECT \"EMPNO\", \"ENAME\", \"JOB\","
+ + " COUNT(*) AS \"C\", SUM(\"SAL\") AS \"S\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\")\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0\n"
+ + "ORDER BY COUNT(*)";
+ relFn(relFn).ok(expectedSql);
+ }
+
+ /** As {@link #testGroupSuperset()}, but with Filter condition and Where
condition. */
+ @Test void testGroupSuperset6() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .aggregate(
+ b.groupKey(ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
+ ImmutableBitSet.of(0), ImmutableBitSet.of())),
+ b.count(false, "C"),
+ b.sum(false, "S", b.field("SAL")))
+ .filter(
+ b.call(SqlStdOperatorTable.LESS_THAN,
+ b.call(SqlStdOperatorTable.GROUP_ID, b.field("EMPNO")),
b.literal(1)))
+ .filter(b.equals(b.field("JOB"), b.literal("DEVELOP")))
+ .project(b.field("JOB"))
+ .build();
+ final String expectedSql = "SELECT \"JOB\"\n"
+ + "FROM (SELECT *\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\", \"JOB\", COUNT(*) AS \"C\",
SUM(\"SAL\") AS \"S\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "GROUP BY GROUPING SETS((\"EMPNO\", \"ENAME\", \"JOB\"),"
+ + " (\"EMPNO\", \"ENAME\"), \"EMPNO\", ())\n"
+ + "HAVING GROUPING(\"EMPNO\", \"ENAME\", \"JOB\") <> 0"
+ + " AND GROUP_ID(\"EMPNO\") < 1) AS \"t\") "
+ + "AS \"t0\"\n"
+ + "WHERE \"JOB\" = 'DEVELOP'";
+ relFn(relFn).ok(expectedSql);
+ }
+
/** Tests GROUP BY ROLLUP of two columns. The SQL for MySQL has
* "GROUP BY ... ROLLUP" but no "ORDER BY". */
@Test void testSelectQueryWithGroupByRollup() {
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 2cd6f9d..b8c1a52 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -1692,6 +1692,40 @@ public class RelBuilderTest {
assertThat(root, hasTree(expected));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4665";>[CALCITE-4665]
+ * Allow Aggregate.groupSet to contain columns not in any of the
groupSets.</a>. */
+ @Test void testGroupingSetWithGroupKeysContainingUnusedColumn() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ RelNode root = builder.scan("EMP")
+ .aggregate(
+ builder.groupKey(
+ ImmutableBitSet.of(0, 1, 2),
+ (Iterable<ImmutableBitSet>)
+ ImmutableList.of(ImmutableBitSet.of(0, 1),
ImmutableBitSet.of(0))),
+ builder.count(false, "C"),
+ builder.sum(false, "S", builder.field("SAL")))
+ .filter(
+ builder.call(
+ SqlStdOperatorTable.GREATER_THAN,
+ builder.field("C"),
+ builder.literal(10)))
+ .filter(
+ builder.call(
+ SqlStdOperatorTable.EQUALS,
+ builder.field("JOB"),
+ builder.literal("DEVELOP")))
+ .project(builder.field("JOB")).build();
+ final String expected = ""
+ + "LogicalProject(JOB=[$2])\n"
+ + " LogicalFilter(condition=[=($2, 'DEVELOP')])\n"
+ + " LogicalFilter(condition=[>($3, 10)])\n"
+ + " LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 1}, {0}]],
C=[COUNT()], S=[SUM"
+ + "($5)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(root, hasTree(expected));
+ }
+
@Test void testAggregateGrouping() {
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 2ae05af..0401e2a 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3135,4 +3135,56 @@ group by grouping sets (deptno, ename);
!ok
+# [CALCITE-4665] Allow Aggregate.groupKey to be a strict superset of
+# Aggregate.groupKeys
+# Use a condition on grouping_id to filter out the superset grouping sets.
+select ename, deptno, gender, grouping(ename) as g_e,
+ grouping(deptno) as g_d, grouping(gender) as g_g
+from emp
+where gender = 'M'
+group by grouping sets (ename, deptno, (ename, deptno),
+ (ename, deptno, gender))
+having grouping_id(ename, deptno, gender) <> 0
+order by ename, deptno;
++-------+--------+--------+-----+-----+-----+
+| ENAME | DEPTNO | GENDER | G_E | G_D | G_G |
++-------+--------+--------+-----+-----+-----+
+| Adam | 50 | | 0 | 0 | 1 |
+| Adam | | | 0 | 1 | 1 |
+| Bob | 10 | | 0 | 0 | 1 |
+| Bob | | | 0 | 1 | 1 |
+| Eric | 20 | | 0 | 0 | 1 |
+| Eric | | | 0 | 1 | 1 |
+| | 10 | | 1 | 0 | 1 |
+| | 20 | | 1 | 0 | 1 |
+| | 50 | | 1 | 0 | 1 |
++-------+--------+--------+-----+-----+-----+
+(9 rows)
+
+!ok
+
+# just a comparison about the above sql
+select ename, deptno, grouping(ename) as g_e,
+ grouping(deptno) as g_d
+from emp
+where gender = 'M'
+group by grouping sets (ename, deptno, (ename, deptno))
+order by ename, deptno;
++-------+--------+-----+-----+
+| ENAME | DEPTNO | G_E | G_D |
++-------+--------+-----+-----+
+| Adam | 50 | 0 | 0 |
+| Adam | | 0 | 1 |
+| Bob | 10 | 0 | 0 |
+| Bob | | 0 | 1 |
+| Eric | 20 | 0 | 0 |
+| Eric | | 0 | 1 |
+| | 10 | 1 | 0 |
+| | 20 | 1 | 0 |
+| | 50 | 1 | 0 |
++-------+--------+-----+-----+
+(9 rows)
+
+!ok
+
# End agg.iq
