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 c92f9e8b73 [CALCITE-7697] Simplify window PARTITION BY and ORDER BY
keys in RelBuilder
c92f9e8b73 is described below
commit c92f9e8b7380679f0a665f31ffddef334663f645
Author: Yu Xu <[email protected]>
AuthorDate: Fri Aug 7 21:41:29 2026 +0800
[CALCITE-7697] Simplify window PARTITION BY and ORDER BY keys in RelBuilder
---
.../java/org/apache/calcite/tools/RelBuilder.java | 63 ++++++++++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 5 +-
.../org/apache/calcite/test/RelBuilderTest.java | 102 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 12 +--
core/src/test/resources/sql/sub-query.iq | 4 +-
5 files changed, 175 insertions(+), 11 deletions(-)
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 7cc7437c62..97d2e3080d 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -5142,12 +5142,73 @@ private OverCall
orderBy_(ImmutableList<RexFieldCollation> sortKeys) {
}
};
final RelDataType type = op.inferReturnType(bind);
+ final ImmutableList<RexNode> newPartitionKeys =
+ simplifyPartitionKeys(partitionKeys);
+ final ImmutableList<RexFieldCollation> newSortKeys =
+ simplifySortKeys(newPartitionKeys, sortKeys);
final RexNode over = getRexBuilder()
- .makeOver(pos, type, op, operands, partitionKeys, sortKeys,
+ .makeOver(pos, type, op, operands, newPartitionKeys, newSortKeys,
lowerBound, upperBound, exclude, rows, allowPartial,
nullWhenCountZero,
distinct, ignoreNulls);
return aliasMaybe(over, alias);
}
+
+ /** Removes constant keys from a window's {@code PARTITION BY}. A constant
+ * partition key places every row in the same partition, so it does not
+ * partition the data and can be dropped. */
+ private ImmutableList<RexNode> simplifyPartitionKeys(
+ List<RexNode> partitionKeys) {
+ final ImmutableList.Builder<RexNode> newKeys = ImmutableList.builder();
+ for (RexNode key : partitionKeys) {
+ if (!RexUtil.isConstant(key)) {
+ newKeys.add(key);
+ }
+ }
+ return newKeys.build();
+ }
+
+ /** Removes redundant keys from a window's {@code ORDER BY}. A sort key is
+ * redundant if it is constant, or if it is functionally determined by the
+ * partition keys and earlier sort keys (those columns are fixed within a
+ * partition, so the key cannot affect the ordering). For example, with
+ * {@code PARTITION BY x, y ORDER BY x + y, z} the key {@code x + y} only
+ * references fixed columns and is dropped, leaving {@code ORDER BY z}. */
+ private ImmutableList<RexFieldCollation> simplifySortKeys(
+ List<RexNode> partitionKeys, List<RexFieldCollation> sortKeys) {
+ // A RANGE frame with a value offset (e.g. RANGE BETWEEN 5 PRECEDING)
+ // derives its bounds from the sort key values, so its keys must be kept.
+ if (!rows
+ && (lowerBound.getOffset() != null || upperBound.getOffset() !=
null)) {
+ return ImmutableList.copyOf(sortKeys);
+ }
+ // Columns whose value is fixed within a partition: partition keys plus
+ // columns pinned by an earlier single-column sort keys.
+ ImmutableBitSet fixedColumns = ImmutableBitSet.of();
+ for (RexNode key : partitionKeys) {
+ if (key instanceof RexInputRef) {
+ fixedColumns = fixedColumns.set(((RexInputRef) key).getIndex());
+ }
+ }
+ final ImmutableList.Builder<RexFieldCollation> newSortKeys =
+ ImmutableList.builder();
+ for (RexFieldCollation collation : sortKeys) {
+ final RexNode key = collation.left;
+ if (RexUtil.isConstant(key)) {
+ continue;
+ }
+ final ImmutableBitSet keyColumns = RelOptUtil.InputFinder.bits(key);
+ if (!keyColumns.isEmpty()
+ && RexUtil.isDeterministic(key)
+ && fixedColumns.contains(keyColumns)) {
+ continue;
+ }
+ newSortKeys.add(collation);
+ if (key instanceof RexInputRef) {
+ fixedColumns = fixedColumns.set(((RexInputRef) key).getIndex());
+ }
+ }
+ return newSortKeys.build();
+ }
}
/** Collects the extra expressions needed for {@link #aggregate}.
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 2a500bcce7..779b320574 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
@@ -2858,8 +2858,9 @@ private SqlDialect nonOrdinalDialect() {
@Test void testNoNeedRewriteOrderByConstantsForOver() {
final String query = "select row_number() over "
+ "(order by 1 nulls last) from \"employee\"";
- // Default dialect keep numeric constant keys in the over of order-by.
- sql(query).ok("SELECT ROW_NUMBER() OVER (ORDER BY 1)\n"
+ // A constant ORDER BY key places every row in the same peer group, so it
+ // is removed when the window is built, leaving an empty OVER clause.
+ sql(query).ok("SELECT ROW_NUMBER() OVER ()\n"
+ "FROM \"foodmart\".\"employee\"");
}
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 cce1aea309..ebe8990c1d 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -1186,6 +1186,108 @@ private RexNode caseCall(RelBuilder b, RexNode ref,
RexNode... nodes) {
assertThat(f.apply(createBuilder()), hasTree(expected));
}
+ /** Tests that RelBuilder removes a constant key from a window's
+ * {@code PARTITION BY}, since a constant partition key places every row in
+ * the same partition. */
+ @Test void testProjectOverConstantPartitionKey() {
+ final Function<RelBuilder, RelNode> f = b -> b.scan("EMP")
+ .project(b.field("DEPTNO"),
+ b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
+ .over()
+ .partitionBy(b.literal(1))
+ .orderBy(b.field("EMPNO"))
+ .rowsUnbounded()
+ .as("x"))
+ .build();
+ final String expected = ""
+ + "LogicalProject(DEPTNO=[$7], x=[ROW_NUMBER() OVER (ORDER BY $0)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ }
+
+ /** Tests that RelBuilder keeps non-constant partition keys and drops only
the
+ * constant one. */
+ @Test void testProjectOverPartialConstantPartitionKey() {
+ final Function<RelBuilder, RelNode> f = b -> b.scan("EMP")
+ .project(b.field("DEPTNO"),
+ b.aggregateCall(SqlStdOperatorTable.SUM, b.field("SAL"))
+ .over()
+ .partitionBy(b.field("DEPTNO"), b.literal(1))
+ .orderBy(b.field("EMPNO"))
+ .rowsUnbounded()
+ .as("x"))
+ .build();
+ final String expected = ""
+ + "LogicalProject(DEPTNO=[$7], "
+ + "x=[SUM($5) OVER (PARTITION BY $7 ORDER BY $0 RANGE BETWEEN "
+ + "UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ }
+
+ /** Tests that RelBuilder removes a constant key from a window's
+ * {@code ORDER BY}. */
+ @Test void testProjectOverConstantSortKey() {
+ final Function<RelBuilder, RelNode> f = b -> b.scan("EMP")
+ .project(b.field("DEPTNO"),
+ b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
+ .over()
+ .partitionBy()
+ .orderBy(b.literal(1), b.field("EMPNO"))
+ .rowsUnbounded()
+ .as("x"))
+ .build();
+ final String expected = ""
+ + "LogicalProject(DEPTNO=[$7], x=[ROW_NUMBER() OVER (ORDER BY $0)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ }
+
+ /** Tests that RelBuilder removes a sort key that is functionally determined
+ * by the partition keys: with {@code PARTITION BY DEPTNO, SAL ORDER BY
+ * DEPTNO + SAL, EMPNO} the key {@code DEPTNO + SAL} references only fixed
+ * columns and is dropped, leaving {@code ORDER BY EMPNO}. */
+ @Test void testProjectOverFunctionallyDependentSortKey() {
+ final Function<RelBuilder, RelNode> f = b -> b.scan("EMP")
+ .project(b.field("DEPTNO"),
+ b.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
+ .over()
+ .partitionBy(b.field("DEPTNO"), b.field("SAL"))
+ .orderBy(
+ b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
+ b.field("SAL")),
+ b.field("EMPNO"))
+ .rowsUnbounded()
+ .as("x"))
+ .build();
+ final String expected = ""
+ + "LogicalProject(DEPTNO=[$7], "
+ + "x=[ROW_NUMBER() OVER (PARTITION BY $7, $5 ORDER BY $0)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ }
+
+ /** Tests that RelBuilder keeps a sort key that would otherwise be dropped
+ * (here {@code DEPTNO}, which equals the partition key) when the frame is a
+ * RANGE with a value offset, because such a frame derives its bounds from
the
+ * sort key values. */
+ @Test void testProjectOverRangeOffsetKeepsSortKey() {
+ final Function<RelBuilder, RelNode> f = b -> b.scan("EMP")
+ .project(b.field("DEPTNO"),
+ b.aggregateCall(SqlStdOperatorTable.SUM, b.field("SAL"))
+ .over()
+ .partitionBy(b.field("DEPTNO"))
+ .orderBy(b.field("DEPTNO"))
+ .rangeBetween(b.preceding(b.literal(5)), b.currentRow())
+ .as("x"))
+ .build();
+ final String expected = ""
+ + "LogicalProject(DEPTNO=[$7], "
+ + "x=[SUM($5) OVER (PARTITION BY $7 ORDER BY $7 RANGE 5 PRECEDING)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(f.apply(createBuilder()), hasTree(expected));
+ }
+
@Test void testRename() {
final RelBuilder builder = RelBuilder.create(config().build());
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 49681f45a3..96ca66bbc7 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -17412,7 +17412,7 @@ from (
<Resource name="planAfter">
<![CDATA[
LogicalProject(COL1=[$2], COL2=[$3], COL3=[$4])
- LogicalWindow(window#0=[window(partition {1} range between UNBOUNDED
PRECEDING and CURRENT ROW aggs [SUM($2)])], window#1=[window(order by [1] aggs
[SUM($2)])], window#2=[window(partition {1} range between UNBOUNDED PRECEDING
and CURRENT ROW aggs [SUM(5000)])], constants=[[100]])
+ LogicalWindow(window#0=[window(partition {1} aggs [SUM($2)])],
window#1=[window(order by [1] aggs [SUM($2)])], window#2=[window(partition {1}
range between UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM(5000)])],
constants=[[100]])
LogicalProject(SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -17420,7 +17420,7 @@ LogicalProject(COL1=[$2], COL2=[$3], COL3=[$4])
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(COL1=[SUM(100) OVER (PARTITION BY $7, $5 ORDER BY $5)],
COL2=[SUM(100) OVER (PARTITION BY $5 ORDER BY $7)], COL3=[SUM($5) OVER
(PARTITION BY $7 ORDER BY $5)])
+LogicalProject(COL1=[SUM(100) OVER (PARTITION BY $7, $5)], COL2=[SUM(100) OVER
(PARTITION BY $5 ORDER BY $7)], COL3=[SUM($5) OVER (PARTITION BY $7 ORDER BY
$5)])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -17437,7 +17437,7 @@ from (
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(COL1=[SUM(100) OVER (ORDER BY $7, $0 RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)], COL2=[SUM(100) OVER (PARTITION BY $5, $7 ORDER BY
$7, $0 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
+LogicalProject(COL1=[SUM(100) OVER (ORDER BY $7, $0 RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)], COL2=[SUM(100) OVER (PARTITION BY $5, $7 ORDER BY $0
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -17445,7 +17445,7 @@ LogicalProject(COL1=[SUM(100) OVER (ORDER BY $7, $0
RANGE BETWEEN CURRENT ROW AN
<Resource name="planAfter">
<![CDATA[
LogicalProject(COL1=[$3], COL2=[$4])
- LogicalWindow(window#0=[window(order by [2, 0] range between CURRENT ROW and
UNBOUNDED FOLLOWING aggs [SUM($3)])], window#1=[window(partition {2} order by
[2, 0] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[SUM($3)])], constants=[[100]])
+ LogicalWindow(window#0=[window(order by [2, 0] range between CURRENT ROW and
UNBOUNDED FOLLOWING aggs [SUM($3)])], window#1=[window(partition {2} order by
[0] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($3)])],
constants=[[100]])
LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -23476,13 +23476,13 @@ window w as (partition by empno order by empno)]]>
<Resource name="planAfter">
<![CDATA[
LogicalProject(EXPR$0=[$9], EXPR$1=[$9])
- LogicalWindow(window#0=[window(partition {0} order by [0] aggs [COUNT()])])
+ LogicalWindow(window#0=[window(partition {0} aggs [COUNT()])])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(EXPR$0=[COUNT() OVER (PARTITION BY $0 ORDER BY $0)],
EXPR$1=[COUNT() OVER (PARTITION BY $0 ORDER BY $0)])
+LogicalProject(EXPR$0=[COUNT() OVER (PARTITION BY $0)], EXPR$1=[COUNT() OVER
(PARTITION BY $0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index d6364652cc..5d1bbb7386 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -8640,7 +8640,7 @@ EnumerableCalc(expr#0..1=[{inputs}], T1B=[$t1])
EnumerableValues(tuples=[[{ 'val1a', 6 }, { 'val1b', 8 }, { 'val1a', 16 },
{ 'val1a', 16 }, { 'val1c', 8 }, { 'val1d', null }, { 'val1d', null }, {
'val1e', 10 }, { 'val1e', 10 }, { 'val1d', 10 }, { 'val1a', 6 }, { 'val1e', 10
}]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableAggregate(group=[{0}], EXPR$0=[MAX($4)])
- EnumerableWindow(window#0=[window(partition {0, 1, 3} order by [3]
aggs [RANK()])])
+ EnumerableWindow(window#0=[window(partition {0, 1, 3} aggs [RANK()])])
EnumerableMergeJoin(condition=[=($2, $3)], joinType=[inner])
EnumerableSort(sort0=[$2], dir0=[ASC])
EnumerableValues(tuples=[[{ 'val2a', 6, 12 }, { 'val1b', 10, 12
}, { 'val1b', 8, 16 }, { 'val1c', 12, 16 }, { 'val1b', null, 16 }, { 'val2e',
8, null }, { 'val1f', 19, null }, { 'val1b', 10, 12 }, { 'val1b', 8, 16 }, {
'val1c', 12, 16 }, { 'val1e', 8, null }, { 'val1f', 19, null }, { 'val1b',
null, 16 }]])
@@ -8681,7 +8681,7 @@ EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableNestedLoopJoin(condition=[>(CAST($0):BIGINT, $1)],
joinType=[inner])
EnumerableValues(tuples=[[{ 6 }, { 8 }, { 16 }, { 16 }, { 8 }, { null },
{ null }, { 10 }, { 10 }, { 10 }, { 6 }, { 10 }]])
EnumerableAggregate(group=[{}], EXPR$0=[MAX($3)])
- EnumerableWindow(window#0=[window(partition {1, 2} order by [1] aggs
[RANK()])])
+ EnumerableWindow(window#0=[window(partition {1, 2} aggs [RANK()])])
EnumerableAggregate(group=[{0, 1}], T3D=[MAX($2)])
EnumerableValues(tuples=[[{ 6, 12, 110 }, { 6, 12, 10 }, { 10, 12,
219 }, { 10, 12, 19 }, { 8, 16, 319 }, { 8, 16, 19 }, { 17, 16, 519 }, { 17,
16, 19 }, { null, 16, 419 }, { null, 16, 19 }, { 8, null, 719 }, { 8, null, 19
}]])
!plan
