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 e15c3954b6 [CALCITE-6036] Support WITHIN GROUP(ORDER BY x) OVER
(PARTITION BY y)
e15c3954b6 is described below
commit e15c3954b643b054f3fb8a6b485af12f9bff25ed
Author: Yu Xu <[email protected]>
AuthorDate: Sun Jul 5 07:19:29 2026 +0800
[CALCITE-6036] Support WITHIN GROUP(ORDER BY x) OVER (PARTITION BY y)
---
babel/src/test/resources/sql/within-group-over.iq | 93 ++++++++++++++++++++++
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 36 ++++++++-
.../org/apache/calcite/sql/SqlOverOperator.java | 26 +++++-
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 18 +++++
.../calcite/sql/validate/SqlConformanceEnum.java | 9 +++
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../apache/calcite/sql/validate/SqlValidator.java | 23 +++++-
.../calcite/sql/validate/SqlValidatorImpl.java | 13 ++-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 26 +++++-
.../java/org/apache/calcite/tools/RelBuilder.java | 9 +++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 86 ++++++++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 27 +++++++
site/_docs/reference.md | 22 +++++
14 files changed, 384 insertions(+), 12 deletions(-)
diff --git a/babel/src/test/resources/sql/within-group-over.iq
b/babel/src/test/resources/sql/within-group-over.iq
new file mode 100644
index 0000000000..82df38fcd8
--- /dev/null
+++ b/babel/src/test/resources/sql/within-group-over.iq
@@ -0,0 +1,93 @@
+# within-group-over.iq - WITHIN GROUP (ORDER BY) OVER (PARTITION BY)
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+# Tests for the non-standard (Oracle) syntax that lets an aggregate function
+# with a "WITHIN GROUP (ORDER BY ...)" sort key also carry an "OVER (...)"
+# clause, so that it behaves as an analytic (window) function. The WITHIN GROUP
+# order key orders the aggregate's input but does not restrict the window
frame:
+# the aggregate is computed over the whole partition and broadcast to every row
+# (matching Oracle). This syntax is enabled by the BABEL conformance
+# (SqlConformance.allowWithinGroupOverAggregate).
+#
+!use scott-babel
+!set outputformat mysql
+
+# The following 3 tests are related to this issue.
+# Results were validated on Oracle.
+
+# LISTAGG as an analytic function, partitioned by deptno. The result is the
same
+# for every row of the partition (broadcast), which distinguishes the analytic
+# form from a per-row accumulating window.
+select deptno,
+ listagg(ename, ',') within group (order by ename)
+ over (partition by deptno) as names
+from emp
+where deptno in (10, 20)
+order by deptno, ename;
++--------+------------------------------+
+| DEPTNO | NAMES |
++--------+------------------------------+
+| 10 | CLARK,KING,MILLER |
+| 10 | CLARK,KING,MILLER |
+| 10 | CLARK,KING,MILLER |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
++--------+------------------------------+
+(8 rows)
+
+!ok
+
+# The WITHIN GROUP order key controls the concatenation order (here
descending),
+# independently of the OVER partition.
+select deptno,
+ listagg(ename, ',') within group (order by ename desc)
+ over (partition by deptno) as names
+from emp
+where deptno = 10
+order by ename;
++--------+-------------------+
+| DEPTNO | NAMES |
++--------+-------------------+
+| 10 | MILLER,KING,CLARK |
+| 10 | MILLER,KING,CLARK |
+| 10 | MILLER,KING,CLARK |
++--------+-------------------+
+(3 rows)
+
+!ok
+
+# Contrast: the aggregate (GROUP BY) form collapses each group to a single row.
+select deptno,
+ listagg(ename, ',') within group (order by ename) as names
+from emp
+where deptno in (10, 20)
+group by deptno
+order by deptno;
++--------+------------------------------+
+| DEPTNO | NAMES |
++--------+------------------------------+
+| 10 | CLARK,KING,MILLER |
+| 20 | ADAMS,FORD,JONES,SCOTT,SMITH |
++--------+------------------------------+
+(2 rows)
+
+!ok
+
+# End within-group-over.iq
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 403d7f6e2f..d8c02a56f3 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
@@ -105,6 +105,7 @@
import org.apache.calcite.util.DateString;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.NlsString;
+import org.apache.calcite.util.Optionality;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.RangeSets;
import org.apache.calcite.util.Sarg;
@@ -1119,6 +1120,23 @@ private SqlCall toSql(@Nullable RexProgram program,
RexOver rexOver) {
for (RexFieldCollation rfc : rexWindow.orderKeys) {
addOrderItem(orderNodes, program, rfc);
}
+
+ SqlAggFunction sqlAggregateFunction = rexOver.getAggOperator();
+
+ // Inverse distribution functions such as PERCENTILE_CONT/DISC take their
+ // sort key from a "WITHIN GROUP (ORDER BY ...)" clause rather than the
+ // window's ORDER BY, as in
+ // "PERCENTILE_CONT(x) WITHIN GROUP (ORDER BY y) OVER (PARTITION BY z)".
+ // Route the window's order keys into a WITHIN GROUP wrapper and leave
the
+ // OVER clause with only the partition.
+ final SqlNodeList groupOrderList;
+ if (sqlAggregateFunction.requiresGroupOrder() == Optionality.MANDATORY
+ && !orderNodes.isEmpty()) {
+ groupOrderList = new SqlNodeList(orderNodes, POS);
+ orderNodes = Expressions.list();
+ } else {
+ groupOrderList = null;
+ }
final SqlNodeList orderList =
new SqlNodeList(orderNodes, POS);
@@ -1132,8 +1150,6 @@ private SqlCall toSql(@Nullable RexProgram program,
RexOver rexOver) {
// "disallow partial" and set the allowPartial = false.
final SqlLiteral allowPartial = null;
- SqlAggFunction sqlAggregateFunction = rexOver.getAggOperator();
-
SqlNode lowerBound = null;
SqlNode upperBound = null;
SqlLiteral exclude = toSql(rexWindow.getExclude());
@@ -1149,15 +1165,22 @@ private SqlCall toSql(@Nullable RexProgram program,
RexOver rexOver) {
final List<SqlNode> nodeList = toSql(program, rexOver.getOperands());
return createOverCall(sqlAggregateFunction, nodeList, sqlWindow,
- rexOver.isDistinct(), rexOver.ignoreNulls());
+ rexOver.isDistinct(), rexOver.ignoreNulls(), groupOrderList);
}
private static SqlCall createOverCall(SqlAggFunction op, List<SqlNode>
operands,
SqlWindow window, boolean isDistinct, boolean ignoreNulls) {
+ return createOverCall(op, operands, window, isDistinct, ignoreNulls,
null);
+ }
+
+ private static SqlCall createOverCall(SqlAggFunction op, List<SqlNode>
operands,
+ SqlWindow window, boolean isDistinct, boolean ignoreNulls,
+ @Nullable SqlNodeList groupOrderList) {
if (op instanceof SqlSumEmptyIsZeroAggFunction) {
// Rewrite "SUM0(x) OVER w" to "COALESCE(SUM(x) OVER w, 0)"
final SqlCall node =
- createOverCall(SqlStdOperatorTable.SUM, operands, window,
isDistinct, ignoreNulls);
+ createOverCall(SqlStdOperatorTable.SUM, operands, window,
isDistinct, ignoreNulls,
+ groupOrderList);
return SqlStdOperatorTable.COALESCE.createCall(POS, node, ZERO);
}
SqlCall aggFunctionCall;
@@ -1171,6 +1194,11 @@ private static SqlCall createOverCall(SqlAggFunction op,
List<SqlNode> operands,
aggFunctionCall =
SqlStdOperatorTable.IGNORE_NULLS.createCall(null, POS,
aggFunctionCall);
}
+ if (groupOrderList != null && !groupOrderList.isEmpty()) {
+ aggFunctionCall =
+ SqlStdOperatorTable.WITHIN_GROUP.createCall(POS, aggFunctionCall,
+ groupOrderList);
+ }
return SqlStdOperatorTable.OVER.createCall(POS, aggFunctionCall,
window);
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
index b1ed13808d..1ec62f3309 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
@@ -78,6 +78,19 @@ public SqlOverOperator() {
default:
break;
}
+ // Support "agg WITHIN GROUP (ORDER BY ...) OVER (...)" for inverse
+ // distribution functions such as PERCENTILE_CONT/PERCENTILE_DISC. The
+ // WITHIN GROUP wrapper carries the sort key, and the underlying operand
+ // is the actual aggregate. This is non-standard (Oracle) syntax, gated by
+ // conformance; otherwise the WITHIN GROUP call is not an aggregator and
the
+ // overNonAggregate error below fires.
+ SqlNodeList groupOrderList = null;
+ if (aggCall.getKind() == SqlKind.WITHIN_GROUP
+ && validator.config().conformance().allowWithinGroupOverAggregate()) {
+ validator.validateCall(aggCall, scope);
+ groupOrderList = aggCall.operand(1);
+ aggCall = aggCall.operand(0);
+ }
if (!aggCall.getOperator().isAggregator()) {
throw validator.newValidationError(aggCall, RESOURCE.overNonAggregate());
}
@@ -87,7 +100,7 @@ public SqlOverOperator() {
throw validator.newValidationError(aggCall, RESOURCE.overNonAggregate());
}
final SqlNode window = call.operand(1);
- validator.validateWindow(window, scope, aggCall);
+ validator.validateWindow(window, scope, aggCall, groupOrderList);
}
@Override public RelDataType deriveType(
@@ -113,6 +126,17 @@ public SqlOverOperator() {
SqlWindow w = validator.resolveWindow(window, scope);
SqlCall aggCall = (SqlCall) agg;
+ // "agg WITHIN GROUP (ORDER BY ...) OVER (...)": the WITHIN GROUP wrapper
+ // has already derived the correct return type (e.g. the collation column
+ // type for PERCENTILE_CONT/DISC via SqlWithinGroupOperator.deriveType), so
+ // reuse it rather than re-inferring from the bare aggregate call, which
+ // would fail because the sort key is not available to the aggregate alone.
+ if (aggCall.getKind() == SqlKind.WITHIN_GROUP) {
+ RelDataType ret = validator.deriveType(scope, aggCall);
+ validator.setValidatedNodeType(call, ret);
+ validator.setValidatedNodeType(agg, ret);
+ return ret;
+ }
// Unwrap FILTER, RESPECT_NULLS, or IGNORE_NULLS to get the actual
aggregate call
while (aggCall != null
&& (aggCall.getKind() == SqlKind.FILTER
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 9ef7559c53..84cf77e3c9 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -33,6 +33,10 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
return SqlConformanceEnum.DEFAULT.allowCharLiteralAlias();
}
+ @Override public boolean allowWithinGroupOverAggregate() {
+ return SqlConformanceEnum.DEFAULT.allowWithinGroupOverAggregate();
+ }
+
@Override public boolean isSupportedDualTable() {
return SqlConformanceEnum.DEFAULT.isSupportedDualTable();
}
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 3ccc860a0d..db0dd693a9 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
@@ -89,6 +89,24 @@ public interface SqlConformance {
*/
boolean allowCharLiteralAlias();
+ /**
+ * Whether to allow an inverse distribution function such as
+ * {@code PERCENTILE_CONT} or {@code PERCENTILE_DISC} to combine a
+ * {@code WITHIN GROUP (ORDER BY ...)} clause with an {@code OVER} clause, so
+ * that it may be used as an analytic (window) function. For example,
+ *
+ * <blockquote><pre>
+ * PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)
+ * OVER (PARTITION BY y)</pre></blockquote>
+ *
+ * <p>This is non-standard SQL supported by Oracle.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BABEL};
+ * false otherwise.
+ */
+ boolean allowWithinGroupOverAggregate();
+
/**
* Whether to allow aliases from the {@code SELECT} clause to be used as
* column names in the {@code GROUP BY} clause.
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 fd083560e0..4ed6cb93c2 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
@@ -103,6 +103,15 @@ public enum SqlConformanceEnum implements SqlConformance {
}
}
+ @Override public boolean allowWithinGroupOverAggregate() {
+ switch (this) {
+ case BABEL:
+ return true;
+ default:
+ return false;
+ }
+ }
+
@Override public boolean isSupportedDualTable() {
switch (this) {
case MYSQL_5:
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 daac4b5c41..e142893891 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
@@ -39,6 +39,10 @@ protected SqlDelegatingConformance(SqlConformance delegate) {
return delegate.allowCharLiteralAlias();
}
+ @Override public boolean allowWithinGroupOverAggregate() {
+ return delegate.allowWithinGroupOverAggregate();
+ }
+
@Override public boolean isSupportedDualTable() {
return delegate.isSupportedDualTable();
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
index 813247376d..94b90c6789 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
@@ -290,10 +290,31 @@ void validateQuery(SqlNode node, SqlValidatorScope scope,
* @param call the SqlNode if a function call if the window is attached
* to one.
*/
+ default void validateWindow(
+ SqlNode windowOrId,
+ SqlValidatorScope scope,
+ @Nullable SqlCall call) {
+ validateWindow(windowOrId, scope, call, null);
+ }
+
+ /**
+ * Validates a window clause where the windowed function carries an inline
+ * {@code WITHIN GROUP (ORDER BY ...)} sort key, as in
+ * {@code PERCENTILE_CONT(x) WITHIN GROUP (ORDER BY y) OVER (PARTITION BY
z)}.
+ *
+ * @param windowOrId SqlNode that can be either SqlWindow with all the
+ * components of a window spec or a SqlIdentifier with
the
+ * name of a window spec.
+ * @param scope Naming scope
+ * @param call the aggregate function call the window is attached
to.
+ * @param groupOrderList the {@code WITHIN GROUP} order list carried by the
+ * aggregate, or null if there is none.
+ */
void validateWindow(
SqlNode windowOrId,
SqlValidatorScope scope,
- @Nullable SqlCall call);
+ @Nullable SqlCall call,
+ @Nullable SqlNodeList groupOrderList);
/** Returns whether the validator is currently validating within a window
* expression. */
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 2897202926..d8fd81a827 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
@@ -6604,7 +6604,8 @@ public void setOriginal(SqlNode expr, SqlNode original) {
@Override public void validateWindow(
SqlNode windowOrId,
SqlValidatorScope scope,
- @Nullable SqlCall call) {
+ @Nullable SqlCall call,
+ @Nullable SqlNodeList groupOrderList) {
// Enable nested aggregates with window aggregates (OVER operator)
inWindow = true;
@@ -6627,9 +6628,15 @@ public void setOriginal(SqlNode expr, SqlNode original) {
targetWindow.setWindowCall(call);
targetWindow.validate(this, scope);
targetWindow.setWindowCall(null);
- call.validate(this, scope);
+ if (groupOrderList == null) {
+ // A bare "PERCENTILE_CONT(x) WITHIN GROUP (ORDER BY y)" call has already
+ // been validated by SqlWithinGroupOperator, so re-validating the naked
+ // aggregate here would fail (it needs the WITHIN GROUP sort key to
+ // derive its type). Only validate when there is no group order list.
+ call.validate(this, scope);
+ }
- validateAggregateParams(call, null, null, null, scope);
+ validateAggregateParams(call, null, null, groupOrderList, scope);
// Disable nested aggregates post validation
inWindow = false;
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 240b8bd02b..f8b661c53f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2527,6 +2527,15 @@ private RexNode convertOver(Blackboard bb, SqlNode node)
{
default:
break;
}
+ // "agg WITHIN GROUP (ORDER BY ...) OVER (...)": the WITHIN GROUP sort key
+ // (used by inverse distribution functions such as PERCENTILE_CONT/DISC) is
+ // carried as the window's ORDER BY. Oracle forbids ORDER BY inside the
OVER
+ // clause for these functions, so the window's own order list is empty
here.
+ @Nullable SqlNodeList groupOrderList = null;
+ if (aggCall.getKind() == SqlKind.WITHIN_GROUP) {
+ groupOrderList = aggCall.operand(1);
+ aggCall = aggCall.operand(0);
+ }
if (filter != null) {
final SqlOperator op = aggCall.getOperator();
if (op instanceof SqlAggFunction
@@ -2551,9 +2560,20 @@ private RexNode convertOver(Blackboard bb, SqlNode node)
{
SqlNode sqlLowerBound = window.getLowerBound();
SqlNode sqlUpperBound = window.getUpperBound();
boolean rows = window.isRows();
- SqlNodeList orderList = window.getOrderList();
-
- if (!aggCall.getOperator().allowsFraming()) {
+ // For "agg WITHIN GROUP (ORDER BY ...) OVER (...)", the sort key comes
from
+ // the WITHIN GROUP clause rather than the window's own (empty) ORDER BY.
+ SqlNodeList orderList =
+ groupOrderList != null ? groupOrderList : window.getOrderList();
+
+ if (groupOrderList != null) {
+ // For "agg WITHIN GROUP (ORDER BY ...) OVER (...)", the sort key orders
+ // the aggregate's input but does not restrict the window frame: the
+ // aggregate is computed over the whole partition and broadcast to every
+ // row (matching Oracle). Force a full-partition frame so that framing
+ // aggregates such as LISTAGG do not accumulate row by row.
+ sqlLowerBound = SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO);
+ sqlUpperBound = SqlWindow.createUnboundedFollowing(SqlParserPos.ZERO);
+ } else if (!aggCall.getOperator().allowsFraming()) {
// If the operator does not allow framing, bracketing is implicitly
// everything up to the current row.
sqlLowerBound = SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO);
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 54d662a3a1..36d56a9f04 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -5063,6 +5063,15 @@ private OverCall
orderBy_(ImmutableList<RexFieldCollation> sortKeys) {
@Override public boolean hasEmptyGroup() {
return !SqlWindow.isAlwaysNonEmpty(lowerBound, upperBound);
}
+
+ @Override public RelDataType getCollationType() {
+ // Inverse distribution functions such as PERCENTILE_CONT/DISC
+ // used as analytic functions ("... WITHIN GROUP (ORDER BY x)
+ // OVER (...)") derive their return type from the sort key.
+ checkArgument(!sortKeys.isEmpty(),
+ "collation type requested but no sort key present");
+ return sortKeys.get(0).left.getType();
+ }
};
final RelDataType type = op.inferReturnType(bind);
final RexNode over = getRexBuilder()
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 1d3dda5af6..95bbe98f6a 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
@@ -8275,6 +8275,92 @@ private void checkLiteral2(String expression, String
expected) {
sql(query).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6036";>[CALCITE-6036]
+ * Support WITHIN GROUP (ORDER BY x) OVER (PARTITION BY y)</a>. Checks that
an
+ * inverse distribution function used as an analytic function is unparsed
with
+ * the sort key inside a {@code WITHIN GROUP} clause and the partition inside
+ * the {@code OVER} clause. */
+ @Test void testWithinGroupOver() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .project(
+ b.aggregateCall(SqlStdOperatorTable.PERCENTILE_CONT,
b.literal(0.5))
+ .over()
+ .partitionBy(b.field("DEPTNO"))
+ .orderBy(b.field("SAL"))
+ .rowsUnbounded()
+ .allowPartial(true)
+ .nullWhenCountZero(false)
+ .as("c"))
+ .build();
+ final String expected = "SELECT PERCENTILE_CONT(5E-1) "
+ + "WITHIN GROUP (ORDER BY \"SAL\") "
+ + "OVER (PARTITION BY \"DEPTNO\") AS \"c\"\n"
+ + "FROM \"scott\".\"EMP\"";
+ relFn(relFn).ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6036";>[CALCITE-6036]
+ * Support WITHIN GROUP (ORDER BY x) OVER (PARTITION BY y)</a>. Checks that
+ * expressions (rather than plain column references) under both the WITHIN
+ * GROUP order key and the OVER partition key are unparsed correctly. */
+ @Test void testWithinGroupOverWithExpressions() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .project(
+ b.aggregateCall(SqlStdOperatorTable.PERCENTILE_CONT,
b.literal(0.5))
+ .over()
+ .partitionBy(
+ b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
+ b.literal(1)))
+ .orderBy(
+ b.call(SqlStdOperatorTable.MULTIPLY, b.field("SAL"),
+ b.literal(2)))
+ .rowsUnbounded()
+ .allowPartial(true)
+ .nullWhenCountZero(false)
+ .as("c"))
+ .build();
+ final String expected = "SELECT PERCENTILE_CONT(5E-1) "
+ + "WITHIN GROUP (ORDER BY \"SAL\" * 2) "
+ + "OVER (PARTITION BY \"DEPTNO\" + 1) AS \"c\"\n"
+ + "FROM \"scott\".\"EMP\"";
+ relFn(relFn).ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6036";>[CALCITE-6036]
+ * Support WITHIN GROUP (ORDER BY x) OVER (PARTITION BY y)</a>. Checks
+ * unparsing with multiple partition and sort expressions, including a
+ * descending sort key. */
+ @Test void testWithinGroupOverWithMultipleExpressions() {
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .project(
+ b.aggregateCall(SqlStdOperatorTable.PERCENTILE_DISC,
b.literal(0.5))
+ .over()
+ .partitionBy(
+ b.field("DEPTNO"),
+ b.call(SqlStdOperatorTable.PLUS, b.field("MGR"),
+ b.literal(1)))
+ .orderBy(
+ b.desc(
+ b.call(SqlStdOperatorTable.MINUS, b.field("SAL"),
+ b.field("COMM"))))
+ .rowsUnbounded()
+ .allowPartial(true)
+ .nullWhenCountZero(false)
+ .as("c"))
+ .build();
+ final String expected = "SELECT PERCENTILE_DISC(5E-1) "
+ + "WITHIN GROUP (ORDER BY \"SAL\" - \"COMM\" DESC) "
+ + "OVER (PARTITION BY \"DEPTNO\", \"MGR\" + 1) AS \"c\"\n"
+ + "FROM \"scott\".\"EMP\"";
+ relFn(relFn).ok(expected);
+ }
+
@Test void testJsonValueExpressionOperator() {
String query = "select \"product_name\" format json, "
+ "\"product_name\" format json encoding utf8, "
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 123dbe01d0..094e0447b1 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8549,6 +8549,33 @@ void testGroupExpressionEquivalenceParams() {
.type("RecordType(INTEGER NOT NULL C, INTEGER NOT NULL D) NOT NULL");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6036";>[CALCITE-6036]
+ * Support WITHIN GROUP (ORDER BY x) OVER (PARTITION BY y)</a>. Combining a
+ * WITHIN GROUP clause with an OVER clause is non-standard (Oracle) syntax
that
+ * is only allowed under a conformance that enables it, such as BABEL. */
+ @Test void testPercentileWithinGroupOver() {
+ final String sql = "select\n"
+ + " percentile_cont(0.25) within group (order by sal)\n"
+ + " over (partition by deptno) as c\n"
+ + "from emp";
+ // Enabled under BABEL conformance.
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BABEL)
+ .type("RecordType(INTEGER NOT NULL C) NOT NULL");
+ }
+
+ @Test void testPercentileWithinGroupOverFailsInDefaultConformance() {
+ final String sql = "select\n"
+ + " ^percentile_cont(0.25) within group (order by sal)^\n"
+ + " over (partition by deptno) as c\n"
+ + "from emp";
+ // Rejected under the default conformance, which does not allow WITHIN
GROUP
+ // to be combined with an OVER clause.
+ sql(sql)
+ .fails("OVER must be applied to aggregate function");
+ }
+
/** Tests that {@code PERCENTILE_CONT} only allows numeric fields. */
@Test void testPercentileContMustOrderByNumeric() {
final String sql = "select\n"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 064d6fb95e..90511aa406 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2166,6 +2166,28 @@ ### Window functions
`DISTINCT`, `FILTER` and `WITHIN GROUP` are as described for aggregate
functions.
+#### WITHIN GROUP clause in window functions
+
+Combining a `WITHIN GROUP (ORDER BY ...)` clause with an `OVER` clause lets an
+aggregate function whose ordering is supplied by `WITHIN GROUP` be used as a
+window function, as in
+
+{% highlight sql %}
+LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) OVER (PARTITION BY deptno)
+{% endhighlight %}
+
+The `WITHIN GROUP` order key orders the function's input but does not restrict
+the window frame: the function is computed over the whole partition and the
+result is broadcast to every row of that partition. Because the sort key is
+supplied by `WITHIN GROUP`, the `OVER` clause must not contain its own
+`ORDER BY` or frame specification.
+
+This is non-standard syntax (supported by Oracle) and is only allowed under a
+conformance that returns true for
+[SqlConformance.allowWithinGroupOverAggregate()]({{ site.apiRoot
}}/org/apache/calcite/sql/validate/SqlConformance.html#allowWithinGroupOverAggregate--),
+such as `BABEL`; otherwise the validator reports "OVER must be applied to
+aggregate function".
+
#### FILTER clause in window functions
When `FILTER` is used with window functions, it is applied in the following
order:
