You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/03/31 22:42:13 UTC
[calcite] 02/02: [CALCITE-883] Support RESPECT NULLS,
IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE,
NTH_VALUE functions (Chunwei Lei)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit d7946a94adfd2e788f5d324910944dd65dab11ee
Author: Chunwei Lei <ch...@alibaba-inc.com>
AuthorDate: Wed Mar 27 14:58:58 2019 +0800
[CALCITE-883] Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE functions (Chunwei Lei)
IGNORE NULLS works in the parser and validator but not at runtime.
We have changed the Rex model support for aggregate functions even
though today it is only valid for windowed functions.
Fix javadoc error on JDK 8.
Close apache/calcite#1108
---
core/src/main/codegen/config.fmpp | 2 +
core/src/main/codegen/templates/Parser.jj | 23 ++++++
.../adapter/enumerable/EnumerableWindow.java | 3 +
.../java/org/apache/calcite/plan/RelOptUtil.java | 44 +++--------
.../apache/calcite/plan/SubstitutionVisitor.java | 1 +
.../org/apache/calcite/rel/core/AggregateCall.java | 66 +++++++++++-----
.../java/org/apache/calcite/rel/core/Window.java | 23 +++++-
.../apache/calcite/rel/externalize/RelJson.java | 4 +-
.../calcite/rel/externalize/RelJsonReader.java | 2 +-
.../apache/calcite/rel/logical/LogicalWindow.java | 3 +-
.../AggregateExpandDistinctAggregatesRule.java | 20 +++--
.../rel/rules/AggregateFilterTransposeRule.java | 4 +-
.../rel/rules/AggregateReduceFunctionsRule.java | 11 ++-
.../calcite/rel/rules/AggregateStarTableRule.java | 7 +-
.../rel/rules/AggregateUnionTransposeRule.java | 10 +--
.../rel/rules/ProjectWindowTransposeRule.java | 9 +--
.../calcite/rel/rules/ReduceExpressionsRule.java | 48 ++++++-----
.../java/org/apache/calcite/rex/RexBuilder.java | 43 ++++++----
.../main/java/org/apache/calcite/rex/RexOver.java | 12 ++-
.../java/org/apache/calcite/rex/RexShuttle.java | 3 +-
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/sql/SqlAggFunction.java | 6 ++
.../org/apache/calcite/sql/SqlFilterOperator.java | 2 +-
.../main/java/org/apache/calcite/sql/SqlKind.java | 11 +++
.../calcite/sql/SqlNullTreatmentOperator.java | 67 ++++++++++++++++
.../org/apache/calcite/sql/SqlOverOperator.java | 6 ++
.../calcite/sql/SqlSplittableAggFunction.java | 7 +-
.../sql/fun/SqlFirstLastValueAggFunction.java | 4 +
.../calcite/sql/fun/SqlLeadLagAggFunction.java | 4 +
.../calcite/sql/fun/SqlNthValueAggFunction.java | 4 +
.../calcite/sql/fun/SqlStdOperatorTable.java | 9 +++
.../apache/calcite/sql/validate/AggChecker.java | 10 +--
.../apache/calcite/sql2rel/SqlToRelConverter.java | 49 ++++++++----
.../java/org/apache/calcite/tools/RelBuilder.java | 71 ++++++++++-------
.../calcite/runtime/CalciteResource.properties | 1 +
.../org/apache/calcite/plan/RelWriterTest.java | 8 +-
.../calcite/plan/volcano/TraitPropagationTest.java | 2 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 58 +++++++++++---
.../java/org/apache/calcite/test/JdbcTest.java | 24 ++++++
.../org/apache/calcite/test/RelMetadataTest.java | 2 +-
.../apache/calcite/test/SqlToRelConverterTest.java | 22 ++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 92 +++++++++++++++++++++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 26 ++++++
site/_docs/reference.md | 41 ++++++++--
44 files changed, 660 insertions(+), 207 deletions(-)
diff --git a/core/src/main/codegen/config.fmpp b/core/src/main/codegen/config.fmpp
index 7b3fca2..7f55730 100644
--- a/core/src/main/codegen/config.fmpp
+++ b/core/src/main/codegen/config.fmpp
@@ -144,6 +144,7 @@ data: {
"GOTO"
"GRANTED"
"HIERARCHY"
+ "IGNORE"
"IMMEDIATE"
"IMMEDIATELY"
"IMPLEMENTATION"
@@ -229,6 +230,7 @@ data: {
"RELATIVE"
"REPEATABLE"
"REPLACE"
+ "RESPECT"
"RESTART"
"RESTRICT"
"RETURNED_CARDINALITY"
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 5dfa66e..22e1641 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -5652,6 +5652,24 @@ SqlCall withinGroup(SqlNode arg) :
}
}
+SqlCall nullTreatment(SqlCall arg) :
+{
+ final Span span;
+}
+{
+ (
+ <IGNORE> { span = span(); } <NULLS> {
+ return SqlStdOperatorTable.IGNORE_NULLS.createCall(
+ span.end(this), arg);
+ }
+ |
+ <RESPECT> { span = span(); } <NULLS> {
+ return SqlStdOperatorTable.RESPECT_NULLS.createCall(
+ span.end(this), arg);
+ }
+ )
+}
+
/**
* Parses a call to a named function (could be a builtin with regular
* syntax, or else a UDF).
@@ -5711,6 +5729,9 @@ SqlNode NamedFunctionCall() :
call = createCall(qualifiedName, s.end(this), funcType, quantifier, args);
}
[
+ call = nullTreatment(call)
+ ]
+ [
call = withinGroup(call)
]
[
@@ -6398,6 +6419,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < HOLD: "HOLD" >
| < HOUR: "HOUR" >
| < IDENTITY: "IDENTITY" >
+| < IGNORE: "IGNORE" >
| < IMMEDIATE: "IMMEDIATE" >
| < IMMEDIATELY: "IMMEDIATELY" >
| < IMPLEMENTATION: "IMPLEMENTATION" >
@@ -6609,6 +6631,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < REPEATABLE: "REPEATABLE" >
| < REPLACE: "REPLACE" >
| < RESET: "RESET" >
+| < RESPECT: "RESPECT" >
| < RESTART: "RESTART" >
| < RESTRICT: "RESTRICT" >
| < RESULT: "RESULT" >
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 4805793..d952710 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
@@ -210,6 +210,9 @@ public class EnumerableWindow extends Window implements EnumerableRel {
List<AggregateCall> aggregateCalls = group.getAggregateCalls(this);
for (int aggIdx = 0; aggIdx < aggregateCalls.size(); aggIdx++) {
AggregateCall call = aggregateCalls.get(aggIdx);
+ if (call.ignoreNulls()) {
+ throw new UnsupportedOperationException("IGNORE NULLS not supported");
+ }
aggs.add(new AggImpState(aggIdx, call, true));
}
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index 52a9ef2..9160b04 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -467,24 +467,10 @@ public abstract class RelOptUtil {
final RelBuilder relBuilder =
RelFactories.LOGICAL_BUILDER.create(cluster, null);
ret = relBuilder.push(ret)
- .project(ImmutableList.of(extraExpr))
+ .project(extraExpr)
+ .aggregate(relBuilder.groupKey(),
+ relBuilder.min(relBuilder.field(0)).as(extraName))
.build();
-
- final AggregateCall aggCall =
- AggregateCall.create(SqlStdOperatorTable.MIN,
- false,
- false,
- ImmutableList.of(0),
- -1,
- RelCollations.EMPTY,
- 0,
- ret,
- null,
- extraName);
-
- ret =
- LogicalAggregate.create(ret,
- ImmutableBitSet.of(), null, ImmutableList.of(aggCall));
}
return ret;
@@ -561,22 +547,12 @@ public abstract class RelOptUtil {
final int projectedKeyCount = exprs.size();
exprs.add(rexBuilder.makeLiteral(true));
- ret = relBuilder.push(ret).project(exprs).build();
-
- final AggregateCall aggCall =
- AggregateCall.create(SqlStdOperatorTable.MIN,
- false,
- false,
- ImmutableList.of(projectedKeyCount),
- -1,
- RelCollations.EMPTY,
- projectedKeyCount,
- ret,
- null,
- null);
-
- ret = LogicalAggregate.create(ret, ImmutableBitSet.range(projectedKeyCount),
- null, ImmutableList.of(aggCall));
+ ret = relBuilder.push(ret)
+ .project(exprs)
+ .aggregate(
+ relBuilder.groupKey(ImmutableBitSet.range(projectedKeyCount)),
+ relBuilder.min(relBuilder.field(projectedKeyCount)))
+ .build();
switch (logic) {
case TRUE_FALSE_UNKNOWN:
@@ -774,7 +750,7 @@ public abstract class RelOptUtil {
for (int i = 0; i < aggCallCnt; i++) {
aggCalls.add(
AggregateCall.create(SqlStdOperatorTable.SINGLE_VALUE, false, false,
- ImmutableList.of(i), -1, RelCollations.EMPTY, 0, rel, null,
+ false, ImmutableList.of(i), -1, RelCollations.EMPTY, 0, rel, null,
null));
}
diff --git a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
index 3f33414..3ddf3c3 100644
--- a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
+++ b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
@@ -1294,6 +1294,7 @@ public class SubstitutionVisitor {
aggregateCalls.add(
AggregateCall.create(getRollup(aggregateCall.getAggregation()),
aggregateCall.isDistinct(), aggregateCall.isApproximate(),
+ aggregateCall.ignoreNulls(),
ImmutableList.of(target.groupSet.cardinality() + i), -1,
aggregateCall.collation, aggregateCall.type,
aggregateCall.name));
diff --git a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
index d612ea4..c3bf08f 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
@@ -42,6 +42,7 @@ public class AggregateCall {
private final boolean distinct;
private final boolean approximate;
+ private final boolean ignoreNulls;
public final RelDataType type;
public final String name;
@@ -69,7 +70,7 @@ public class AggregateCall {
List<Integer> argList,
RelDataType type,
String name) {
- this(aggFunction, distinct, false,
+ this(aggFunction, distinct, false, false,
argList, -1, RelCollations.EMPTY, type, name);
}
@@ -88,8 +89,8 @@ public class AggregateCall {
* @param name Name (may be null)
*/
private AggregateCall(SqlAggFunction aggFunction, boolean distinct,
- boolean approximate, List<Integer> argList, int filterArg,
- RelCollation collation, RelDataType type, String name) {
+ boolean approximate, boolean ignoreNulls, List<Integer> argList,
+ int filterArg, RelCollation collation, RelDataType type, String name) {
this.type = Objects.requireNonNull(type);
this.name = name;
this.aggFunction = Objects.requireNonNull(aggFunction);
@@ -98,6 +99,7 @@ public class AggregateCall {
this.collation = Objects.requireNonNull(collation);
this.distinct = distinct;
this.approximate = approximate;
+ this.ignoreNulls = ignoreNulls;
}
//~ Methods ----------------------------------------------------------------
@@ -106,7 +108,7 @@ public class AggregateCall {
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, List<Integer> argList, int groupCount, RelNode input,
RelDataType type, String name) {
- return create(aggFunction, distinct, false, argList, -1,
+ return create(aggFunction, distinct, false, false, argList, -1,
RelCollations.EMPTY, groupCount, input, type, name);
}
@@ -114,7 +116,7 @@ public class AggregateCall {
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, List<Integer> argList, int filterArg, int groupCount,
RelNode input, RelDataType type, String name) {
- return create(aggFunction, distinct, false, argList, filterArg,
+ return create(aggFunction, distinct, false, false, argList, filterArg,
RelCollations.EMPTY, groupCount, input, type, name);
}
@@ -123,15 +125,25 @@ public class AggregateCall {
boolean distinct, boolean approximate, List<Integer> argList,
int filterArg, int groupCount,
RelNode input, RelDataType type, String name) {
- return create(aggFunction, distinct, approximate, argList,
+ return create(aggFunction, distinct, approximate, false, argList,
filterArg, RelCollations.EMPTY, groupCount, input, type, name);
}
- /** Creates an AggregateCall, inferring its type if {@code type} is null. */
+ @Deprecated // to be removed before 2.0
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, boolean approximate, List<Integer> argList,
int filterArg, RelCollation collation, int groupCount,
RelNode input, RelDataType type, String name) {
+ return create(aggFunction, distinct, approximate, false, argList, filterArg,
+ collation, groupCount, input, type, name);
+ }
+
+ /** Creates an AggregateCall, inferring its type if {@code type} is null. */
+ public static AggregateCall create(SqlAggFunction aggFunction,
+ boolean distinct, boolean approximate, boolean ignoreNulls,
+ List<Integer> argList, int filterArg, RelCollation collation,
+ int groupCount,
+ RelNode input, RelDataType type, String name) {
if (type == null) {
final RelDataTypeFactory typeFactory =
input.getCluster().getTypeFactory();
@@ -142,15 +154,15 @@ public class AggregateCall {
groupCount, filterArg >= 0);
type = aggFunction.inferReturnType(callBinding);
}
- return create(aggFunction, distinct, approximate, argList, filterArg,
- collation, type, name);
+ return create(aggFunction, distinct, approximate, ignoreNulls, argList,
+ filterArg, collation, type, name);
}
@Deprecated // to be removed before 2.0
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, List<Integer> argList, int filterArg, RelDataType type,
String name) {
- return create(aggFunction, distinct, false, argList, filterArg,
+ return create(aggFunction, distinct, false, false, argList, filterArg,
RelCollations.EMPTY, type, name);
}
@@ -158,18 +170,27 @@ public class AggregateCall {
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, boolean approximate, List<Integer> argList,
int filterArg, RelDataType type, String name) {
- return create(aggFunction, distinct, approximate, argList, filterArg,
+ return create(aggFunction, distinct, approximate, false, argList, filterArg,
RelCollations.EMPTY, type, name);
}
- /** Creates an AggregateCall. */
+ @Deprecated // to be removed before 2.0
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, boolean approximate, List<Integer> argList,
int filterArg, RelCollation collation, RelDataType type, String name) {
- return new AggregateCall(aggFunction, distinct, approximate, argList,
+ return new AggregateCall(aggFunction, distinct, approximate, false, argList,
filterArg, collation, type, name);
}
+ /** Creates an AggregateCall. */
+ public static AggregateCall create(SqlAggFunction aggFunction,
+ boolean distinct, boolean approximate, boolean ignoreNulls,
+ List<Integer> argList, int filterArg, RelCollation collation,
+ RelDataType type, String name) {
+ return new AggregateCall(aggFunction, distinct, approximate, ignoreNulls,
+ argList, filterArg, collation, type, name);
+ }
+
/**
* Returns whether this AggregateCall is distinct, as in <code>
* COUNT(DISTINCT empno)</code>.
@@ -191,6 +212,15 @@ public class AggregateCall {
}
/**
+ * Returns whether this AggregateCall ignores nulls.
+ *
+ * @return whether ignore nulls
+ */
+ public final boolean ignoreNulls() {
+ return ignoreNulls;
+ }
+
+ /**
* Returns the aggregate function.
*
* @return aggregate function
@@ -247,7 +277,7 @@ public class AggregateCall {
if (Objects.equals(this.name, name)) {
return this;
}
- return new AggregateCall(aggFunction, distinct, approximate,
+ return new AggregateCall(aggFunction, distinct, approximate, ignoreNulls,
argList,
filterArg, RelCollations.EMPTY, type,
name);
@@ -328,8 +358,8 @@ public class AggregateCall {
*/
public AggregateCall copy(List<Integer> args, int filterArg,
RelCollation collation) {
- return new AggregateCall(aggFunction, distinct, approximate, args,
- filterArg, collation, type, name);
+ return new AggregateCall(aggFunction, distinct, approximate, ignoreNulls,
+ args, filterArg, collation, type, name);
}
@Deprecated // to be removed before 2.0
@@ -365,8 +395,8 @@ public class AggregateCall {
&& filterArg == this.filterArg
? type
: null;
- return create(aggFunction, distinct, approximate, argList, filterArg,
- collation, newGroupKeyCount, input, newType, getName());
+ return create(aggFunction, distinct, approximate, ignoreNulls, argList,
+ filterArg, collation, newGroupKeyCount, input, newType, getName());
}
/** Creates a copy of this aggregate call, applying a mapping to its
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 013e7cc..67b77a0 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
@@ -315,9 +315,9 @@ public abstract class Window extends SingleRel {
public AggregateCall get(int index) {
final RexWinAggCall aggCall = aggCalls.get(index);
final SqlAggFunction op = (SqlAggFunction) aggCall.getOperator();
- return AggregateCall.create(op, aggCall.distinct,
- false, getProjectOrdinals(aggCall.getOperands()), -1,
- RelCollations.EMPTY,
+ return AggregateCall.create(op, aggCall.distinct, false,
+ aggCall.ignoreNulls, getProjectOrdinals(aggCall.getOperands()),
+ -1, RelCollations.EMPTY,
aggCall.getType(), fieldNames.get(aggCall.ordinal));
}
};
@@ -342,6 +342,19 @@ public abstract class Window extends SingleRel {
/** Whether to eliminate duplicates before applying aggregate function. */
public final boolean distinct;
+ /** Whether to ignore nulls. */
+ public final boolean ignoreNulls;
+
+ @Deprecated // to be removed before 2.0
+ public RexWinAggCall(
+ SqlAggFunction aggFun,
+ RelDataType type,
+ List<RexNode> operands,
+ int ordinal,
+ boolean distinct) {
+ this(aggFun, type, operands, ordinal, distinct, false);
+ }
+
/**
* Creates a RexWinAggCall.
*
@@ -356,10 +369,12 @@ public abstract class Window extends SingleRel {
RelDataType type,
List<RexNode> operands,
int ordinal,
- boolean distinct) {
+ boolean distinct,
+ boolean ignoreNulls) {
super(type, aggFun, operands);
this.ordinal = ordinal;
this.distinct = distinct;
+ this.ignoreNulls = ignoreNulls;
}
/** {@inheritDoc}
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
index 92bdbda..a264946 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
@@ -466,8 +466,8 @@ public class RelJson {
}
final boolean distinct = (Boolean) map.get("distinct");
return rexBuilder.makeOver(type, operator, rexOperands, partitionKeys,
- ImmutableList.copyOf(orderKeys), lowerBound, upperBound, physical, true, false,
- distinct);
+ ImmutableList.copyOf(orderKeys), lowerBound, upperBound, physical,
+ true, false, distinct, false);
} else {
final SqlOperator operator = toOp(relInput, op, map);
final RelDataType type;
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
index 4309f96..0403cc5 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java
@@ -283,7 +283,7 @@ public class RelJsonReader {
final Integer filterOperand = (Integer) jsonAggCall.get("filter");
final RelDataType type =
relJson.toType(cluster.getTypeFactory(), jsonAggCall.get("type"));
- return AggregateCall.create(aggregation, distinct, false, operands,
+ return AggregateCall.create(aggregation, distinct, false, false, operands,
filterOperand == null ? -1 : filterOperand,
RelCollations.EMPTY,
type, null);
diff --git a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
index db5b856..7f0db03 100644
--- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
+++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
@@ -155,7 +155,8 @@ public final class LogicalWindow extends Window {
over.getType(),
toInputRefs(over.operands),
aggMap.size(),
- over.isDistinct());
+ over.isDistinct(),
+ over.ignoreNulls());
aggCalls.add(aggCall);
aggMap.put(over, aggCall);
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
index 7175a5f..5538449 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java
@@ -296,7 +296,8 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
if (!aggCall.isDistinct()) {
final AggregateCall newCall =
AggregateCall.create(aggCall.getAggregation(), false,
- aggCall.isApproximate(), aggCall.getArgList(), -1,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
+ aggCall.getArgList(), -1,
aggCall.collation, bottomGroupSet.cardinality(),
relBuilder.peek(), null, aggCall.name);
bottomAggregateCalls.add(newCall);
@@ -323,6 +324,7 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
AggregateCall.create(aggCall.getAggregation(),
false,
aggCall.isApproximate(),
+ aggCall.ignoreNulls(),
newArgList,
-1,
aggCall.collation,
@@ -338,13 +340,15 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
if (aggCall.getAggregation().getKind() == SqlKind.COUNT) {
newCall =
AggregateCall.create(new SqlSumEmptyIsZeroAggFunction(), false,
- aggCall.isApproximate(), newArgs, -1, aggCall.collation,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
+ newArgs, -1, aggCall.collation,
originalGroupSet.cardinality(), relBuilder.peek(),
aggCall.getType(), aggCall.getName());
} else {
newCall =
AggregateCall.create(aggCall.getAggregation(), false,
- aggCall.isApproximate(), newArgs, -1, aggCall.collation,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
+ newArgs, -1, aggCall.collation,
originalGroupSet.cardinality(),
relBuilder.peek(), aggCall.getType(), aggCall.name);
}
@@ -409,7 +413,7 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
final Map<ImmutableBitSet, Integer> filters = new LinkedHashMap<>();
final int z = groupCount + distinctAggCalls.size();
distinctAggCalls.add(
- AggregateCall.create(SqlStdOperatorTable.GROUPING, false, false,
+ AggregateCall.create(SqlStdOperatorTable.GROUPING, false, false, false,
ImmutableIntList.copyOf(fullGroupSet), -1, RelCollations.EMPTY,
groupSets.size(), relBuilder.peek(), null, "$g"));
for (Ord<ImmutableBitSet> groupSet : Ord.zip(groupSets)) {
@@ -455,7 +459,8 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
.union(aggregate.getGroupSet()));
}
final AggregateCall newCall =
- AggregateCall.create(aggregation, false, aggCall.isApproximate(),
+ AggregateCall.create(aggregation, false,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
newArgList, newFilterArg, aggCall.collation,
aggregate.getGroupCount(), distinct, null, aggCall.name);
newCalls.add(newCall);
@@ -666,7 +671,8 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
aggCall.filterArg >= 0 ? sourceOf.get(aggCall.filterArg) : -1;
final AggregateCall newAggCall =
AggregateCall.create(aggCall.getAggregation(), false,
- aggCall.isApproximate(), newArgs, newFilterArg, aggCall.collation,
+ aggCall.isApproximate(), aggCall.ignoreNulls(),
+ newArgs, newFilterArg, aggCall.collation,
aggCall.getType(), aggCall.getName());
assert refs.get(i) == null;
if (n == 0) {
@@ -755,7 +761,7 @@ public final class AggregateExpandDistinctAggregatesRule extends RelOptRule {
}
final AggregateCall newAggCall =
AggregateCall.create(aggCall.getAggregation(), false,
- aggCall.isApproximate(), newArgs, -1, aggCall.collation,
+ aggCall.isApproximate(), aggCall.ignoreNulls(), newArgs, -1, aggCall.collation,
aggCall.getType(), aggCall.getName());
newAggCalls.set(i, newAggCall);
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateFilterTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateFilterTransposeRule.java
index 9b54f08..50c8aa2 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateFilterTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateFilterTransposeRule.java
@@ -144,8 +144,8 @@ public class AggregateFilterTransposeRule extends RelOptRule {
}
topAggCallList.add(
AggregateCall.create(rollup, aggregateCall.isDistinct(),
- aggregateCall.isApproximate(), ImmutableList.of(i++), -1,
- aggregateCall.collation,
+ aggregateCall.isApproximate(), aggregateCall.ignoreNulls(),
+ ImmutableList.of(i++), -1, aggregateCall.collation,
aggregateCall.type, aggregateCall.name));
}
final Aggregate topAggregate =
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
index 2c9c4e5..e62c786 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java
@@ -314,6 +314,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
return AggregateCall.create(aggFunction,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
ImmutableIntList.of(argOrdinal),
filter,
oldCall.collation,
@@ -338,6 +339,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.SUM,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
oldCall.getArgList(),
oldCall.filterArg,
oldCall.collation,
@@ -349,6 +351,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.COUNT,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
oldCall.getArgList(),
oldCall.filterArg,
oldCall.collation,
@@ -398,13 +401,15 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
final AggregateCall sumZeroCall =
AggregateCall.create(SqlStdOperatorTable.SUM0, oldCall.isDistinct(),
- oldCall.isApproximate(), oldCall.getArgList(), oldCall.filterArg,
+ oldCall.isApproximate(), oldCall.ignoreNulls(),
+ oldCall.getArgList(), oldCall.filterArg,
oldCall.collation, oldAggRel.getGroupCount(), oldAggRel.getInput(),
null, oldCall.name);
final AggregateCall countCall =
AggregateCall.create(SqlStdOperatorTable.COUNT,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
oldCall.getArgList(),
oldCall.filterArg,
oldCall.collation,
@@ -496,6 +501,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.SUM,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
ImmutableIntList.of(argOrdinal),
oldCall.filterArg,
oldCall.collation,
@@ -520,6 +526,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.COUNT,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
oldCall.getArgList(),
oldCall.filterArg,
oldCall.collation,
@@ -593,6 +600,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.SUM,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
ImmutableIntList.of(argOrdinal),
filterArg,
oldCall.collation,
@@ -639,6 +647,7 @@ public class AggregateReduceFunctionsRule extends RelOptRule {
AggregateCall.create(SqlStdOperatorTable.REGR_COUNT,
oldCall.isDistinct(),
oldCall.isApproximate(),
+ oldCall.ignoreNulls(),
argOrdinals,
filterArg,
oldCall.collation,
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateStarTableRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateStarTableRule.java
index b9820c1..069b238 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateStarTableRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateStarTableRule.java
@@ -234,8 +234,8 @@ public class AggregateStarTableRule extends RelOptRule {
if (roll == null) {
break tryRoll;
}
- return AggregateCall.create(roll, false,
- aggregateCall.isApproximate(), ImmutableList.of(offset + i), -1,
+ return AggregateCall.create(roll, false, aggregateCall.isApproximate(),
+ aggregateCall.ignoreNulls(), ImmutableList.of(offset + i), -1,
aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
@@ -252,7 +252,8 @@ public class AggregateStarTableRule extends RelOptRule {
newArgs.add(z);
}
return AggregateCall.create(aggregation, false,
- aggregateCall.isApproximate(), newArgs, -1, aggregateCall.collation,
+ aggregateCall.isApproximate(), aggregateCall.ignoreNulls(),
+ newArgs, -1, aggregateCall.collation,
groupCount, relBuilder.peek(), null, aggregateCall.name);
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
index 05ba565..f723d75 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateUnionTransposeRule.java
@@ -174,13 +174,9 @@ public class AggregateUnionTransposeRule extends RelOptRule {
}
AggregateCall newCall =
AggregateCall.create(aggFun, origCall.isDistinct(),
- origCall.isApproximate(),
- ImmutableList.of(groupCount + ord.i), -1,
- origCall.collation,
- groupCount,
- input,
- aggType,
- origCall.getName());
+ origCall.isApproximate(), origCall.ignoreNulls(),
+ ImmutableList.of(groupCount + ord.i), -1, origCall.collation,
+ groupCount, input, aggType, origCall.getName());
newCalls.add(newCall);
}
return newCalls;
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
index b7cc9f6..ce5025b 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
@@ -112,15 +112,14 @@ public class ProjectWindowTransposeRule extends RelOptRule {
@Override public RexNode visitCall(final RexCall call) {
if (call instanceof Window.RexWinAggCall) {
+ final Window.RexWinAggCall aggCall = (Window.RexWinAggCall) call;
boolean[] update = {false};
final List<RexNode> clonedOperands = visitList(call.operands, update);
if (update[0]) {
return new Window.RexWinAggCall(
- (SqlAggFunction) call.getOperator(),
- call.getType(),
- clonedOperands,
- ((Window.RexWinAggCall) call).ordinal,
- ((Window.RexWinAggCall) call).distinct);
+ (SqlAggFunction) call.getOperator(), call.getType(),
+ clonedOperands, aggCall.ordinal, aggCall.distinct,
+ aggCall.ignoreNulls);
} else {
return call;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
index e55c417..88eacba 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
@@ -491,43 +491,41 @@ public abstract class ReduceExpressionsRule extends RelOptRule {
final List<Window.Group> groups = new ArrayList<>();
for (Window.Group group : window.groups) {
List<Window.RexWinAggCall> aggCalls = new ArrayList<>();
- for (Window.RexWinAggCall rexWinAggCall : group.aggCalls) {
- final List<RexNode> expList = new ArrayList<>(
- rexWinAggCall.getOperands());
- boolean opReduced = reduceExpressions(window, expList, predicates);
- aggCalls.add(opReduced
- ?
- new Window.RexWinAggCall(
- (SqlAggFunction) rexWinAggCall.getOperator(),
- rexWinAggCall.type, expList, rexWinAggCall.ordinal,
- rexWinAggCall.distinct)
- :
- rexWinAggCall);
- reduced |= opReduced;
+ for (Window.RexWinAggCall aggCall : group.aggCalls) {
+ final List<RexNode> expList = new ArrayList<>(aggCall.getOperands());
+ if (reduceExpressions(window, expList, predicates)) {
+ aggCall = new Window.RexWinAggCall(
+ (SqlAggFunction) aggCall.getOperator(), aggCall.type, expList,
+ aggCall.ordinal, aggCall.distinct, aggCall.ignoreNulls);
+ reduced = true;
+ }
+ aggCalls.add(aggCall);
}
final ImmutableBitSet.Builder keyBuilder = ImmutableBitSet.builder();
- group.keys.asList().stream().filter(key
- -> !predicates.constantMap.containsKey(rexBuilder.
- makeInputRef(window.getInput(), key))).collect(
- Collectors.toList()).forEach(i -> keyBuilder.set(i));
- ImmutableBitSet keys = keyBuilder.build();
+ group.keys.asList().stream()
+ .filter(key ->
+ !predicates.constantMap.containsKey(
+ rexBuilder.makeInputRef(window.getInput(), key)))
+ .collect(Collectors.toList())
+ .forEach(i -> keyBuilder.set(i));
+ final ImmutableBitSet keys = keyBuilder.build();
reduced |= keys.cardinality() != group.keys.cardinality();
final List<RelFieldCollation> collationsList = group.orderKeys
- .getFieldCollations().stream().filter(fc -> !predicates.constantMap
- .containsKey(rexBuilder
- .makeInputRef(window.getInput(), fc.getFieldIndex())))
+ .getFieldCollations().stream()
+ .filter(fc ->
+ !predicates.constantMap.containsKey(
+ rexBuilder.makeInputRef(window.getInput(),
+ fc.getFieldIndex())))
.collect(Collectors.toList());
boolean collationReduced =
group.orderKeys.getFieldCollations().size() != collationsList.size();
reduced |= collationReduced;
RelCollation relCollation = collationReduced
- ?
- RelCollations.of(collationsList)
- :
- group.orderKeys;
+ ? RelCollations.of(collationsList)
+ : group.orderKeys;
groups.add(
new Window.Group(keys, group.isRows, group.lowerBound,
group.upperBound, relCollation, aggCalls));
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 641b497..5425ffb 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -327,6 +327,17 @@ public class RexBuilder {
return list;
}
+ @Deprecated // to be removed before 2.0
+ public RexNode makeOver(RelDataType type, SqlAggFunction operator,
+ List<RexNode> exprs, List<RexNode> partitionKeys,
+ ImmutableList<RexFieldCollation> orderKeys,
+ RexWindowBound lowerBound, RexWindowBound upperBound,
+ boolean physical, boolean allowPartial, boolean nullWhenCountZero,
+ boolean distinct) {
+ return makeOver(type, operator, exprs, partitionKeys, orderKeys, lowerBound,
+ upperBound, physical, allowPartial, nullWhenCountZero, distinct, false);
+ }
+
/**
* Creates a call to a windowed agg.
*/
@@ -341,7 +352,8 @@ public class RexBuilder {
boolean physical,
boolean allowPartial,
boolean nullWhenCountZero,
- boolean distinct) {
+ boolean distinct,
+ boolean ignoreNulls) {
assert operator != null;
assert exprs != null;
assert partitionKeys != null;
@@ -353,14 +365,15 @@ public class RexBuilder {
lowerBound,
upperBound,
physical);
- final RexOver over = new RexOver(type, operator, exprs, window, distinct);
+ final RexOver over = new RexOver(type, operator, exprs, window,
+ distinct, ignoreNulls);
RexNode result = over;
// This should be correct but need time to go over test results.
// Also want to look at combing with section below.
if (nullWhenCountZero) {
- final RelDataType bigintType = getTypeFactory().createSqlType(
- SqlTypeName.BIGINT);
+ final RelDataType bigintType =
+ typeFactory.createSqlType(SqlTypeName.BIGINT);
result = makeCall(
SqlStdOperatorTable.CASE,
makeCall(
@@ -370,21 +383,22 @@ public class RexBuilder {
SqlStdOperatorTable.COUNT,
exprs,
window,
- distinct),
+ distinct,
+ ignoreNulls),
makeLiteral(
BigDecimal.ZERO,
bigintType,
SqlTypeName.DECIMAL)),
ensureType(type, // SUM0 is non-nullable, thus need a cast
new RexOver(typeFactory.createTypeWithNullability(type, false),
- operator, exprs, window, distinct),
+ operator, exprs, window, distinct, ignoreNulls),
false),
makeCast(type, constantNull()));
}
if (!allowPartial) {
Preconditions.checkArgument(physical, "DISALLOW PARTIAL over RANGE");
- final RelDataType bigintType = getTypeFactory().createSqlType(
- SqlTypeName.BIGINT);
+ final RelDataType bigintType =
+ typeFactory.createSqlType(SqlTypeName.BIGINT);
// todo: read bound
result =
makeCall(
@@ -396,7 +410,8 @@ public class RexBuilder {
SqlStdOperatorTable.COUNT,
ImmutableList.of(),
window,
- distinct),
+ distinct,
+ ignoreNulls),
makeLiteral(
BigDecimal.valueOf(2),
bigintType,
@@ -642,10 +657,8 @@ public class RexBuilder {
multiplier, divider);
if (scale > 0) {
RelDataType decimalType =
- getTypeFactory().createSqlType(
- SqlTypeName.DECIMAL,
- scale + exp.getType().getPrecision(),
- scale);
+ typeFactory.createSqlType(SqlTypeName.DECIMAL,
+ scale + exp.getType().getPrecision(), scale);
value = encodeIntervalOrDecimal(value, decimalType, false);
}
return ensureType(toType, value, false);
@@ -1089,9 +1102,7 @@ public class RexBuilder {
boolean typeNullability = type.isNullable();
boolean valueNullability = value.getType().isNullable();
if (typeNullability != valueNullability) {
- return getTypeFactory().createTypeWithNullability(
- type,
- valueNullability);
+ return typeFactory.createTypeWithNullability(type, valueNullability);
}
return type;
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexOver.java b/core/src/main/java/org/apache/calcite/rex/RexOver.java
index 806495e..78a6a37 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexOver.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexOver.java
@@ -38,6 +38,7 @@ public class RexOver extends RexCall {
private final RexWindow window;
private final boolean distinct;
+ private final boolean ignoreNulls;
//~ Constructors -----------------------------------------------------------
@@ -65,11 +66,13 @@ public class RexOver extends RexCall {
SqlAggFunction op,
List<RexNode> operands,
RexWindow window,
- boolean distinct) {
+ boolean distinct,
+ boolean ignoreNulls) {
super(type, op, operands);
Preconditions.checkArgument(op.isAggregator());
this.window = Objects.requireNonNull(window);
this.distinct = distinct;
+ this.ignoreNulls = ignoreNulls;
}
//~ Methods ----------------------------------------------------------------
@@ -89,6 +92,10 @@ public class RexOver extends RexCall {
return distinct;
}
+ public boolean ignoreNulls() {
+ return ignoreNulls;
+ }
+
@Override protected @Nonnull String computeDigest(boolean withType) {
final StringBuilder sb = new StringBuilder(op.getName());
sb.append("(");
@@ -97,6 +104,9 @@ public class RexOver extends RexCall {
}
appendOperands(sb);
sb.append(")");
+ if (ignoreNulls) {
+ sb.append(" IGNORE NULLS");
+ }
if (withType) {
sb.append(":");
sb.append(type.getFullTypeString());
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 b1f1593..eee8d57 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
@@ -49,7 +49,8 @@ public class RexShuttle implements RexVisitor<RexNode> {
over.getAggOperator(),
clonedOperands,
window,
- over.isDistinct());
+ over.isDistinct(),
+ over.ignoreNulls());
} else {
return over;
}
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 53b2480..66244ee 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -308,6 +308,9 @@ public interface CalciteResource {
@BaseMessage("Window ''{0}'' not found")
ExInst<SqlValidatorException> windowNotFound(String a0);
+ @BaseMessage("Cannot specify IGNORE NULLS or RESPECT NULLS following ''{0}''")
+ ExInst<SqlValidatorException> disallowsNullTreatment(String a0);
+
@BaseMessage("Expression ''{0}'' is not being grouped")
ExInst<SqlValidatorException> notGroupExpr(String a0);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
index cca0ad0..6e64059 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
@@ -182,6 +182,12 @@ public abstract class SqlAggFunction extends SqlFunction implements Context {
public boolean allowsFilter() {
return true;
}
+
+ /** Returns whether this aggregate function allows specifying null treatment
+ * ({@code RESPECT NULLS} or {@code IGNORE NULLS}). */
+ public boolean allowsNullTreatment() {
+ return false;
+ }
}
// End SqlAggFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java
index 8215dca..b75fcc1 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java
@@ -41,7 +41,7 @@ public class SqlFilterOperator extends SqlBinaryOperator {
//~ Constructors -----------------------------------------------------------
public SqlFilterOperator() {
- super("FILTER", SqlKind.FILTER, 2, true, ReturnTypes.ARG0_FORCE_NULLABLE,
+ super("FILTER", SqlKind.FILTER, 20, true, ReturnTypes.ARG0_FORCE_NULLABLE,
null, OperandTypes.ANY_ANY);
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 9f0a735..eeda717 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -213,6 +213,16 @@ public enum SqlKind {
OVER,
/**
+ * RESPECT NULLS operator
+ */
+ RESPECT_NULLS("RESPECT NULLS"),
+
+ /**
+ * IGNORE NULLS operator
+ */
+ IGNORE_NULLS("IGNORE NULLS"),
+
+ /**
* FILTER operator
*/
FILTER,
@@ -1194,6 +1204,7 @@ public enum SqlKind {
concat(
EnumSet.of(AS, ARGUMENT_ASSIGNMENT, DEFAULT,
RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
+ FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
SELECT, JOIN, OTHER_FUNCTION, POSITION, CAST, TRIM, FLOOR, CEIL,
TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT,
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java
new file mode 100644
index 0000000..df6c6d9
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java
@@ -0,0 +1,67 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.sql;
+
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+
+import com.google.common.base.Preconditions;
+
+import static org.apache.calcite.util.Static.RESOURCE;
+
+/**
+ * An operator that decides how to handle null input
+ * ({@code RESPECT NULLS} and {@code IGNORE NULLS}).
+ *
+ * <p>Currently, only the windowed aggregate functions {@code FIRST_VALUE},
+ * {@code LAST_VALUE}, {@code LEAD} and {@code LAG} support it.
+ *
+ * @see SqlAggFunction#allowsNullTreatment()
+ */
+public class SqlNullTreatmentOperator extends SqlSpecialOperator {
+ public SqlNullTreatmentOperator(SqlKind kind) {
+ super(kind.sql, kind, 20, true, ReturnTypes.ARG0, null, OperandTypes.ANY);
+ Preconditions.checkArgument(kind == SqlKind.RESPECT_NULLS
+ || kind == SqlKind.IGNORE_NULLS);
+ }
+
+ @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
+ int rightPrec) {
+ assert call.operandCount() == 1;
+ call.operand(0).unparse(writer, getLeftPrec(), getRightPrec());
+ writer.keyword(getName());
+ }
+
+ public void validateCall(
+ SqlCall call,
+ SqlValidator validator,
+ SqlValidatorScope scope,
+ SqlValidatorScope operandScope) {
+ assert call.getOperator() == this;
+ assert call.operandCount() == 1;
+ SqlCall aggCall = call.operand(0);
+ if (!aggCall.getOperator().isAggregator()
+ || !((SqlAggFunction) aggCall.getOperator()).allowsNullTreatment()) {
+ throw validator.newValidationError(aggCall,
+ RESOURCE.disallowsNullTreatment(aggCall.getOperator().getName()));
+ }
+ }
+}
+
+// End SqlNullTreatmentOperator.java
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 23ebbc4..9450728 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlOverOperator.java
@@ -65,6 +65,12 @@ public class SqlOverOperator extends SqlBinaryOperator {
assert call.getOperator() == this;
assert call.operandCount() == 2;
SqlCall aggCall = call.operand(0);
+ switch (aggCall.getKind()) {
+ case RESPECT_NULLS:
+ case IGNORE_NULLS:
+ validator.validateCall(aggCall, scope);
+ aggCall = aggCall.operand(0);
+ }
if (!aggCall.getOperator().isAggregator()) {
throw validator.newValidationError(aggCall, RESOURCE.overNonAggregate());
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
index 652886f..da843f3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
@@ -119,7 +119,7 @@ public interface SqlSplittableAggFunction {
public AggregateCall other(RelDataTypeFactory typeFactory, AggregateCall e) {
return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false,
- ImmutableIntList.of(), -1, RelCollations.EMPTY,
+ false, ImmutableIntList.of(), -1, RelCollations.EMPTY,
typeFactory.createSqlType(SqlTypeName.BIGINT), null);
}
@@ -148,7 +148,7 @@ public interface SqlSplittableAggFunction {
}
int ordinal = extra.register(node);
return AggregateCall.create(SqlStdOperatorTable.SUM0, false, false,
- ImmutableList.of(ordinal), -1, aggregateCall.collation,
+ false, ImmutableList.of(ordinal), -1, aggregateCall.collation,
aggregateCall.type, aggregateCall.name);
}
@@ -232,6 +232,7 @@ public interface SqlSplittableAggFunction {
public AggregateCall other(RelDataTypeFactory typeFactory, AggregateCall e) {
return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false,
+ false,
ImmutableIntList.of(), -1,
RelCollations.EMPTY,
typeFactory.createSqlType(SqlTypeName.BIGINT), null);
@@ -264,7 +265,7 @@ public interface SqlSplittableAggFunction {
}
int ordinal = extra.register(node);
return AggregateCall.create(getMergeAggFunctionOfTopSplit(), false, false,
- ImmutableList.of(ordinal), -1, aggregateCall.collation,
+ false, ImmutableList.of(ordinal), -1, aggregateCall.collation,
aggregateCall.type, aggregateCall.name);
}
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlFirstLastValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlFirstLastValueAggFunction.java
index 397acf8..04012d7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlFirstLastValueAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlFirstLastValueAggFunction.java
@@ -74,6 +74,10 @@ public class SqlFirstLastValueAggFunction extends SqlAggFunction {
return typeFactory.createTypeWithNullability(
typeFactory.createSqlType(SqlTypeName.ANY), true);
}
+
+ @Override public boolean allowsNullTreatment() {
+ return true;
+ }
}
// End SqlFirstLastValueAggFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLeadLagAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLeadLagAggFunction.java
index 55dc0cf..288ee95 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLeadLagAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLeadLagAggFunction.java
@@ -94,6 +94,10 @@ public class SqlLeadLagAggFunction extends SqlAggFunction {
return false;
}
+ @Override public boolean allowsNullTreatment() {
+ return true;
+ }
+
}
// End SqlLeadLagAggFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java
index 6e66653..5549683 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java
@@ -34,6 +34,10 @@ public class SqlNthValueAggFunction extends SqlAggFunction {
null, OperandTypes.ANY_NUMERIC, SqlFunctionCategory.NUMERIC, false,
true, Optionality.FORBIDDEN);
}
+
+ @Override public boolean allowsNullTreatment() {
+ return true;
+ }
}
// End SqlNthValueAggFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 5e89b88..3d57072 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -32,6 +32,7 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLateralOperator;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNullTreatmentOperator;
import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperandCountRange;
import org.apache.calcite.sql.SqlOperator;
@@ -1170,6 +1171,14 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
//-------------------------------------------------------------
public static final SqlRowOperator ROW = new SqlRowOperator("ROW");
+ /** <code>IGNORE NULLS</code> operator. */
+ public static final SqlNullTreatmentOperator IGNORE_NULLS =
+ new SqlNullTreatmentOperator(SqlKind.IGNORE_NULLS);
+
+ /** <code>RESPECT NULLS</code> operator. */
+ public static final SqlNullTreatmentOperator RESPECT_NULLS =
+ new SqlNullTreatmentOperator(SqlKind.RESPECT_NULLS);
+
/**
* A special operator for the subtraction of two DATETIMEs. The format of
* DATETIME subtraction is:
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
index e14f2ca..864bb80 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
@@ -148,11 +148,11 @@ class AggChecker extends SqlBasicVisitor<Void> {
// BY deptno'
return null;
}
- if (call.getKind() == SqlKind.FILTER) {
- call.operand(0).accept(this);
- return null;
- }
- if (call.getKind() == SqlKind.WITHIN_GROUP) {
+ switch (call.getKind()) {
+ case FILTER:
+ case WITHIN_GROUP:
+ case RESPECT_NULLS:
+ case IGNORE_NULLS:
call.operand(0).accept(this);
return null;
}
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 d746ba9..f462804 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1121,10 +1121,10 @@ public class SqlToRelConverter {
LogicalAggregate.create(seek, ImmutableBitSet.of(), null,
ImmutableList.of(
AggregateCall.create(SqlStdOperatorTable.COUNT, false,
- false, ImmutableList.of(), -1, RelCollations.EMPTY,
+ false, false, ImmutableList.of(), -1, RelCollations.EMPTY,
longType, null),
AggregateCall.create(SqlStdOperatorTable.COUNT, false,
- false, args, -1, RelCollations.EMPTY, longType, null)));
+ false, false, args, -1, RelCollations.EMPTY, longType, null)));
LogicalJoin join =
LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true),
ImmutableSet.of(), JoinRelType.INNER);
@@ -1857,6 +1857,15 @@ public class SqlToRelConverter {
private RexNode convertOver(Blackboard bb, SqlNode node) {
SqlCall call = (SqlCall) node;
SqlCall aggCall = call.operand(0);
+ boolean ignoreNulls = false;
+ switch (aggCall.getKind()) {
+ case IGNORE_NULLS:
+ ignoreNulls = true;
+ // fall through
+ case RESPECT_NULLS:
+ aggCall = aggCall.operand(0);
+ }
+
SqlNode windowOrRef = call.operand(1);
final SqlWindow window =
validator.resolveWindow(windowOrRef, bb.scope, true);
@@ -1919,7 +1928,8 @@ public class SqlToRelConverter {
RexWindowBound.create(window.getLowerBound(), lowerBound),
RexWindowBound.create(window.getUpperBound(), upperBound),
window,
- isDistinct);
+ isDistinct,
+ ignoreNulls);
RexNode overNode = rexAgg.accept(visitor);
return overNode;
@@ -3999,10 +4009,6 @@ public class SqlToRelConverter {
LogicalUnion.create(unionRels, true),
true);
}
-
- // REVIEW jvs 22-Jan-2004: should I add
- // mapScopeToLux.put(validator.getScope(values),bb.root);
- // ?
}
//~ Inner Classes ----------------------------------------------------------
@@ -5003,21 +5009,30 @@ public class SqlToRelConverter {
}
private void translateAgg(SqlCall call) {
- translateAgg(call, null, null, call);
+ translateAgg(call, null, null, false, call);
}
private void translateAgg(SqlCall call, SqlNode filter,
- SqlNodeList orderList, SqlCall outerCall) {
+ SqlNodeList orderList, boolean ignoreNulls, SqlCall outerCall) {
assert bb.agg == this;
assert outerCall != null;
switch (call.getKind()) {
case FILTER:
assert filter == null;
- translateAgg(call.operand(0), call.operand(1), orderList, outerCall);
+ translateAgg(call.operand(0), call.operand(1), orderList, ignoreNulls,
+ outerCall);
return;
case WITHIN_GROUP:
assert orderList == null;
- translateAgg(call.operand(0), filter, call.operand(1), outerCall);
+ translateAgg(call.operand(0), filter, call.operand(1), ignoreNulls,
+ outerCall);
+ return;
+ case IGNORE_NULLS:
+ ignoreNulls = true;
+ // fall through
+ case RESPECT_NULLS:
+ translateAgg(call.operand(0), filter, orderList, ignoreNulls,
+ outerCall);
return;
}
final List<Integer> args = new ArrayList<>();
@@ -5100,6 +5115,7 @@ public class SqlToRelConverter {
aggFunction,
distinct,
approximate,
+ ignoreNulls,
args,
filterArg,
collation,
@@ -5253,19 +5269,22 @@ public class SqlToRelConverter {
private final RexWindowBound upperBound;
private final SqlWindow window;
private final boolean distinct;
+ private final boolean ignoreNulls;
HistogramShuttle(
List<RexNode> partitionKeys,
ImmutableList<RexFieldCollation> orderKeys,
RexWindowBound lowerBound, RexWindowBound upperBound,
SqlWindow window,
- boolean distinct) {
+ boolean distinct,
+ boolean ignoreNulls) {
this.partitionKeys = partitionKeys;
this.orderKeys = orderKeys;
this.lowerBound = lowerBound;
this.upperBound = upperBound;
this.window = window;
this.distinct = distinct;
+ this.ignoreNulls = ignoreNulls;
}
public RexNode visitCall(RexCall call) {
@@ -5322,7 +5341,8 @@ public class SqlToRelConverter {
window.isRows(),
window.isAllowPartial(),
false,
- distinct);
+ distinct,
+ ignoreNulls);
RexNode histogramCall =
rexBuilder.makeCall(
@@ -5363,7 +5383,8 @@ public class SqlToRelConverter {
window.isRows(),
window.isAllowPartial(),
needSum0,
- distinct);
+ distinct,
+ ignoreNulls);
}
}
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 68492c9..c93567b 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -781,21 +781,21 @@ public class RelBuilder {
@Deprecated // to be removed before 2.0
public AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct,
RexNode filter, String alias, RexNode... operands) {
- return aggregateCall(aggFunction, distinct, false, filter,
+ return aggregateCall(aggFunction, distinct, false, false, filter,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@Deprecated // to be removed before 2.0
public AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct,
boolean approximate, RexNode filter, String alias, RexNode... operands) {
- return aggregateCall(aggFunction, distinct, approximate, filter,
+ return aggregateCall(aggFunction, distinct, approximate, false, filter,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@Deprecated // to be removed before 2.0
public AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct,
RexNode filter, String alias, Iterable<? extends RexNode> operands) {
- return aggregateCall(aggFunction, distinct, false, filter,
+ return aggregateCall(aggFunction, distinct, false, false, filter,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@@ -803,7 +803,7 @@ public class RelBuilder {
public AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct,
boolean approximate, RexNode filter, String alias,
Iterable<? extends RexNode> operands) {
- return aggregateCall(aggFunction, distinct, approximate, filter,
+ return aggregateCall(aggFunction, distinct, approximate, false, filter,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@@ -817,7 +817,7 @@ public class RelBuilder {
* {@link AggCall#as} to the result. */
public AggCall aggregateCall(SqlAggFunction aggFunction,
Iterable<? extends RexNode> operands) {
- return aggregateCall(aggFunction, false, false, null, ImmutableList.of(),
+ return aggregateCall(aggFunction, false, false, false, null, ImmutableList.of(),
null, ImmutableList.copyOf(operands));
}
@@ -831,16 +831,16 @@ public class RelBuilder {
* {@link AggCall#as} to the result. */
public AggCall aggregateCall(SqlAggFunction aggFunction,
RexNode... operands) {
- return aggregateCall(aggFunction, false, false, null, ImmutableList.of(),
+ return aggregateCall(aggFunction, false, false, false, null, ImmutableList.of(),
null, ImmutableList.copyOf(operands));
}
/** Creates a call to an aggregate function with all applicable operands. */
protected AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct,
- boolean approximate, RexNode filter, ImmutableList<RexNode> orderKeys,
+ boolean approximate, boolean ignoreNulls, RexNode filter, ImmutableList<RexNode> orderKeys,
String alias, ImmutableList<RexNode> operands) {
- return new AggCallImpl(aggFunction, distinct, approximate, filter, alias,
- operands, orderKeys);
+ return new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeys);
}
/** Creates a call to the {@code COUNT} aggregate function. */
@@ -856,7 +856,7 @@ public class RelBuilder {
/** Creates a call to the {@code COUNT} aggregate function,
* optionally distinct and with an alias. */
public AggCall count(boolean distinct, String alias, RexNode... operands) {
- return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, null,
+ return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, false, null,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@@ -864,7 +864,7 @@ public class RelBuilder {
* optionally distinct and with an alias. */
public AggCall count(boolean distinct, String alias,
Iterable<? extends RexNode> operands) {
- return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, null,
+ return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, false, null,
ImmutableList.of(), alias, ImmutableList.copyOf(operands));
}
@@ -881,7 +881,7 @@ public class RelBuilder {
/** Creates a call to the {@code SUM} aggregate function,
* optionally distinct and with an alias. */
public AggCall sum(boolean distinct, String alias, RexNode operand) {
- return aggregateCall(SqlStdOperatorTable.SUM, distinct, false, null,
+ return aggregateCall(SqlStdOperatorTable.SUM, distinct, false, false, null,
ImmutableList.of(), alias, ImmutableList.of(operand));
}
@@ -893,7 +893,7 @@ public class RelBuilder {
/** Creates a call to the {@code AVG} aggregate function,
* optionally distinct and with an alias. */
public AggCall avg(boolean distinct, String alias, RexNode operand) {
- return aggregateCall(SqlStdOperatorTable.AVG, distinct, false, null,
+ return aggregateCall(SqlStdOperatorTable.AVG, distinct, false, false, null,
ImmutableList.of(), alias, ImmutableList.of(operand));
}
@@ -905,7 +905,7 @@ public class RelBuilder {
/** Creates a call to the {@code MIN} aggregate function,
* optionally with an alias. */
public AggCall min(String alias, RexNode operand) {
- return aggregateCall(SqlStdOperatorTable.MIN, false, false, null,
+ return aggregateCall(SqlStdOperatorTable.MIN, false, false, false, null,
ImmutableList.of(), alias, ImmutableList.of(operand));
}
@@ -917,7 +917,7 @@ public class RelBuilder {
/** Creates a call to the {@code MAX} aggregate function. */
public AggCall max(String alias, RexNode operand) {
- return aggregateCall(SqlStdOperatorTable.MAX, false, false, null,
+ return aggregateCall(SqlStdOperatorTable.MAX, false, false, false, null,
ImmutableList.of(), alias, ImmutableList.of(operand));
}
@@ -1564,7 +1564,8 @@ public class RelBuilder {
.collect(Collectors.toList()));
aggregateCall =
AggregateCall.create(aggCall1.aggFunction, aggCall1.distinct,
- aggCall1.approximate, args, filterArg, collation,
+ aggCall1.approximate,
+ aggCall1.ignoreNulls, args, filterArg, collation,
groupSet.cardinality(), r, null, aggCall1.alias);
} else {
aggregateCall = ((AggCallImpl2) aggCall).aggregateCall;
@@ -2325,6 +2326,9 @@ public class RelBuilder {
* if {@code approximate} is true. */
AggCall approximate(boolean approximate);
+ /** Returns a copy of this AggCall that ignores nulls. */
+ AggCall ignoreNulls(boolean ignoreNulls);
+
/** Returns a copy of this AggCall with a given alias. */
AggCall as(String alias);
@@ -2377,18 +2381,20 @@ public class RelBuilder {
private final SqlAggFunction aggFunction;
private final boolean distinct;
private final boolean approximate;
+ private final boolean ignoreNulls;
private final RexNode filter; // may be null
private final String alias; // may be null
private final ImmutableList<RexNode> operands; // may be empty, never null
private final ImmutableList<RexNode> orderKeys; // may be empty, never null
AggCallImpl(SqlAggFunction aggFunction, boolean distinct,
- boolean approximate, RexNode filter,
+ boolean approximate, boolean ignoreNulls, RexNode filter,
String alias, ImmutableList<RexNode> operands,
ImmutableList<RexNode> orderKeys) {
this.aggFunction = Objects.requireNonNull(aggFunction);
this.distinct = distinct;
this.approximate = approximate;
+ this.ignoreNulls = ignoreNulls;
this.alias = alias;
this.operands = Objects.requireNonNull(operands);
this.orderKeys = Objects.requireNonNull(orderKeys);
@@ -2408,8 +2414,8 @@ public class RelBuilder {
ImmutableList.copyOf(orderKeys);
return orderKeyList.equals(this.orderKeys)
? this
- : new AggCallImpl(aggFunction, distinct, approximate, filter,
- alias, operands, orderKeyList);
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeyList);
}
public AggCall sort(RexNode... orderKeys) {
@@ -2419,34 +2425,41 @@ public class RelBuilder {
public AggCall approximate(boolean approximate) {
return approximate == this.approximate
? this
- : new AggCallImpl(aggFunction, distinct, approximate, filter,
- alias, operands, orderKeys);
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeys);
}
public AggCall filter(RexNode condition) {
return Objects.equals(condition, this.filter)
? this
- : new AggCallImpl(aggFunction, distinct, approximate, condition,
- alias, operands, orderKeys);
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ condition, alias, operands, orderKeys);
}
public AggCall as(String alias) {
return Objects.equals(alias, this.alias)
? this
- : new AggCallImpl(aggFunction, distinct, approximate, filter,
- alias, operands, orderKeys);
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeys);
}
public AggCall distinct(boolean distinct) {
return distinct == this.distinct
? this
- : new AggCallImpl(aggFunction, distinct, approximate, filter,
- alias, operands, orderKeys);
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeys);
}
public AggCall distinct() {
return distinct(true);
}
+
+ public AggCall ignoreNulls(boolean ignoreNulls) {
+ return ignoreNulls == this.ignoreNulls
+ ? this
+ : new AggCallImpl(aggFunction, distinct, approximate, ignoreNulls,
+ filter, alias, operands, orderKeys);
+ }
}
/** Implementation of {@link AggCall} that wraps an
@@ -2485,6 +2498,10 @@ public class RelBuilder {
public AggCall distinct() {
throw new UnsupportedOperationException();
}
+
+ public AggCall ignoreNulls(boolean ignoreNulls) {
+ throw new UnsupportedOperationException();
+ }
}
/** Collects the extra expressions needed for {@link #aggregate}.
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index d92b76a..2afd902 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -103,6 +103,7 @@ NaturalDisallowsOnOrUsing=Cannot specify NATURAL keyword with ON or USING clause
ColumnInUsingNotUnique=Column name ''{0}'' in USING clause is not unique on one side of join
NaturalOrUsingColumnNotCompatible=Column ''{0}'' matched using NATURAL keyword or USING clause has incompatible types: cannot compare ''{1}'' to ''{2}''
WindowNotFound=Window ''{0}'' not found
+DisallowsNullTreatment=Cannot specify IGNORE NULLS or RESPECT NULLS following ''{0}''
NotGroupExpr=Expression ''{0}'' is not being grouped
GroupingArgument=Argument to {0} operator must be a grouped expression
GroupingInAggregate={0} operator may only occur in an aggregate query
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 1cc182e..415292f 100644
--- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java
@@ -325,10 +325,10 @@ public class RelWriterTest {
LogicalAggregate.create(filter, ImmutableBitSet.of(0), null,
ImmutableList.of(
AggregateCall.create(SqlStdOperatorTable.COUNT,
- true, false, ImmutableList.of(1), -1,
+ true, false, false, ImmutableList.of(1), -1,
RelCollations.EMPTY, bigIntType, "c"),
AggregateCall.create(SqlStdOperatorTable.COUNT,
- false, false, ImmutableList.of(), -1,
+ false, false, false, ImmutableList.of(), -1,
RelCollations.EMPTY, bigIntType, "d")));
aggregate.explain(writer);
return writer.asString();
@@ -368,7 +368,7 @@ public class RelWriterTest {
SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO), null),
RexWindowBound.create(
SqlWindow.createCurrentRow(SqlParserPos.ZERO), null),
- true, true, false, false),
+ true, true, false, false, false),
rexBuilder.makeOver(bigIntType,
SqlStdOperatorTable.SUM,
ImmutableList.of(rexBuilder.makeInputRef(scan, 0)),
@@ -382,7 +382,7 @@ public class RelWriterTest {
rexBuilder.makeCall(
SqlWindow.FOLLOWING_OPERATOR,
rexBuilder.makeExactLiteral(BigDecimal.ONE))),
- false, true, false, false)),
+ false, true, false, false, false)),
ImmutableList.of("field0", "field1", "field2"));
final RelJsonWriter writer = new RelJsonWriter();
project.explain(writer);
diff --git a/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java b/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
index 11be2c2..6685d2d 100644
--- a/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
+++ b/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java
@@ -165,7 +165,7 @@ public class TraitPropagationTest {
// aggregate on s, count
AggregateCall aggCall = AggregateCall.create(SqlStdOperatorTable.COUNT,
- false, false, Collections.singletonList(1), -1, RelCollations.EMPTY,
+ false, false, false, Collections.singletonList(1), -1, RelCollations.EMPTY,
sqlBigInt, "cnt");
RelNode agg = new LogicalAggregate(cluster,
cluster.traitSetOf(Convention.NONE), project, false,
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 5e00000..c9ce179 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -1613,14 +1613,17 @@ public class SqlParserTest {
}
@Test public void testAggregateFilter() {
- sql("select sum(sal) filter (where gender = 'F') as femaleSal,\n"
+ final String sql = "select\n"
+ + " sum(sal) filter (where gender = 'F') as femaleSal,\n"
+ " sum(sal) filter (where true) allSal,\n"
+ " count(distinct deptno) filter (where (deptno < 40))\n"
- + "from emp")
- .ok("SELECT (SUM(`SAL`) FILTER (WHERE (`GENDER` = 'F'))) AS `FEMALESAL`,"
- + " (SUM(`SAL`) FILTER (WHERE TRUE)) AS `ALLSAL`,"
- + " (COUNT(DISTINCT `DEPTNO`) FILTER (WHERE (`DEPTNO` < 40)))\n"
- + "FROM `EMP`");
+ + "from emp";
+ final String expected = "SELECT"
+ + " SUM(`SAL`) FILTER (WHERE (`GENDER` = 'F')) AS `FEMALESAL`,"
+ + " SUM(`SAL`) FILTER (WHERE TRUE) AS `ALLSAL`,"
+ + " COUNT(DISTINCT `DEPTNO`) FILTER (WHERE (`DEPTNO` < 40))\n"
+ + "FROM `EMP`";
+ sql(sql).ok(expected);
}
@Test public void testGroup() {
@@ -4249,6 +4252,35 @@ public class SqlParserTest {
+ "FROM `BIDS`");
}
+ @Test public void testNullTreatment() {
+ sql("select lead(x) respect nulls over (w) from t")
+ .ok("SELECT (LEAD(`X`) RESPECT NULLS OVER (`W`))\n"
+ + "FROM `T`");
+ sql("select deptno, sum(sal) respect nulls from emp group by deptno")
+ .ok("SELECT `DEPTNO`, SUM(`SAL`) RESPECT NULLS\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY `DEPTNO`");
+ sql("select deptno, sum(sal) ignore nulls from emp group by deptno")
+ .ok("SELECT `DEPTNO`, SUM(`SAL`) IGNORE NULLS\n"
+ + "FROM `EMP`\n"
+ + "GROUP BY `DEPTNO`");
+ final String sql = "select col1,\n"
+ + " collect(col2) ignore nulls\n"
+ + " within group (order by col3)\n"
+ + " filter (where 1 = 0)\n"
+ + " over (rows 10 preceding)\n"
+ + " as c\n"
+ + "from t\n"
+ + "order by col1 limit 10";
+ final String expected = "SELECT `COL1`, (COLLECT(`COL2`) IGNORE NULLS"
+ + " WITHIN GROUP (ORDER BY `COL3`)"
+ + " FILTER (WHERE (1 = 0)) OVER (ROWS 10 PRECEDING)) AS `C`\n"
+ + "FROM `T`\n"
+ + "ORDER BY `COL1`\n"
+ + "FETCH NEXT 10 ROWS ONLY";
+ sql(sql).ok(expected);
+ }
+
@Test public void testAs() {
// AS is optional for column aliases
check(
@@ -8277,7 +8309,7 @@ public class SqlParserTest {
+ "from t\n"
+ "order by col1 limit 10";
final String expected = "SELECT `COL1`,"
- + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`))\n"
+ + " COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`)\n"
+ "FROM `T`\n"
+ "ORDER BY `COL1`\n"
+ "FETCH NEXT 10 ROWS ONLY";
@@ -8289,7 +8321,7 @@ public class SqlParserTest {
+ "from t\n"
+ "order by col1 limit 10";
final String expected = "SELECT"
- + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`))\n"
+ + " COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`)\n"
+ "FROM `T`\n"
+ "ORDER BY `COL1`\n"
+ "FETCH NEXT 10 ROWS ONLY";
@@ -8308,7 +8340,7 @@ public class SqlParserTest {
+ "from t\n"
+ "order by col1 limit 10";
final String expected = "SELECT `COL1`,"
- + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`, `COL4`))\n"
+ + " COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`, `COL4`)\n"
+ "FROM `T`\n"
+ "ORDER BY `COL1`\n"
+ "FETCH NEXT 10 ROWS ONLY";
@@ -8321,8 +8353,8 @@ public class SqlParserTest {
+ " order by col3 desc nulls first, col4 asc nulls last)\n"
+ "from t\n"
+ "order by col1 limit 10";
- final String expected = "SELECT `COL1`, (COLLECT(`COL2`) "
- + "WITHIN GROUP (ORDER BY `COL3` DESC NULLS FIRST, `COL4` NULLS LAST))\n"
+ final String expected = "SELECT `COL1`, COLLECT(`COL2`) "
+ + "WITHIN GROUP (ORDER BY `COL3` DESC NULLS FIRST, `COL4` NULLS LAST)\n"
+ "FROM `T`\n"
+ "ORDER BY `COL1`\n"
+ "FETCH NEXT 10 ROWS ONLY";
@@ -8486,9 +8518,9 @@ public class SqlParserTest {
@Test public void testJsonArrayAgg2() {
checkExp("json_arrayagg(\"column\" order by \"column\")",
- "(JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY `column`))");
+ "JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY `column`)");
checkExp("json_arrayagg(\"column\") within group (order by \"column\")",
- "(JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY `column`))");
+ "JSON_ARRAYAGG(`column` ABSENT ON NULL) WITHIN GROUP (ORDER BY `column`)");
checkFails("^json_arrayagg(\"column\" order by \"column\") within group (order by \"column\")^",
"(?s).*Including both WITHIN GROUP\\(\\.\\.\\.\\) and inside ORDER BY "
+ "in a single JSON_ARRAYAGG call is not allowed.*");
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index cfd34c9..e4f711a 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -3926,6 +3926,30 @@ public class JdbcTest {
}
/**
+ * Tests LAG function with IGNORE NULLS.
+ */
+ @Test public void testLagIgnoreNulls() {
+ final String sql = "select\n"
+ + " lag(rn, expected, 42) ignore nulls over (w) l,\n"
+ + " lead(rn, expected) over (w),\n"
+ + " lead(rn, expected) over (order by expected)\n"
+ + "from (values"
+ + " (1,0,1),\n"
+ + " (2,0,1),\n"
+ + " (2,0,1),\n"
+ + " (3,1,2),\n"
+ + " (4,0,3),\n"
+ + " (cast(null as int),0,3),\n"
+ + " (5,0,3),\n"
+ + " (6,0,3),\n"
+ + " (7,1,4),\n"
+ + " (8,1,4)) as t(rn,val,expected)\n"
+ + "window w as (order by rn)";
+ CalciteAssert.that().query(sql)
+ .throws_("IGNORE NULLS not supported");
+ }
+
+ /**
* Tests NTILE(2).
*/
@Test public void testNtile1() {
diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
index 2e0fef2..0d04bd2 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -1352,7 +1352,7 @@ public class RelMetadataTest extends SqlToRelTestBase {
ImmutableList.of(),
ImmutableList.of(
AggregateCall.create(SqlStdOperatorTable.COUNT,
- false, false, ImmutableIntList.of(),
+ false, false, false, ImmutableIntList.of(),
-1, RelCollations.EMPTY, 2, join, null, null)));
rowSize = mq.getAverageRowSize(aggregate);
columnSizes = mq.getAverageColumnSizes(aggregate);
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 7d13416..9e21272 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1776,6 +1776,28 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
+ * Tests that a window with specifying null treatment.
+ */
+ @Test public void testOverNullTreatmentWindow() {
+ final String sql = "select\n"
+ + "lead(deptno, 1) over w,\n "
+ + "lead(deptno, 2) ignore nulls over w,\n"
+ + "lead(deptno, 3) respect nulls over w,\n"
+ + "lead(deptno, 1) over w,\n"
+ + "lag(deptno, 2) ignore nulls over w,\n"
+ + "lag(deptno, 2) respect nulls over w,\n"
+ + "first_value(deptno) over w,\n"
+ + "first_value(deptno) ignore nulls over w,\n"
+ + "first_value(deptno) respect nulls over w,\n"
+ + "last_value(deptno) over w,\n"
+ + "last_value(deptno) ignore nulls over w,\n"
+ + "last_value(deptno) respect nulls over w\n"
+ + " from emp\n"
+ + "window w as (order by empno)";
+ sql(sql).ok();
+ }
+
+ /**
* Tests that a window with a FOLLOWING bound becomes BETWEEN CURRENT ROW
* AND FOLLOWING.
*/
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 7905ba5..e7f3691 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1043,7 +1043,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
checkWholeExpFails(
"LOCALTIME()",
"No match found for function signature LOCALTIME..");
- checkExpType("LOCALTIME", "TIME(0) NOT NULL"); // with TZ ?
+ checkExpType("LOCALTIME", "TIME(0) NOT NULL"); // with TZ?
checkWholeExpFails(
"LOCALTIME(-1)",
"Argument to function 'LOCALTIME' must be a positive integer literal");
@@ -1066,7 +1066,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
checkWholeExpFails(
"LOCALTIMESTAMP()",
"No match found for function signature LOCALTIMESTAMP..");
- checkExpType("LOCALTIMESTAMP", "TIMESTAMP(0) NOT NULL"); // with TZ ?
+ checkExpType("LOCALTIMESTAMP", "TIMESTAMP(0) NOT NULL"); // with TZ?
checkWholeExpFails(
"LOCALTIMESTAMP(-1)",
"Argument to function 'LOCALTIMESTAMP' must be a positive integer literal");
@@ -1107,7 +1107,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
checkWholeExpFails(
"current_time()",
"No match found for function signature CURRENT_TIME..");
- checkExpType("current_time", "TIME(0) NOT NULL"); // with TZ ?
+ checkExpType("current_time", "TIME(0) NOT NULL"); // with TZ?
checkWholeExpFails(
"current_time(-1)",
"Argument to function 'CURRENT_TIME' must be a positive integer literal");
@@ -4242,6 +4242,88 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
}
/** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-883">[CALCITE-883]
+ * Give error if the aggregate function don't support null treatment</a>. */
+ @Test public void testWindowFunctionsIgnoreNulls() {
+ winSql("select lead(sal, 4) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lead(sal, 4) IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lag(sal, 4) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lag(sal, 4) IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select first_value(sal) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select first_value(sal) IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select last_value(sal) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select last_value(sal) IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select ^sum(sal)^ IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'SUM'");
+
+ winSql("select ^count(sal)^ IGNORE NULLS over (w)\n"
+ + " from emp window w as (order by empno)").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'COUNT'");
+
+ winSql("select ^avg(sal)^ IGNORE NULLS \n"
+ + " from emp").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'AVG'");
+
+ winSql("select ^abs(sal)^ IGNORE NULLS \n"
+ + " from emp").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'ABS'");
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-883">[CALCITE-883]
+ * Give error if the aggregate function don't support null treatment</a>. */
+ @Test public void testWindowFunctionsRespectNulls() {
+ winSql("select lead(sal, 4) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lead(sal, 4) RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lag(sal, 4) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select lag(sal, 4) RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select first_value(sal) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select first_value(sal) RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select last_value(sal) over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select last_value(sal) RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").ok();
+
+ winSql("select ^sum(sal)^ RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'SUM'");
+
+ winSql("select ^count(sal)^ RESPECT NULLS over (w)\n"
+ + " from emp window w as (order by empno)").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'COUNT'");
+
+ winSql("select ^avg(sal)^ RESPECT NULLS \n"
+ + " from emp").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'AVG'");
+
+ winSql("select ^abs(sal)^ RESPECT NULLS \n"
+ + " from emp").fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'ABS'");
+ }
+
+ /** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-1954">[CALCITE-1954]
* Column from outer join should be null, whether or not it is aliased</a>. */
@Test public void testLeftOuterJoinWithAlias() {
@@ -8887,7 +8969,10 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "=> -\n"
+ "AS -\n"
+ "DESC post\n"
+ + "FILTER left\n"
+ + "IGNORE NULLS -\n"
+ "OVER left\n"
+ + "RESPECT NULLS -\n"
+ "TABLESAMPLE -\n"
+ "\n"
+ "INTERSECT left\n"
@@ -8907,7 +8992,6 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "UNION ALL left\n"
+ "\n"
+ "$throw -\n"
- + "FILTER left\n"
+ "Reinterpret -\n"
+ "TABLE pre\n"
+ "VALUES -\n"
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 25589d2..8496053 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1349,6 +1349,32 @@ LogicalProject(EXPR$0=[LAST_VALUE($7) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED
]]>
</Resource>
</TestCase>
+ <TestCase name="testOverNullTreatmentWindow">
+ <Resource name="sql">
+ <![CDATA[select
+lead(deptno, 1) over w,
+ lead(deptno, 2) ignore nulls over w,
+lead(deptno, 3) respect nulls over w,
+lead(deptno, 1) over w,
+lag(deptno, 2) ignore nulls over w,
+lag(deptno, 2) respect nulls over w,
+first_value(deptno) over w,
+first_value(deptno) ignore nulls over w,
+first_value(deptno) respect nulls over w,
+last_value(deptno) over w,
+last_value(deptno) ignore nulls over w,
+last_value(deptno) respect nulls over w
+ from emp
+window w as (order by empno)
+]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[LEAD($7, 1) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$1=[LEAD($7, 2) IGNORE NULLS OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$2=[LEAD($7, 3) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$3=[LEAD($7, 1) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$4=[LAG($7, 2) IGNORE NULLS OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) [...]
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testOverOrderFollowingWindow">
<Resource name="sql">
<![CDATA[select
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 895a968..731cb46 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -220,13 +220,13 @@ groupItem:
| ROLLUP '(' expression [, expression ]* ')'
| GROUPING SETS '(' groupItem [, groupItem ]* ')'
-windowRef:
+window:
windowName
| windowSpec
windowSpec:
- [ windowName ]
'('
+ [ windowName ]
[ ORDER BY orderItem [, orderItem ]* ]
[ PARTITION BY expression [, expression ]* ]
[
@@ -531,6 +531,7 @@ HIERARCHY,
**HOLD**,
**HOUR**,
**IDENTITY**,
+IGNORE,
IMMEDIATE,
IMMEDIATELY,
IMPLEMENTATION,
@@ -743,6 +744,7 @@ RELATIVE,
REPEATABLE,
REPLACE,
**RESET**,
+RESPECT,
RESTART,
RESTRICT,
**RESULT**,
@@ -1513,6 +1515,9 @@ aggregateCall:
| agg(*) [ FILTER (WHERE condition) ]
{% endhighlight %}
+where *agg* is one of the operators in the following table, or a user-defined
+aggregate function.
+
If `FILTER` is present, the aggregate function only considers rows for which
*condition* evaluates to TRUE.
@@ -1531,7 +1536,7 @@ and `LISTAGG`).
| COLLECT( [ ALL | DISTINCT ] value) | Returns a multiset of the values
| COUNT( [ ALL | DISTINCT ] value [, value ]*) | Returns the number of input rows for which *value* is not null (wholly not null if *value* is composite)
| COUNT(*) | Returns the number of input rows
-| FUSION( multiset ) | Returns the multiset union of *multiset* across all input values
+| FUSION(multiset) | Returns the multiset union of *multiset* across all input values
| APPROX_COUNT_DISTINCT(value [, value ]*) | Returns the approximate number of distinct values of *value*; the database is allowed to use an approximation but is not required to
| AVG( [ ALL | DISTINCT ] numeric) | Returns the average (arithmetic mean) of *numeric* across all input values
| SUM( [ ALL | DISTINCT ] numeric) | Returns the sum of *numeric* across all input values
@@ -1562,6 +1567,26 @@ Not implemented:
### Window functions
+Syntax:
+
+{% highlight sql %}
+windowedAggregateCall:
+ agg( [ ALL | DISTINCT ] value [, value ]*)
+ [ RESPECT NULLS | IGNORE NULLS ]
+ [ WITHIN GROUP (ORDER BY orderItem [, orderItem ]*) ]
+ [ FILTER (WHERE condition) ]
+ OVER window
+ | agg(*)
+ [ FILTER (WHERE condition) ]
+ OVER window
+{% endhighlight %}
+
+where *agg* is one of the operators in the following table, or a user-defined
+aggregate function.
+
+`DISTINCT`, `FILTER` and `WITHIN GROUP` are as described for aggregate
+functions.
+
| Operator syntax | Description
|:----------------------------------------- |:-----------
| COUNT(value [, value ]*) OVER window | Returns the number of rows in *window* for which *value* is not null (wholly not null if *value* is composite)
@@ -1580,15 +1605,19 @@ Not implemented:
| NTH_VALUE(value, nth) OVER window | Returns *value* evaluated at the row that is the *n*th row of the window frame
| NTILE(value) OVER window | Returns an integer ranging from 1 to *value*, dividing the partition as equally as possible
+Note:
+
+* You may specify null treatment (`IGNORE NULLS`, `RESPECT NULLS`) for
+ `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`, `LEAD` and `LAG` functions. The
+ syntax handled by the parser, but only `RESPECT NULLS` is implemented at
+ runtime.
+
Not implemented:
* COUNT(DISTINCT value [, value ]*) OVER window
* APPROX_COUNT_DISTINCT(value [, value ]*) OVER window
-* FIRST_VALUE(value) IGNORE NULLS OVER window
-* LAST_VALUE(value) IGNORE NULLS OVER window
* PERCENT_RANK(value) OVER window
* CUME_DIST(value) OVER window
-* NTH_VALUE(value, nth) [ FROM { FIRST | LAST } ] IGNORE NULLS OVER window
### Grouping functions