This is an automated email from the ASF dual-hosted git repository.
jhyde 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 99a0df108a [CALCITE-5883] ROWS window aggregates ignore frames when
there is no ORDER BY clause
99a0df108a is described below
commit 99a0df108a9f72805afb6d87ec5b2c0ed258f1ec
Author: Julian Hyde <[email protected]>
AuthorDate: Mon Aug 19 20:02:32 2024 -0700
[CALCITE-5883] ROWS window aggregates ignore frames when there is no ORDER
BY clause
Before this change, `SUM(x) OVER (ROWS 1 PRECEDING)` was
treated the same as `SUM(x) OVER (ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)`. This was wrong -
removing frames is correct for RANGE but not for ROWS.
This change also fixes a special case,
[CALCITE-6538] OVER (ROWS CURRENT ROW) should return a
window with one row, not all rows
In class RexWindowBound, add `isUnboundedPreceding()` and
`isUnboundedFollowing()` convenience methods.
Fix `RexWindow digest`, so that `OVER ()` is equivalent to
`OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)`
but not equivalent to `OVER (ROWS CURRENT ROW)`.
Add Quidem tests for OVER, checked against Postgres.
A few cosmetic changes, such as removing `assert x != null`
when x is a parameter not declared `@Nullable`, adding static
imports for `Objects.requireNonNull`, and
changing `size() = 0` to `isEmpty()`.
Close apache/calcite#3925
---
.../adapter/enumerable/EnumerableWindow.java | 2 +-
.../java/org/apache/calcite/rel/core/Window.java | 26 +--
.../java/org/apache/calcite/rex/RexBuilder.java | 16 +-
.../java/org/apache/calcite/rex/RexShuttle.java | 8 +-
.../java/org/apache/calcite/rex/RexWindow.java | 55 +++--
.../org/apache/calcite/rex/RexWindowBound.java | 10 +
.../apache/calcite/sql2rel/SqlToRelConverter.java | 17 +-
.../org/apache/calcite/plan/RelWriterTest.java | 65 ++++--
.../org/apache/calcite/rex/RexProgramTest.java | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 24 ++
.../org/apache/calcite/test/RelOptRulesTest.xml | 10 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 29 ++-
core/src/test/resources/sql/winagg.iq | 245 +++++++++++++++++++++
.../calcite/piglet/PigRelToSqlConverter.java | 11 +-
.../java/org/apache/calcite/test/PigRelOpTest.java | 3 +-
15 files changed, 431 insertions(+), 94 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
index 7a4b2687be..c180e3fc0f 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
@@ -401,7 +401,7 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
}
Expression lowerBoundCanChange =
- group.lowerBound.isUnbounded() && group.lowerBound.isPreceding()
+ group.lowerBound.isUnboundedPreceding()
? Expressions.constant(false)
: Expressions.notEqual(startX, prevStart);
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Window.java
b/core/src/main/java/org/apache/calcite/rel/core/Window.java
index 86a8822dc7..f1a0418127 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Window.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Window.java
@@ -55,7 +55,9 @@ import
org.checkerframework.checker.nullness.qual.RequiresNonNull;
import java.util.AbstractList;
import java.util.Collections;
import java.util.List;
-import java.util.Objects;
+
+import static java.util.Objects.hash;
+import static java.util.Objects.requireNonNull;
/**
* A relational expression representing a set of window aggregates.
@@ -91,8 +93,7 @@ public abstract class Window extends SingleRel implements
Hintable {
RelNode input, List<RexLiteral> constants, RelDataType rowType,
List<Group> groups) {
super(cluster, traitSet, input);
this.constants = ImmutableList.copyOf(constants);
- assert rowType != null;
- this.rowType = rowType;
+ this.rowType = requireNonNull(rowType, "rowType");
this.groups = ImmutableList.copyOf(groups);
this.hints = ImmutableList.copyOf(hints);
}
@@ -267,12 +268,12 @@ public abstract class Window extends SingleRel implements
Hintable {
RexWindowExclusion exclude,
RelCollation orderKeys,
List<RexWinAggCall> aggCalls) {
- this.keys = Objects.requireNonNull(keys, "keys");
+ this.keys = requireNonNull(keys, "keys");
this.isRows = isRows;
- this.lowerBound = Objects.requireNonNull(lowerBound, "lowerBound");
- this.upperBound = Objects.requireNonNull(upperBound, "upperBound");
+ this.lowerBound = requireNonNull(lowerBound, "lowerBound");
+ this.upperBound = requireNonNull(upperBound, "upperBound");
this.exclude = exclude;
- this.orderKeys = Objects.requireNonNull(orderKeys, "orderKeys");
+ this.orderKeys = requireNonNull(orderKeys, "orderKeys");
this.aggCalls = ImmutableList.copyOf(aggCalls);
this.digest = computeString();
}
@@ -297,17 +298,14 @@ public abstract class Window extends SingleRel implements
Hintable {
buf.append(orderKeys);
}
if (orderKeys.getFieldCollations().isEmpty()
- && lowerBound.isUnbounded()
- && lowerBound.isPreceding()
- && upperBound.isUnbounded()
- && upperBound.isFollowing()) {
+ && lowerBound.isUnboundedPreceding()
+ && upperBound.isUnboundedFollowing()) {
// skip bracket if no ORDER BY, and if bracket is the default,
// "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING",
// which is equivalent to
// "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
} else if (!orderKeys.getFieldCollations().isEmpty()
- && lowerBound.isUnbounded()
- && lowerBound.isPreceding()
+ && lowerBound.isUnboundedPreceding()
&& upperBound.isCurrentRow()
&& !isRows) {
// skip bracket if there is ORDER BY, and if bracket is the default,
@@ -467,7 +465,7 @@ public abstract class Window extends SingleRel implements
Hintable {
@Override public int hashCode() {
if (hash == 0) {
- hash = Objects.hash(super.hashCode(), ordinal, distinct, ignoreNulls);
+ hash = hash(super.hashCode(), ordinal, distinct, ignoreNulls);
}
return hash;
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index 4d40f2be03..0814f474df 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -512,8 +512,12 @@ public class RexBuilder {
RexWindowBound upperBound,
boolean rows,
RexWindowExclusion exclude) {
- if (lowerBound.isUnbounded() && lowerBound.isPreceding()
- && upperBound.isUnbounded() && upperBound.isFollowing()) {
+ if (orderKeys.isEmpty() && !rows) {
+ lowerBound = RexWindowBounds.UNBOUNDED_PRECEDING;
+ upperBound = RexWindowBounds.UNBOUNDED_FOLLOWING;
+ }
+ if (lowerBound.isUnboundedPreceding()
+ && upperBound.isUnboundedFollowing()) {
// RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
// is equivalent to
// ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
@@ -936,7 +940,7 @@ public class RexBuilder {
* Makes a reinterpret cast.
*
* @param type type returned by the cast
- * @param exp expression to be casted
+ * @param exp expression to be cast
* @param checkOverflow whether an overflow check is required
* @return a RexCall with two operands and a special return type
*/
@@ -945,7 +949,7 @@ public class RexBuilder {
RexNode exp,
RexNode checkOverflow) {
List<RexNode> args;
- if ((checkOverflow != null) && checkOverflow.isAlwaysTrue()) {
+ if (checkOverflow.isAlwaysTrue()) {
args = ImmutableList.of(exp, checkOverflow);
} else {
args = ImmutableList.of(exp);
@@ -1268,8 +1272,7 @@ public class RexBuilder {
* @return Character string literal
*/
protected RexLiteral makePreciseStringLiteral(String s) {
- assert s != null;
- if (s.equals("")) {
+ if (s.isEmpty()) {
return charEmpty;
}
return makeCharLiteral(new NlsString(s, null, null));
@@ -1342,7 +1345,6 @@ public class RexBuilder {
* defaults.
*/
public RexLiteral makeCharLiteral(NlsString str) {
- assert str != null;
RelDataType type = SqlUtil.createNlsStringType(typeFactory, str);
return makeLiteral(str, type, SqlTypeName.CHAR);
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
index 466a0df6fb..189cd8954b 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
@@ -75,16 +75,14 @@ public class RexShuttle implements RexVisitor<RexNode> {
visitList(window.partitionKeys, update);
final RexWindowBound lowerBound = window.getLowerBound().accept(this);
final RexWindowBound upperBound = window.getUpperBound().accept(this);
- if (lowerBound == null
- || upperBound == null
- || !update[0]
+ if (!update[0]
&& lowerBound == window.getLowerBound()
&& upperBound == window.getUpperBound()) {
return window;
}
boolean rows = window.isRows();
- if (lowerBound.isUnbounded() && lowerBound.isPreceding()
- && upperBound.isUnbounded() && upperBound.isFollowing()) {
+ if (lowerBound.isUnboundedPreceding()
+ && upperBound.isUnboundedFollowing()) {
// RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
// is equivalent to
// ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
diff --git a/core/src/main/java/org/apache/calcite/rex/RexWindow.java
b/core/src/main/java/org/apache/calcite/rex/RexWindow.java
index 9c1b39f5b7..6e3ee28ff8 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexWindow.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexWindow.java
@@ -23,10 +23,11 @@ import com.google.common.collect.ImmutableList;
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
-import java.util.Objects;
import static com.google.common.base.Preconditions.checkArgument;
+import static java.util.Objects.requireNonNull;
+
/**
* Specification of the window of rows over which a {@link RexOver} windowed
* aggregate is evaluated.
@@ -81,15 +82,16 @@ public class RexWindow {
RexWindowExclusion exclude) {
this.partitionKeys = ImmutableList.copyOf(partitionKeys);
this.orderKeys = ImmutableList.copyOf(orderKeys);
- this.lowerBound = Objects.requireNonNull(lowerBound, "lowerBound");
- this.upperBound = Objects.requireNonNull(upperBound, "upperBound");
+ this.lowerBound = requireNonNull(lowerBound, "lowerBound");
+ this.upperBound = requireNonNull(upperBound, "upperBound");
this.exclude = exclude;
this.isRows = isRows;
this.nodeCount = computeCodeCount();
this.digest = computeDigest();
checkArgument(
- !(lowerBound.isUnbounded() && lowerBound.isPreceding()
- && upperBound.isUnbounded() && upperBound.isFollowing() && isRows),
+ !(lowerBound.isUnboundedPreceding()
+ && upperBound.isUnboundedFollowing()
+ && isRows),
"use RANGE for unbounded, not ROWS");
}
@@ -157,16 +159,18 @@ public class RexWindow {
// There are 3 reasons to skip the ROWS/RANGE clause.
// 1. If this window is being used with a RANK-style function that does not
// allow framing, or
- // 2. If there is no ORDER BY (in which case a frame is invalid), or
- // 3. If the ROWS/RANGE clause is the default, "RANGE BETWEEN UNBOUNDED
- // PRECEDING AND CURRENT ROW"
+ // 2. If it is RANGE without ORDER BY (in which case all frames yield all
+ // rows),
+ // 3. If it is an unbounded range
+ // ("RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
+ // or "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING")
+ // with no ORDER BY.
if (!allowFraming // 1
- || orderKeys.isEmpty() // 2
- || (lowerBound.isUnbounded() // 3
- && lowerBound.isPreceding()
- && upperBound.isCurrentRow()
- && !isRows)) {
- // No ROWS or RANGE clause
+ || (!isRows && orderKeys.isEmpty()) // 2
+ || (orderKeys.isEmpty()
+ && lowerBound.isUnboundedPreceding() // 3
+ && upperBound.isUnboundedFollowing())) {
+ // Don't print a ROWS or RANGE clause
} else if (upperBound.isCurrentRow()) {
// Per MSSQL: If ROWS/RANGE is specified and <window frame preceding>
// is used for <window frame extent> (short syntax) then this
@@ -175,18 +179,25 @@ public class RexWindow {
// "ROWS 5 PRECEDING" is equal to "ROWS BETWEEN 5 PRECEDING AND CURRENT
// ROW".
//
- // By similar reasoning to (3) above, we print the shorter option if it
is
+ // We print the shorter option if it is
// the default. If the RexWindow is, say, "ROWS BETWEEN 5 PRECEDING AND
// CURRENT ROW", we output "ROWS 5 PRECEDING" because it is equivalent
and
// is shorter.
- sb.append(sb.length() > initialLength
- ? (isRows ? " ROWS " : " RANGE ")
- : (isRows ? "ROWS " : "RANGE "))
- .append(lowerBound);
+ if (!isRows && lowerBound.isUnboundedPreceding()) {
+ // OVER (ORDER BY x)
+ // OVER (ORDER BY x RANGE UNBOUNDED PRECEDING)
+ // OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ // are equivalent, so print the first (i.e. nothing).
+ } else {
+ sb.append(sb.length() > initialLength ? " " : "")
+ .append(isRows ? "ROWS" : "RANGE")
+ .append(' ')
+ .append(lowerBound);
+ }
} else {
- sb.append(sb.length() > initialLength
- ? (isRows ? " ROWS BETWEEN " : " RANGE BETWEEN ")
- : (isRows ? "ROWS BETWEEN " : "RANGE BETWEEN "))
+ sb.append(sb.length() > initialLength ? " " : "")
+ .append(isRows ? "ROWS" : "RANGE")
+ .append(" BETWEEN ")
.append(lowerBound)
.append(" AND ")
.append(upperBound);
diff --git a/core/src/main/java/org/apache/calcite/rex/RexWindowBound.java
b/core/src/main/java/org/apache/calcite/rex/RexWindowBound.java
index e2d0d7fd2a..0072db9c1a 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexWindowBound.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexWindowBound.java
@@ -48,6 +48,16 @@ public abstract class RexWindowBound {
return false;
}
+ /** Returns whether the bound is {@code UNBOUNDED PRECEDING}. */
+ public final boolean isUnboundedPreceding() {
+ return isUnbounded() && isPreceding();
+ }
+
+ /** Returns whether the bound is {@code UNBOUNDED FOLLOWING}. */
+ public final boolean isUnboundedFollowing() {
+ return isUnbounded() && isFollowing();
+ }
+
/**
* Returns if the bound is PRECEDING.
*
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 e32c48f756..d88daa6318 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1720,8 +1720,8 @@ public class SqlToRelConverter {
SqlNodeList selectList = select.getSelectList();
SqlNodeList groupList = select.getGroup();
- if ((selectList.size() == 1)
- && ((groupList == null) || (groupList.size() == 0))) {
+ if (selectList.size() == 1
+ && (groupList == null || groupList.isEmpty())) {
SqlNode selectExpr = selectList.get(0);
if (selectExpr instanceof SqlCall) {
SqlCall selectExprCall = (SqlCall) selectExpr;
@@ -2284,8 +2284,9 @@ public class SqlToRelConverter {
// ROW_NUMBER() expects specific kind of framing.
rows = true;
}
- } else if (orderList.size() == 0) {
- // Without ORDER BY, there must be no bracketing.
+ } else if (orderList.isEmpty() && !rows) {
+ // In RANGE without ORDER BY, all rows are equivalent, so bracketing has
+ // no effect.
sqlLowerBound = SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO);
sqlUpperBound = SqlWindow.createUnboundedFollowing(SqlParserPos.ZERO);
} else if (sqlLowerBound == null && sqlUpperBound == null) {
@@ -2307,7 +2308,7 @@ public class SqlToRelConverter {
bb.convertExpression(requireNonNull(sqlLowerBound, "sqlLowerBound"));
final RexNode upperBound =
bb.convertExpression(requireNonNull(sqlUpperBound, "sqlUpperBound"));
- if (orderList.size() == 0 && !rows) {
+ if (orderList.isEmpty() && !rows) {
// A logical range requires an ORDER BY clause. Use the implicit
// ordering of this relation. There must be one, otherwise it would
// have failed validation.
@@ -2745,7 +2746,7 @@ public class SqlToRelConverter {
// 3. Gather columns used as arguments to aggregate functions.
pivotBb.agg = aggConverter;
final List<@Nullable String> aggAliasList = new ArrayList<>();
- assert aggConverter.aggCalls.size() == 0;
+ assert aggConverter.aggCalls.isEmpty();
pivot.forEachAgg((alias, call) -> {
call.accept(aggConverter);
aggAliasList.add(alias);
@@ -3404,7 +3405,7 @@ public class SqlToRelConverter {
bb.setRoot(ImmutableList.of(leftRel, rightRel));
replaceSubQueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
final RelNode newRightRel =
- bb.root == null || bb.registered.size() == 0
+ bb.root == null || bb.registered.isEmpty()
? rightRel
: bb.reRegister(rightRel);
bb.setRoot(ImmutableList.of(leftRel, newRightRel));
@@ -4638,7 +4639,7 @@ public class SqlToRelConverter {
joinList.add(relBuilder.build());
}
- if (joinList.size() == 0) {
+ if (joinList.isEmpty()) {
joinList.add(lastList);
}
diff --git a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
index fd9499510e..39ea28a1a1 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
@@ -15,6 +15,7 @@
* limitations under the License.
*/
package org.apache.calcite.plan;
+
import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.plan.volcano.VolcanoPlanner;
@@ -114,6 +115,7 @@ import static java.util.Objects.requireNonNull;
/**
* Unit test for {@link org.apache.calcite.rel.externalize.RelJson}.
*/
+@SuppressWarnings("ConcatenationWithEmptyString")
class RelWriterTest {
public static final String XX = "{\n"
+ " \"rels\": [\n"
@@ -521,11 +523,11 @@ class RelWriterTest {
Frameworks.withPlanner((cluster, relOptSchema, rootSchema) -> {
rootSchema.add("hr",
new ReflectiveSchema(new HrSchema()));
+ final RelOptTable table =
+ requireNonNull(
+ relOptSchema.getTableForMember(Arrays.asList("hr", "emps")));
LogicalTableScan scan =
- LogicalTableScan.create(cluster,
- relOptSchema.getTableForMember(
- Arrays.asList("hr", "emps")),
- ImmutableList.of());
+ LogicalTableScan.create(cluster, table, ImmutableList.of());
final RexBuilder rexBuilder = cluster.getRexBuilder();
LogicalFilter filter =
LogicalFilter.create(scan,
@@ -568,11 +570,11 @@ class RelWriterTest {
Frameworks.withPlanner((cluster, relOptSchema, rootSchema) -> {
rootSchema.add("hr",
new ReflectiveSchema(new HrSchema()));
+ final RelOptTable table =
+ requireNonNull(
+ relOptSchema.getTableForMember(Arrays.asList("hr", "emps")));
LogicalTableScan scan =
- LogicalTableScan.create(cluster,
- relOptSchema.getTableForMember(
- Arrays.asList("hr", "emps")),
- ImmutableList.of());
+ LogicalTableScan.create(cluster, table, ImmutableList.of());
final RexBuilder rexBuilder = cluster.getRexBuilder();
final RelDataType bigIntType =
cluster.getTypeFactory().createSqlType(SqlTypeName.BIGINT);
@@ -956,7 +958,7 @@ class RelWriterTest {
rel.explain(jsonWriter);
final String relJson = jsonWriter.asString();
String s = deserializeAndDump(getSchema(rel), relJson, format);
- String expected = null;
+ final String expected;
switch (format) {
case TEXT:
expected = ""
@@ -975,6 +977,8 @@ class RelWriterTest {
+ "$5\\n\" [label=\"0\"]\n"
+ "}\n";
break;
+ default:
+ throw new AssertionError();
}
assertThat(s, isLinux(expected));
}
@@ -1216,8 +1220,7 @@ class RelWriterTest {
final Function<RelBuilder, RelNode> relFn = b ->
mockCountOver(b, "EMP", ImmutableList.of(),
ImmutableList.of("DEPTNO"));
final String expected = ""
- + "LogicalProject($f0=[COUNT() OVER (ORDER BY $7 NULLS LAST "
- + "ROWS UNBOUNDED PRECEDING)])\n"
+ + "LogicalProject($f0=[COUNT() OVER (ORDER BY $7 NULLS LAST)])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n";
relFn(relFn)
.assertThatPlan(isLinux(expected));
@@ -1355,7 +1358,7 @@ class RelWriterTest {
return super.visit(scan);
}
});
- return schemaHolder.get();
+ return requireNonNull(schemaHolder.get());
}
/**
@@ -1427,7 +1430,7 @@ class RelWriterTest {
for (String orderKeyName : orderKeyNames) {
orderKeys.add(new RexFieldCollation(builder.field(orderKeyName),
ImmutableSet.of()));
}
- final RelNode rel = builder
+ return builder
.project(
rexBuilder.makeOver(
type,
@@ -1437,9 +1440,8 @@ class RelWriterTest {
ImmutableList.copyOf(orderKeys),
RexWindowBounds.UNBOUNDED_PRECEDING,
RexWindowBounds.CURRENT_ROW,
- true, true, false, false, false))
+ false, true, false, false, false))
.build();
- return rel;
}
@Test void testHashDistributionWithoutKeys() {
@@ -1462,11 +1464,10 @@ class RelWriterTest {
final RelDataTypeFactory typeFactory = relBuilder.getTypeFactory();
final RelDataType intType =
typeFactory.createSqlType(SqlTypeName.INTEGER);
final RexDynamicParam dynamicParam =
rexBuilder.makeDynamicParam(intType, 0);
- final RelNode relNode = relBuilder
+ return relBuilder
.scan("EMP")
.sortLimit(null, dynamicParam,
relBuilder.fields(RelCollations.EMPTY))
.build();
- return relNode;
};
final String expectedJson = "{\n"
@@ -1528,10 +1529,14 @@ class RelWriterTest {
.project(b.fields(), ImmutableList.of(), true)
.let(b2 -> {
final RelNode input = b2.build();
- final RelOptTable table = input.getInput(0).getTable();
+ final RelOptTable table =
+ requireNonNull(input.getInput(0).getTable());
+ final Prepare.CatalogReader schema =
+ (Prepare.CatalogReader)
+ requireNonNull(table.getRelOptSchema());
final LogicalTableModify modify =
LogicalTableModify.create(table,
- (Prepare.CatalogReader) table.getRelOptSchema(),
+ schema,
input,
TableModify.Operation.INSERT,
null,
@@ -1556,10 +1561,14 @@ class RelWriterTest {
b.equals(b.field("JOB"), b.literal("c")))
.let(b2 -> {
final RelNode filter = b2.build();
- final RelOptTable table = filter.getInput(0).getTable();
+ final RelOptTable table =
+ requireNonNull(filter.getInput(0).getTable());
+ final Prepare.CatalogReader schema =
+ (Prepare.CatalogReader)
+ requireNonNull(table.getRelOptSchema());
final LogicalTableModify modify =
LogicalTableModify.create(table,
- (Prepare.CatalogReader) table.getRelOptSchema(),
+ schema,
filter,
TableModify.Operation.UPDATE,
ImmutableList.of("ENAME"),
@@ -1583,10 +1592,14 @@ class RelWriterTest {
.filter(b.equals(b.field("JOB"), b.literal("c")))
.let(b2 -> {
final RelNode filter = b2.build();
- final RelOptTable table = filter.getInput(0).getTable();
+ final RelOptTable table =
+ requireNonNull(filter.getInput(0).getTable());
+ final Prepare.CatalogReader schema =
+ (Prepare.CatalogReader)
+ requireNonNull(table.getRelOptSchema());
LogicalTableModify modify =
LogicalTableModify.create(table,
- (Prepare.CatalogReader) table.getRelOptSchema(),
+ schema,
filter,
TableModify.Operation.DELETE,
null,
@@ -1647,9 +1660,12 @@ class RelWriterTest {
// INSERT VALUES (0, 'x', 'x', 0, '20200501 10:00:00',
// 0, 0, 0, 0)
final RelNode project = b.build();
+ final Prepare.CatalogReader schema =
+ (Prepare.CatalogReader)
+ requireNonNull(emp.get().getRelOptSchema());
LogicalTableModify modify =
LogicalTableModify.create(emp.get(),
- (Prepare.CatalogReader) emp.get().getRelOptSchema(),
+ schema,
project,
TableModify.Operation.MERGE,
ImmutableList.of("ENAME"),
@@ -1716,6 +1732,7 @@ class RelWriterTest {
return this;
}
+ @SuppressWarnings("UnusedReturnValue")
Fixture assertThatPlan(Matcher<String> matcher) {
final FrameworkConfig config = RelBuilderTest.config().build();
final RelBuilder b = RelBuilder.create(config);
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index a0b983fdd7..ddb66a2af6 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -3616,12 +3616,12 @@ class RexProgramTest extends RexProgramTestBase {
final RelDataType bigintType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
final RexCall countCall =
new RexCall(bigintType, SqlStdOperatorTable.COUNT, ImmutableList.of());
- checkSimplify(m2v(v2m(countCall)), "COUNT() OVER ()");
+ checkSimplify(m2v(v2m(countCall)), "COUNT() OVER (ROWS CURRENT ROW)");
// "m2v(v2m(sum($0))" -> "sum($0) over (rows current row)"
final RexInputRef i0 = rexBuilder.makeInputRef(bigintType, 0);
final RexCall sumCall =
new RexCall(bigintType, SqlStdOperatorTable.SUM, ImmutableList.of(i0));
- checkSimplify(m2v(v2m(sumCall)), "SUM($0) OVER ()");
+ checkSimplify(m2v(v2m(sumCall)), "SUM($0) OVER (ROWS CURRENT ROW)");
}
@Test void testSimplifyUnaryMinus() {
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 02980adc35..47efce21c6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2544,6 +2544,30 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ /** As {@link #testOverDefaultBracket()}, but no {@code ORDER BY},
+ * which makes more things equivalent. */
+ @Test void testOverDefaultBracketNoOrderBy() {
+ // c2 is invalid (therefore commented out);
+ // c3, c6, c7 are equivalent to c1;
+ // c5 is equivalent to c4.
+ final String sql = "select\n"
+ + " count(*) over () c1,\n"
+ + "--count(*) over (\n"
+ + "-- range unbounded preceding) c2,\n"
+ + " count(*) over (\n"
+ + " range between unbounded preceding and current row) c3,\n"
+ + " count(*) over (\n"
+ + " rows unbounded preceding) c4,\n"
+ + " count(*) over (\n"
+ + " rows between unbounded preceding and current row) c5,\n"
+ + " count(*) over (\n"
+ + " range between unbounded preceding and unbounded following)
c6,\n"
+ + " count(*) over (\n"
+ + " rows between unbounded preceding and unbounded following) c7\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-750";>[CALCITE-750]
* Allow windowed aggregate on top of regular aggregate</a>. */
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 01aa3af1ff..c3ee9f7c66 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5272,8 +5272,8 @@ LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
<![CDATA[
LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
LogicalFilter(condition=[IS NULL($2)])
- LogicalProject(EMPNO=[$0], DEPTNO=[$7], $2=[$9])
- LogicalWindow(window#0=[window(aggs [COUNT($0)])])
+ LogicalWindow(window#0=[window(rows between $2 PRECEDING and $3 PRECEDING
aggs [COUNT($0)])])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6794,7 +6794,7 @@ from empm]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject(DEPTNO=[$7], AVGSAL=[CAST(/(SUM($5) OVER (), COUNT($5) OVER
())):INTEGER NOT NULL])
+LogicalProject(DEPTNO=[$7], AVGSAL=[CAST(/(SUM($5) OVER (ROWS CURRENT ROW),
COUNT($5) OVER (ROWS CURRENT ROW))):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6825,7 +6825,7 @@ order by 2 desc limit 3]]>
LogicalProject(DEPTNO=[$0], AVGSAL=[$1])
LogicalProject(DEPTNO=[$0], AVGSAL=[M2V($1)], EXPR$2=[$2])
LogicalSort(sort0=[$2], dir0=[DESC], fetch=[3])
- LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5),
COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (), COUNT($5) OVER
())):INTEGER NOT NULL])
+ LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5),
COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (ROWS CURRENT ROW),
COUNT($5) OVER (ROWS CURRENT ROW))):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -6833,7 +6833,7 @@ LogicalProject(DEPTNO=[$0], AVGSAL=[$1])
<![CDATA[
LogicalProject(DEPTNO=[$0], AVGSAL=[$3])
LogicalSort(sort0=[$2], dir0=[ASC], fetch=[3])
- LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5),
COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (), COUNT($5) OVER
())):INTEGER NOT NULL], $f3=[CAST(/(SUM($5) OVER (), COUNT($5) OVER
())):INTEGER NOT NULL])
+ LogicalProject(DEPTNO=[$7], AVGSAL=[V2M(CAST(/(SUM($5),
COUNT($5))):INTEGER NOT NULL)], EXPR$2=[CAST(/(SUM($5) OVER (ROWS CURRENT ROW),
COUNT($5) OVER (ROWS CURRENT ROW))):INTEGER NOT NULL], $f3=[CAST(/(SUM($5) OVER
(ROWS CURRENT ROW), COUNT($5) OVER (ROWS CURRENT ROW))):INTEGER NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 60b1ce73e6..5d6f6f2f0f 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4657,7 +4657,7 @@ from (
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(DEPTNO=[$7], COUNT_PLUS_10=[+(COUNT() OVER (), 10)])
+LogicalProject(DEPTNO=[$7], COUNT_PLUS_10=[+(COUNT() OVER (ROWS CURRENT ROW),
10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -4670,7 +4670,7 @@ from (select deptno, job, 1 as measure uno from emp) as
t]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(DEPTNO=[$7], C=[COUNT() OVER ()], UNO=[1], TWO=[2])
+LogicalProject(DEPTNO=[$7], C=[COUNT() OVER (ROWS CURRENT ROW)], UNO=[1],
TWO=[2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -5882,6 +5882,31 @@ from emp]]>
<![CDATA[
LogicalProject(C1=[COUNT() OVER (ORDER BY $7)], C2=[COUNT() OVER (ORDER BY
$7)], C3=[COUNT() OVER (ORDER BY $7)], C4=[COUNT() OVER (ORDER BY $7 ROWS
UNBOUNDED PRECEDING)], C5=[COUNT() OVER (ORDER BY $7 ROWS UNBOUNDED
PRECEDING)], C6=[COUNT() OVER (ORDER BY $7 RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)], C7=[COUNT() OVER (ORDER BY $7 RANGE BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testOverDefaultBracketNoOrderBy">
+ <Resource name="sql">
+ <![CDATA[select
+ count(*) over () c1,
+--count(*) over (
+-- range unbounded preceding) c2,
+ count(*) over (
+ range between unbounded preceding and current row) c3,
+ count(*) over (
+ rows unbounded preceding) c4,
+ count(*) over (
+ rows between unbounded preceding and current row) c5,
+ count(*) over (
+ range between unbounded preceding and unbounded following) c6,
+ count(*) over (
+ rows between unbounded preceding and unbounded following) c7
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(C1=[COUNT() OVER ()], C3=[COUNT() OVER ()], C4=[COUNT() OVER
(ROWS UNBOUNDED PRECEDING)], C5=[COUNT() OVER (ROWS UNBOUNDED PRECEDING)],
C6=[COUNT() OVER ()], C7=[COUNT() OVER ()])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index ddd6dfaae1..64e4a0d26c 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -78,6 +78,187 @@ from emp;
!ok
+# [CALCITE-6538] OVER (ROWS CURRENT ROW) should return a window with one row,
not all rows
+# (RANGE CURRENT ROW returns all rows, and is already correct.)
+select ename,
+ sal,
+ sum(sal) over (rows current row) as row_sum_sal,
+ sum(sal) over (range current row) as range_sum_sal
+from emp
+where job = 'MANAGER';
++-------+---------+-------------+---------------+
+| ENAME | SAL | ROW_SUM_SAL | RANGE_SUM_SAL |
++-------+---------+-------------+---------------+
+| BLAKE | 2850.00 | 2850.00 | 8275.00 |
+| CLARK | 2450.00 | 2450.00 | 8275.00 |
+| JONES | 2975.00 | 2975.00 | 8275.00 |
++-------+---------+-------------+---------------+
+(3 rows)
+
+!ok
+
+# [CALCITE-5883] ROWS window aggregates ignore frames when there is no ORDER
BY clause
+# Other windows with ROWS and no ORDER BY.
+select count(*) over (rows between 3 preceding and 2 preceding) as c3p2p,
+ sum(two) over (rows between 3 preceding and 2 preceding) as s3p2p,
+ sum(two) over (rows 1 preceding) as s1p,
+ sum(two) over (rows between 1 preceding and 1 following) as s1p1f,
+ sum(two) over (rows between current row and 2 following) as s2f
+from (select *, 2 as two from emp)
+order by 1;
++-------+-------+-----+-------+-----+
+| C3P2P | S3P2P | S1P | S1P1F | S2F |
++-------+-------+-----+-------+-----+
+| 0 | | 2 | 4 | 6 |
+| 0 | | 4 | 6 | 6 |
+| 1 | 2 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 6 |
+| 2 | 4 | 4 | 6 | 4 |
+| 2 | 4 | 4 | 4 | 2 |
++-------+-------+-----+-------+-----+
+(14 rows)
+
+!ok
+
+# Various combinations of ROWS, RANGE, UNBOUNDED PRECEDING,
+# UNBOUNDED FOLLOWING, and CURRENT ROW.
+#
+# 'OVER (ROWS CURRENT ROW)' includes one row;
+#
+# 'OVER (ROWS UNBOUNDED PRECEDING)' includes the previous rows;
+#
+# 'OVER ()' includes all rows and is equivalent to
+# 'OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)' and
+# 'OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)' and
+# 'OVER (RANGE UNBOUNDED PRECEDING)'.
+# Checked on Postgres.
+select count(*) over (rows current row) as "rows",
+ count(*) over (rows unbounded preceding) as "rowsUp",
+ count(*) over (range unbounded preceding) as "rangeUp",
+ count(*) over () as "empty",
+ count(*) over (rows between unbounded preceding and unbounded following) as
"rowsUpUf",
+ count(*) over (range between unbounded preceding and unbounded following) as
"rangeUpUf",
+ count(*) over (range unbounded preceding) as "rangeUp"
+from emp
+order by 3;
++------+--------+---------+-------+----------+-----------+---------+
+| rows | rowsUp | rangeUp | empty | rowsUpUf | rangeUpUf | rangeUp |
++------+--------+---------+-------+----------+-----------+---------+
+| 1 | 1 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 2 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 3 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 4 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 5 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 6 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 7 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 8 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 9 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 10 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 11 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 12 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 13 | 14 | 14 | 14 | 14 | 14 |
+| 1 | 14 | 14 | 14 | 14 | 14 | 14 |
++------+--------+---------+-------+----------+-----------+---------+
+(14 rows)
+
+!ok
+
+# Various combinations of ROWS, RANGE, UNBOUNDED PRECEDING,
+# UNBOUNDED FOLLOWING, and CURRENT ROW, with PARTITION BY.
+# The equivalence sets are the same the previous query (without PARTITION BY).
+# Checked on Postgres.
+select deptno,
+ count(*) over (partition by deptno rows current row) as "rows",
+ count(*) over (partition by deptno rows unbounded preceding) as "rowsUp",
+ count(*) over (partition by deptno range unbounded preceding) as "rangeUp",
+ count(*) over (partition by deptno) as "empty",
+ count(*) over (partition by deptno rows between unbounded preceding and
unbounded following) as "rowsUpUf",
+ count(*) over (partition by deptno range between unbounded preceding and
unbounded following) as "rangeUpUf",
+ count(*) over (partition by deptno range unbounded preceding) as "rangeUp"
+from emp
+order by 1, 4;
++--------+------+--------+---------+-------+----------+-----------+---------+
+| DEPTNO | rows | rowsUp | rangeUp | empty | rowsUpUf | rangeUpUf | rangeUp |
++--------+------+--------+---------+-------+----------+-----------+---------+
+| 10 | 1 | 1 | 3 | 3 | 3 | 3 | 3 |
+| 10 | 1 | 2 | 3 | 3 | 3 | 3 | 3 |
+| 10 | 1 | 3 | 3 | 3 | 3 | 3 | 3 |
+| 20 | 1 | 1 | 5 | 5 | 5 | 5 | 5 |
+| 20 | 1 | 2 | 5 | 5 | 5 | 5 | 5 |
+| 20 | 1 | 3 | 5 | 5 | 5 | 5 | 5 |
+| 20 | 1 | 4 | 5 | 5 | 5 | 5 | 5 |
+| 20 | 1 | 5 | 5 | 5 | 5 | 5 | 5 |
+| 30 | 1 | 1 | 6 | 6 | 6 | 6 | 6 |
+| 30 | 1 | 2 | 6 | 6 | 6 | 6 | 6 |
+| 30 | 1 | 3 | 6 | 6 | 6 | 6 | 6 |
+| 30 | 1 | 4 | 6 | 6 | 6 | 6 | 6 |
+| 30 | 1 | 5 | 6 | 6 | 6 | 6 | 6 |
+| 30 | 1 | 6 | 6 | 6 | 6 | 6 | 6 |
++--------+------+--------+---------+-------+----------+-----------+---------+
+(14 rows)
+
+!ok
+
+# As previous, with PARTITION BY and ORDER BY.
+#
+# 'OVER (... ROWS CURRENT ROW)' (rows) includes one row;
+#
+# 'OVER (...)' (empty),
+# 'OVER (... RANGE UNBOUNDED PRECEDING)' (rangeUp) and
+# 'OVER (... RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)' (rangeUpC)
+# are equivalent to each other,
+#
+# 'OVER (... ROWS UNBOUNDED PRECEDING)' (rowsUp) and
+# 'OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)' (rowsUpC)
+# are equivalent to each other and include N rows,
+# and are similar to empty/rangeUp/rangeUpC except when there are ties (WARD,
SCOTT);
+#
+# 'OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)'
(rowsUpUf) and
+# 'OVER (... RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)'
(rangeUpUf)
+# are equivalent and return all rows.
+#
+# Checked on Postgres.
+select deptno, sal, ename,
+ count(*) over (partition by deptno order by sal rows current row) as "rows",
+ count(*) over (partition by deptno order by sal) as "empty",
+ count(*) over (partition by deptno order by sal range unbounded preceding)
as "rangeUp",
+ count(*) over (partition by deptno order by sal range between unbounded
preceding and current row) as "rangeUpC",
+ count(*) over (partition by deptno order by sal rows unbounded preceding) as
"rowsUp",
+ count(*) over (partition by deptno order by sal rows between unbounded
preceding and current row) as "rowsUpC",
+ count(*) over (partition by deptno order by sal range between unbounded
preceding and unbounded following) as "rangeUpUf",
+ count(*) over (partition by deptno order by sal rows between unbounded
preceding and unbounded following) as "rowsUpUf"
+from emp
+order by 1, 2;
++--------+---------+--------+------+-------+---------+----------+--------+---------+-----------+----------+
+| DEPTNO | SAL | ENAME | rows | empty | rangeUp | rangeUpC | rowsUp |
rowsUpC | rangeUpUf | rowsUpUf |
++--------+---------+--------+------+-------+---------+----------+--------+---------+-----------+----------+
+| 10 | 1300.00 | MILLER | 1 | 1 | 1 | 1 | 1 |
1 | 3 | 3 |
+| 10 | 2450.00 | CLARK | 1 | 2 | 2 | 2 | 2 |
2 | 3 | 3 |
+| 10 | 5000.00 | KING | 1 | 3 | 3 | 3 | 3 |
3 | 3 | 3 |
+| 20 | 800.00 | SMITH | 1 | 1 | 1 | 1 | 1 |
1 | 5 | 5 |
+| 20 | 1100.00 | ADAMS | 1 | 2 | 2 | 2 | 2 |
2 | 5 | 5 |
+| 20 | 2975.00 | JONES | 1 | 3 | 3 | 3 | 3 |
3 | 5 | 5 |
+| 20 | 3000.00 | SCOTT | 1 | 5 | 5 | 5 | 4 |
4 | 5 | 5 |
+| 20 | 3000.00 | FORD | 1 | 5 | 5 | 5 | 5 |
5 | 5 | 5 |
+| 30 | 950.00 | JAMES | 1 | 1 | 1 | 1 | 1 |
1 | 6 | 6 |
+| 30 | 1250.00 | WARD | 1 | 3 | 3 | 3 | 2 |
2 | 6 | 6 |
+| 30 | 1250.00 | MARTIN | 1 | 3 | 3 | 3 | 3 |
3 | 6 | 6 |
+| 30 | 1500.00 | TURNER | 1 | 4 | 4 | 4 | 4 |
4 | 6 | 6 |
+| 30 | 1600.00 | ALLEN | 1 | 5 | 5 | 5 | 5 |
5 | 6 | 6 |
+| 30 | 2850.00 | BLAKE | 1 | 6 | 6 | 6 | 6 |
6 | 6 | 6 |
++--------+---------+--------+------+-------+---------+----------+--------+---------+-----------+----------+
+(14 rows)
+
+!ok
+
# STDDEV applied to nullable column
select empno,
stddev(CAST(comm AS DECIMAL(12, 4))) over (order by empno rows unbounded
preceding) as stdev
@@ -875,6 +1056,70 @@ order by 1;
!ok
+select
+ count(*) over (order by "one" rows between 2 preceding and 5 following
exclude group) as c2p5fxg,
+ count(*) over (order by "one" rows between 2 preceding and 5 following) as
c2p5f,
+ count(*) over (order by "one" rows 20 preceding) as c20p,
+ sum("one") over (order by "one" rows between 2 preceding and unbounded
following exclude ties) as s2pxt,
+ sum("one") over (order by "one" rows between unbounded preceding and
unbounded following exclude no others) as sxn,
+ sum("one") over (order by "one" rows between unbounded preceding and
unbounded following) as s
+from (select *, 1 as "one" from emp) as e
+order by c20p;
++---------+-------+------+-------+-----+----+
+| C2P5FXG | C2P5F | C20P | S2PXT | SXN | S |
++---------+-------+------+-------+-----+----+
+| 0 | 6 | 1 | 1 | 14 | 14 |
+| 0 | 7 | 2 | 1 | 14 | 14 |
+| 0 | 8 | 3 | 1 | 14 | 14 |
+| 0 | 8 | 4 | 1 | 14 | 14 |
+| 0 | 8 | 5 | 1 | 14 | 14 |
+| 0 | 8 | 6 | 1 | 14 | 14 |
+| 0 | 8 | 7 | 1 | 14 | 14 |
+| 0 | 8 | 8 | 1 | 14 | 14 |
+| 0 | 8 | 9 | 1 | 14 | 14 |
+| 0 | 7 | 10 | 1 | 14 | 14 |
+| 0 | 6 | 11 | 1 | 14 | 14 |
+| 0 | 5 | 12 | 1 | 14 | 14 |
+| 0 | 4 | 13 | 1 | 14 | 14 |
+| 0 | 3 | 14 | 1 | 14 | 14 |
++---------+-------+------+-------+-----+----+
+(14 rows)
+
+!ok
+
+# As above, without ORDER BY. Results are the same, because 'one' has
+# the same value for all rows.
+select
+ count(*) over (rows between 2 preceding and 5 following exclude group) as
c2p5fxg,
+ count(*) over (rows between 2 preceding and 5 following) as c2p5f,
+ count(*) over (rows between 20 preceding and current row) as c20p,
+ sum("one") over (rows between 2 preceding and unbounded following exclude
ties) as s2pxt,
+ sum("one") over (rows between unbounded preceding and unbounded following
exclude no others) as sxn,
+ sum("one") over (rows between unbounded preceding and unbounded following)
as s
+from (select *, 1 as "one" from emp) as e
+order by c20p;
++---------+-------+------+-------+-----+----+
+| C2P5FXG | C2P5F | C20P | S2PXT | SXN | S |
++---------+-------+------+-------+-----+----+
+| 0 | 6 | 1 | 1 | 14 | 14 |
+| 0 | 7 | 2 | 1 | 14 | 14 |
+| 0 | 8 | 3 | 1 | 14 | 14 |
+| 0 | 8 | 4 | 1 | 14 | 14 |
+| 0 | 8 | 5 | 1 | 14 | 14 |
+| 0 | 8 | 6 | 1 | 14 | 14 |
+| 0 | 8 | 7 | 1 | 14 | 14 |
+| 0 | 8 | 8 | 1 | 14 | 14 |
+| 0 | 8 | 9 | 1 | 14 | 14 |
+| 0 | 7 | 10 | 1 | 14 | 14 |
+| 0 | 6 | 11 | 1 | 14 | 14 |
+| 0 | 5 | 12 | 1 | 14 | 14 |
+| 0 | 4 | 13 | 1 | 14 | 14 |
+| 0 | 3 | 14 | 1 | 14 | 14 |
++---------+-------+------+-------+-----+----+
+(14 rows)
+
+!ok
+
select empno,
stddev(comm) over (order by empno rows between 2 preceding and 5 following
exclude current row) as e1,
count(*) over (order by empno rows between 2 preceding and 5 following
exclude group) as e2,
diff --git
a/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
b/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
index 2b6ea23de2..bba1f44baa 100644
--- a/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
+++ b/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
@@ -105,14 +105,21 @@ public class PigRelToSqlConverter extends
RelToSqlConverter {
orderList.add(builder.context.toSql(orderKey));
}
+ SqlNode lowerBound = builder.context.toSql(winGroup.lowerBound);
+ SqlNode upperBound = builder.context.toSql(winGroup.upperBound);
+ if (orderList.isEmpty() && !winGroup.isRows) {
+ // With no ORDER BY, all RANGE windows are equivalent to OVER (),
+ // so simplify.
+ lowerBound = upperBound = null;
+ }
final SqlNode sqlWindow =
SqlWindow.create(null, // Window declaration name
null, // Window reference name
new SqlNodeList(partitionList, POS),
new SqlNodeList(orderList, POS),
SqlLiteral.createBoolean(winGroup.isRows, POS),
- builder.context.toSql(winGroup.lowerBound),
- builder.context.toSql(winGroup.upperBound),
+ lowerBound,
+ upperBound,
null, // allowPartial
builder.context.toSql(winGroup.exclude),
POS);
diff --git a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
index 4e00b561c3..9b5709184a 100644
--- a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
+++ b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
@@ -1667,8 +1667,7 @@ class PigRelOpTest extends PigRelTestBase {
+ " LogicalTableScan(table=[[emp1]])\n";
final String sql = "SELECT w0$o0 AS rank_A, id, name, age, city\n"
- + "FROM (SELECT id, name, age, city, RANK() OVER (RANGE BETWEEN "
- + "UNBOUNDED PRECEDING AND CURRENT ROW)\n"
+ + "FROM (SELECT id, name, age, city, RANK() OVER ()\n"
+ " FROM emp1) AS t\n"
+ "WHERE w0$o0 > 1";
pig(script).assertRel(hasTree(plan))
