This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 8668ee1d4e [CALCITE-6537] Add syntax to allow non-aggregated rows to
be used in GROUPING SETS
8668ee1d4e is described below
commit 8668ee1d4eacbd8e53d6fec83879abe649a00db8
Author: Yu Xu <[email protected]>
AuthorDate: Sat Aug 22 22:51:02 2026 +0800
[CALCITE-6537] Add syntax to allow non-aggregated rows to be used in
GROUPING SETS
---
core/src/main/codegen/templates/Parser.jj | 77 ++++++++-
.../calcite/sql/validate/SqlConformance.java | 26 +++
.../calcite/sql/validate/SqlConformanceEnum.java | 10 ++
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 119 ++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 78 +++++++++
core/src/test/resources/sql/agg.iq | 180 +++++++++++++++++++++
7 files changed, 491 insertions(+), 3 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 42e4fc791a..1105df6bcb 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -187,6 +187,34 @@ public class ${parser.class} extends SqlAbstractParserImpl
return rowValueStarCount > 0;
}
+ /**
+ * Whether {@code *} is currently allowed as a bare grouping element in
+ * GROUPING SETS / ROLLUP / CUBE. Set while parsing a grouping element
+ * when the {@code isGroupingSetsStarAllowed} conformance is on.
+ *
+ * <p>Note that the validator expands such a star only in the
+ * well-defined positions of the syntax: a complete grouping set, an
+ * element of a grouping set, or an argument of ROLLUP or CUBE (including
+ * grouping constructs nested within GROUPING SETS). A star nested inside
+ * other expressions, say {@code ABS(*)}, parses too, but the validator
+ * does not expand it, and validation fails because {@code *} is not a
+ * known column.
+ */
+ private int groupingStarCount;
+
+ private void pushGroupingStar() {
+ groupingStarCount++;
+ }
+
+ private void popGroupingStar() {
+ assert groupingStarCount > 0;
+ groupingStarCount--;
+ }
+
+ private boolean allowGroupingStar() {
+ return groupingStarCount > 0;
+ }
+
/**
* {@link SqlParserImplFactory} implementation for creating parser.
*/
@@ -2901,11 +2929,23 @@ SqlNodeList GroupBy() :
List<SqlNode> GroupingElementList() :
{
final List<SqlNode> list = new ArrayList<SqlNode>();
+ final boolean starAllowed =
+ this.conformance.isGroupingSetsStarAllowed();
}
{
+ {
+ if (starAllowed) {
+ pushGroupingStar();
+ }
+ }
AddGroupingElement(list)
( LOOKAHEAD(2) <COMMA> AddGroupingElement(list) )*
- { return list; }
+ {
+ if (starAllowed) {
+ popGroupingStar();
+ }
+ return list;
+ }
}
void AddGroupingElement(List<SqlNode> list) :
@@ -2913,6 +2953,8 @@ void AddGroupingElement(List<SqlNode> list) :
final List<SqlNode> subList;
final SqlNodeList nodes;
final Span s;
+ final boolean starAllowed =
+ this.conformance.isGroupingSetsStarAllowed();
}
{
LOOKAHEAD(2)
@@ -2922,14 +2964,30 @@ void AddGroupingElement(List<SqlNode> list) :
SqlStdOperatorTable.GROUPING_SETS.createCall(s.end(this),
subList));
}
| <ROLLUP> { s = span(); }
- <LPAREN> nodes = ExpressionCommaList(s, ExprContext.ACCEPT_SUB_QUERY)
+ <LPAREN> {
+ if (starAllowed) {
+ pushGroupingStar();
+ }
+ }
+ nodes = ExpressionCommaList(s, ExprContext.ACCEPT_SUB_QUERY)
<RPAREN> {
+ if (starAllowed) {
+ popGroupingStar();
+ }
list.add(
SqlStdOperatorTable.ROLLUP.createCall(s.end(this),
nodes.getList()));
}
| <CUBE> { s = span(); }
- <LPAREN> nodes = ExpressionCommaList(s, ExprContext.ACCEPT_SUB_QUERY)
+ <LPAREN> {
+ if (starAllowed) {
+ pushGroupingStar();
+ }
+ }
+ nodes = ExpressionCommaList(s, ExprContext.ACCEPT_SUB_QUERY)
<RPAREN> {
+ if (starAllowed) {
+ popGroupingStar();
+ }
list.add(
SqlStdOperatorTable.CUBE.createCall(s.end(this), nodes.getList()));
}
@@ -2937,6 +2995,10 @@ void AddGroupingElement(List<SqlNode> list) :
<LPAREN> { s = span(); } <RPAREN> {
list.add(new SqlNodeList(s.end(this)));
}
+| LOOKAHEAD({ allowGroupingStar() && getToken(1).kind == STAR })
+ <STAR> {
+ list.add(SqlIdentifier.star(getPos()));
+ }
| AddExpression(list, ExprContext.ACCEPT_SUB_QUERY)
}
@@ -4824,6 +4886,15 @@ SqlNode AtomicRowExpression() :
|
{ return starId; }
)
+ |
+ // Parses a bare "*" grouping element appearing inside a tuple or
+ // argument list of GROUPING SETS / ROLLUP / CUBE. (A star nested
+ // inside other expressions, say ABS(*), parses too, but the
+ // validator does not expand it and validation fails.)
+ LOOKAHEAD({ allowGroupingStar() && getToken(1).kind == STAR })
+ <STAR> {
+ return SqlIdentifier.star(getPos());
+ }
|
e = NewSpecification()
|
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 703dfd4a14..0a4041bbde 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -747,4 +747,30 @@ default boolean isColonFieldAccessAllowed() {
default boolean isCorrelatedAggregateAllowed() {
return false;
}
+
+ /**
+ * Whether {@code *} is allowed as a grouping element in
+ * {@code GROUPING SETS}, {@code ROLLUP} and {@code CUBE} sub-clauses of
+ * {@code GROUP BY}, to produce non-aggregated (detail) rows.
+ *
+ * <p>By analogy with {@code COUNT(*)}, a grouping set containing {@code *}
+ * groups by every input column, so no rows are merged (except identical
+ * duplicate rows) and each input row appears in the output.
+ *
+ * <p>The star may appear only in well-defined positions: as a complete
+ * grouping set ({@code GROUPING SETS (*)}), as an element of a grouping
+ * set ({@code GROUPING SETS ((deptno), (*))}), or as an argument of
+ * {@code ROLLUP} or {@code CUBE} ({@code ROLLUP (deptno, *)}), including
+ * grouping constructs nested within {@code GROUPING SETS}. It may not
+ * appear at the top level of {@code GROUP BY}, nor inside other
+ * expressions.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BABEL},
+ * {@link SqlConformanceEnum#LENIENT};
+ * false otherwise.
+ */
+ default boolean isGroupingSetsStarAllowed() {
+ return false;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 681835724f..072cdc0f68 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -572,4 +572,14 @@ public enum SqlConformanceEnum implements SqlConformance {
return false;
}
}
+
+ @Override public boolean isGroupingSetsStarAllowed() {
+ switch (this) {
+ case BABEL:
+ case LENIENT:
+ return true;
+ default:
+ return false;
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index 7bed86a410..94ed901bca 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -193,4 +193,8 @@ protected SqlDelegatingConformance(SqlConformance delegate)
{
@Override public boolean isCorrelatedAggregateAllowed() {
return delegate.isCorrelatedAggregateAllowed();
}
+
+ @Override public boolean isGroupingSetsStarAllowed() {
+ return delegate.isGroupingSetsStarAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 78c9d0d522..f6fd853d74 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -5658,6 +5658,7 @@ private static SqlNode measureToValue(SqlNode e) {
*/
protected void validateGroupClause(SqlSelect select) {
rewriteGroupByAll(select);
+ rewriteGroupingStar(select);
SqlNodeList groupList = select.getGroup();
if (groupList == null) {
return;
@@ -5759,6 +5760,124 @@ private void rewriteGroupByAll(SqlSelect select) {
select.setGroupBy(new SqlNodeList(keys, groupList.getParserPosition()));
}
+ /** If the conformance allows {@code *} as a grouping element, rewrites it
+ * into a {@code ROW} of every input column. A grouping set containing all
+ * input columns does not merge any rows (except identical duplicates),
+ * yielding the non-aggregated "detail" rows.
+ *
+ * <p>A star may appear only in the well-defined positions of this syntax:
+ * as a complete grouping set ({@code GROUPING SETS (*)}), as an element of
+ * a grouping set ({@code GROUPING SETS ((deptno), (*))}), or as an argument
+ * of ROLLUP or CUBE ({@code ROLLUP (deptno, *)}), including grouping
+ * constructs nested within GROUPING SETS. It may not appear at the top
+ * level of GROUP BY, nor inside other expressions.
+ *
+ * @see SqlConformance#isGroupingSetsStarAllowed() */
+ private void rewriteGroupingStar(SqlSelect select) {
+ if (!config.conformance().isGroupingSetsStarAllowed()) {
+ return;
+ }
+ final SqlNodeList groupList = select.getGroup();
+ if (groupList == null) {
+ return;
+ }
+ boolean changed = false;
+ final List<SqlNode> newItems = new ArrayList<>();
+ for (SqlNode groupItem : groupList) {
+ final SqlNode newItem = rewriteGroupingStarNode(groupItem, select);
+ if (newItem != groupItem) {
+ changed = true;
+ }
+ newItems.add(newItem);
+ }
+ if (changed) {
+ select.setGroupBy(new SqlNodeList(newItems,
groupList.getParserPosition()));
+ }
+ }
+
+ /** Recursively rewrites bare {@code *} grouping elements into a ROW of all
+ * input columns. Descends only into the constructs in which the grouping
+ * star syntax is defined: the operands of GROUPING SETS, ROLLUP and CUBE
+ * calls, the elements of the ROW tuples that make up a grouping set, and
+ * the grouping elements wrapped by GROUP BY DISTINCT. A star nested inside
+ * any other expression, say {@code GROUPING SETS (ABS(*))}, is not
+ * expanded; validation of that expression fails because {@code *} is not
+ * a known column. */
+ private SqlNode rewriteGroupingStarNode(SqlNode node, SqlSelect select) {
+ if (node instanceof SqlIdentifier) {
+ final SqlIdentifier id = (SqlIdentifier) node;
+ if (id.isStar() && id.names.size() == 1) {
+ return starToRow(select, id.getParserPosition());
+ }
+ return node;
+ }
+ if (node instanceof SqlNodeList) {
+ final SqlNodeList list = (SqlNodeList) node;
+ List<@Nullable SqlNode> newItems = null;
+ int i = 0;
+ for (SqlNode item : list) {
+ final SqlNode newItem = rewriteGroupingStarNode(item, select);
+ if (newItem != item) {
+ if (newItems == null) {
+ newItems = new ArrayList<@Nullable SqlNode>(list.getList());
+ }
+ newItems.set(i, newItem);
+ }
+ i++;
+ }
+ return newItems == null ? node
+ : new SqlNodeList(newItems, list.getParserPosition());
+ }
+ if (node instanceof SqlCall && isGroupingStarContainer((SqlCall) node)) {
+ final SqlCall call = (SqlCall) node;
+ final List<SqlNode> operands = call.getOperandList();
+ List<SqlNode> newOperands = null;
+ for (int i = 0; i < operands.size(); i++) {
+ final SqlNode operand = operands.get(i);
+ final SqlNode newOperand = rewriteGroupingStarNode(operand, select);
+ if (newOperand != operand) {
+ if (newOperands == null) {
+ newOperands = new ArrayList<>(operands);
+ }
+ newOperands.set(i, newOperand);
+ }
+ }
+ if (newOperands != null) {
+ return call.getOperator().createCall(
+ call.getParserPosition(), newOperands);
+ }
+ }
+ return node;
+ }
+
+ /** Returns whether a call may directly contain a bare {@code *} grouping
+ * element: a GROUPING SETS, ROLLUP or CUBE sub-clause, a ROW tuple within
+ * one of those clauses, or the wrapper that GROUP BY DISTINCT places
+ * around its grouping elements. */
+ private static boolean isGroupingStarContainer(SqlCall call) {
+ switch (call.getKind()) {
+ case GROUPING_SETS:
+ case ROLLUP:
+ case CUBE:
+ case ROW:
+ case GROUP_BY_DISTINCT:
+ return true;
+ default:
+ return false;
+ }
+ }
+
+ /** Builds a {@code ROW} of every input column of the FROM clause, expanding
+ * a bare grouping {@code *}. */
+ private SqlNode starToRow(SqlSelect select, SqlParserPos pos) {
+ final SqlIdentifier star = SqlIdentifier.star(pos);
+ final List<SqlNode> columns = expandStarForAllRewrite(select, star);
+ if (columns.isEmpty()) {
+ throw newValidationError(star, RESOURCE.selectStarRequiresFrom());
+ }
+ return SqlStdOperatorTable.ROW.createCall(pos, columns);
+ }
+
private void validateGroupItem(SqlValidatorScope groupScope,
@Nullable AggregatingSelectScope aggregatingScope,
SqlNode groupItem) {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 3915e5723c..1336a92029 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8302,6 +8302,84 @@ public boolean isBangEqualAllowed() {
+ " ()").ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6537";>[CALCITE-6537]
+ * Add syntax to allow non-aggregated rows to be used in GROUPING SETS</a>.
*/
+ @Test void testGroupingSetsStarConformance() {
+ // Under DEFAULT conformance, "*" is not a valid grouping element.
+ sql("select deptno, ename, sum(sal)\n"
+ + "from emp\n"
+ + "group by grouping sets ((deptno), (^*^))")
+ .fails("(?s).*Encountered.*\\*.*");
+
+ // Under LENIENT conformance, "*" expands to a ROW of every input column,
+ // so the non-aggregated SELECT columns are grouped in the detail set.
+ sql("select deptno, ename, sum(sal)\n"
+ + "from emp\n"
+ + "group by grouping sets ((deptno), (*))")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // ROLLUP and CUBE accept "*" too.
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by rollup(deptno, *)")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by cube(deptno, *)")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** More tests for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6537";>[CALCITE-6537]
+ * Add syntax to allow non-aggregated rows to be used in GROUPING SETS</a>.
+ *
+ * <p>Under LENIENT conformance, "*" is allowed as a complete grouping
+ * set, as an element of a grouping set, and as an argument of ROLLUP or
+ * CUBE, including grouping constructs nested within GROUPING SETS. It may
+ * not appear at the top level of GROUP BY, nor inside other expressions.
+ */
+ @Test void testGroupingSetsStarNested() {
+ // "*" as a complete grouping set
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by grouping sets (*)")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // "*" as an element of a grouping set
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by grouping sets ((deptno, *))")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // "*" as an argument of ROLLUP nested within GROUPING SETS
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by grouping sets ((deptno), rollup(ename, *))")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // "*" inside a tuple that is a ROLLUP argument
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by rollup((deptno, *))")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // "*" as a grouping set within GROUP BY DISTINCT
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by distinct grouping sets ((deptno), (*))")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+
+ // "*" at the top level of GROUP BY is not allowed
+ sql("select deptno, ename, sum(sal) from emp\n"
+ + "group by ^*^")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .fails("(?s).*Encountered.*\\*.*");
+
+ // "*" inside another expression is not expanded; validation fails
+ sql("select sum(sal) from emp\n"
+ + "group by grouping sets (abs(^*^))")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .fails("(?s).*Unknown identifier '\\*'.*");
+ sql("select sum(sal) from emp\n"
+ + "group by grouping sets (deptno + ^*^)")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .fails("(?s).*Unknown identifier '\\*'.*");
+ }
+
@Test void testRollup() {
// DEPTNO is not null in database, but rollup introduces nulls
sql("select deptno, count(*) as c, sum(sal) as s\n"
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 41f2436a25..55fd016f03 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -4750,3 +4750,183 @@ order by y;
!ok
# End agg.iq
+
+# [CALCITE-6537] Add syntax to allow non-aggregated rows to be used in
GROUPING SETS
+# non-aggregated (detail) rows. Requires LENIENT conformance.
+
+!use scott-lenient
+
+# GROUPING SETS ((deptno), (*)): department totals (ename NULL) plus one
+# detail row per employee.
+select deptno, ename, sum(sal) as sumsal
+from emp
+group by grouping sets ((deptno), (*))
+order by deptno, ename nulls first;
++--------+--------+----------+
+| DEPTNO | ENAME | SUMSAL |
++--------+--------+----------+
+| 10 | | 8750.00 |
+| 10 | CLARK | 2450.00 |
+| 10 | KING | 5000.00 |
+| 10 | MILLER | 1300.00 |
+| 20 | | 10875.00 |
+| 20 | ADAMS | 1100.00 |
+| 20 | FORD | 3000.00 |
+| 20 | JONES | 2975.00 |
+| 20 | SCOTT | 3000.00 |
+| 20 | SMITH | 800.00 |
+| 30 | | 9400.00 |
+| 30 | ALLEN | 1600.00 |
+| 30 | BLAKE | 2850.00 |
+| 30 | JAMES | 950.00 |
+| 30 | MARTIN | 1250.00 |
+| 30 | TURNER | 1500.00 |
+| 30 | WARD | 1250.00 |
++--------+--------+----------+
+(17 rows)
+
+!ok
+
+# ROLLUP (deptno, *): detail rows, department totals and a grand total.
+select deptno, ename, sum(sal) as sumsal
+from emp
+group by rollup (deptno, *)
+order by deptno nulls last, ename nulls first;
++--------+--------+----------+
+| DEPTNO | ENAME | SUMSAL |
++--------+--------+----------+
+| 10 | | 8750.00 |
+| 10 | CLARK | 2450.00 |
+| 10 | KING | 5000.00 |
+| 10 | MILLER | 1300.00 |
+| 20 | | 10875.00 |
+| 20 | ADAMS | 1100.00 |
+| 20 | FORD | 3000.00 |
+| 20 | JONES | 2975.00 |
+| 20 | SCOTT | 3000.00 |
+| 20 | SMITH | 800.00 |
+| 30 | | 9400.00 |
+| 30 | ALLEN | 1600.00 |
+| 30 | BLAKE | 2850.00 |
+| 30 | JAMES | 950.00 |
+| 30 | MARTIN | 1250.00 |
+| 30 | TURNER | 1500.00 |
+| 30 | WARD | 1250.00 |
+| | | 29025.00 |
++--------+--------+----------+
+(18 rows)
+
+!ok
+
+# GROUPING distinguishes the detail set (ename grouped, g=0) from the
+# department set (ename not grouped, g=1).
+select g, count(*) as cnt
+from (
+ select grouping(ename) as g
+ from emp
+ group by grouping sets ((deptno), (*)))
+group by g
+order by g;
++---+-----+
+| G | CNT |
++---+-----+
+| 0 | 14 |
+| 1 | 3 |
++---+-----+
+(2 rows)
+
+!ok
+
+# "*" as a complete grouping set returns one detail row per employee.
+select deptno, ename, sum(sal) as sumsal
+from emp
+group by grouping sets (*)
+order by deptno, ename;
++--------+--------+---------+
+| DEPTNO | ENAME | SUMSAL |
++--------+--------+---------+
+| 10 | CLARK | 2450.00 |
+| 10 | KING | 5000.00 |
+| 10 | MILLER | 1300.00 |
+| 20 | ADAMS | 1100.00 |
+| 20 | FORD | 3000.00 |
+| 20 | JONES | 2975.00 |
+| 20 | SCOTT | 3000.00 |
+| 20 | SMITH | 800.00 |
+| 30 | ALLEN | 1600.00 |
+| 30 | BLAKE | 2850.00 |
+| 30 | JAMES | 950.00 |
+| 30 | MARTIN | 1250.00 |
+| 30 | TURNER | 1500.00 |
+| 30 | WARD | 1250.00 |
++--------+--------+---------+
+(14 rows)
+
+!ok
+
+# "*" combined with the empty grouping set: detail rows, department
+# totals and a grand total.
+select deptno, ename, sum(sal) as sumsal
+from emp
+group by grouping sets ((deptno), (*), ())
+order by deptno nulls last, ename nulls first;
++--------+--------+----------+
+| DEPTNO | ENAME | SUMSAL |
++--------+--------+----------+
+| 10 | | 8750.00 |
+| 10 | CLARK | 2450.00 |
+| 10 | KING | 5000.00 |
+| 10 | MILLER | 1300.00 |
+| 20 | | 10875.00 |
+| 20 | ADAMS | 1100.00 |
+| 20 | FORD | 3000.00 |
+| 20 | JONES | 2975.00 |
+| 20 | SCOTT | 3000.00 |
+| 20 | SMITH | 800.00 |
+| 30 | | 9400.00 |
+| 30 | ALLEN | 1600.00 |
+| 30 | BLAKE | 2850.00 |
+| 30 | JAMES | 950.00 |
+| 30 | MARTIN | 1250.00 |
+| 30 | TURNER | 1500.00 |
+| 30 | WARD | 1250.00 |
+| | | 29025.00 |
++--------+--------+----------+
+(18 rows)
+
+!ok
+
+# "*" inside a tuple that is a ROLLUP argument: detail rows and a grand
+# total.
+select deptno, ename, sum(sal) as sumsal
+from emp
+group by rollup((deptno, *))
+order by deptno nulls last, ename nulls first;
++--------+--------+----------+
+| DEPTNO | ENAME | SUMSAL |
++--------+--------+----------+
+| 10 | CLARK | 2450.00 |
+| 10 | KING | 5000.00 |
+| 10 | MILLER | 1300.00 |
+| 20 | ADAMS | 1100.00 |
+| 20 | FORD | 3000.00 |
+| 20 | JONES | 2975.00 |
+| 20 | SCOTT | 3000.00 |
+| 20 | SMITH | 800.00 |
+| 30 | ALLEN | 1600.00 |
+| 30 | BLAKE | 2850.00 |
+| 30 | JAMES | 950.00 |
+| 30 | MARTIN | 1250.00 |
+| 30 | TURNER | 1500.00 |
+| 30 | WARD | 1250.00 |
+| | | 29025.00 |
++--------+--------+----------+
+(15 rows)
+
+!ok
+
+# Under DEFAULT conformance, * in GROUPING SETS is a parse error;
+# see SqlValidatorTest.testGroupingSetsStarConformance.
+# Nested forms and restrictions are in
+# SqlValidatorTest.testGroupingSetsStarNested.
+!use post
