http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.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 3c0b527..8215dca 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java @@ -69,13 +69,18 @@ public class SqlFilterOperator extends SqlBinaryOperator { SqlValidatorScope operandScope) { assert call.getOperator() == this; assert call.operandCount() == 2; - SqlCall aggCall = call.operand(0); + SqlCall aggCall = getAggCall(call); if (!aggCall.getOperator().isAggregator()) { throw validator.newValidationError(aggCall, RESOURCE.filterNonAggregate()); } final SqlNode condition = call.operand(1); - validator.validateAggregateParams(aggCall, condition, scope); + SqlNodeList orderList = null; + if (hasWithinGroupCall(call)) { + SqlCall withinGroupCall = getWithinGroupCall(call); + orderList = withinGroupCall.operand(1); + } + validator.validateAggregateParams(aggCall, condition, orderList, scope); final RelDataType type = validator.deriveType(scope, condition); if (!SqlTypeUtil.inBooleanFamily(type)) { @@ -92,14 +97,7 @@ public class SqlFilterOperator extends SqlBinaryOperator { validateOperands(validator, scope, call); // Assume the first operand is an aggregate call and derive its type. - SqlNode agg = call.operand(0); - - if (!(agg instanceof SqlCall)) { - throw new IllegalStateException("Argument to SqlOverOperator" - + " should be SqlCall, got " + agg.getClass() + ": " + agg); - } - - final SqlCall aggCall = (SqlCall) agg; + final SqlCall aggCall = getAggCall(call); // Pretend that group-count is 0. This tells the aggregate function that it // might be invoked with 0 rows in a group. Most aggregate functions will @@ -114,9 +112,36 @@ public class SqlFilterOperator extends SqlBinaryOperator { // Copied from validateOperands ((SqlValidatorImpl) validator).setValidatedNodeType(call, ret); - ((SqlValidatorImpl) validator).setValidatedNodeType(agg, ret); + ((SqlValidatorImpl) validator).setValidatedNodeType(aggCall, ret); + if (hasWithinGroupCall(call)) { + ((SqlValidatorImpl) validator).setValidatedNodeType(getWithinGroupCall(call), ret); + } return ret; } + + private static SqlCall getAggCall(SqlCall call) { + assert call.getOperator().getKind() == SqlKind.FILTER; + call = call.operand(0); + if (call.getOperator().getKind() == SqlKind.WITHIN_GROUP) { + call = call.operand(0); + } + return call; + } + + private static SqlCall getWithinGroupCall(SqlCall call) { + assert call.getOperator().getKind() == SqlKind.FILTER; + call = call.operand(0); + if (call.getOperator().getKind() == SqlKind.WITHIN_GROUP) { + return call; + } + throw new AssertionError(); + } + + private static boolean hasWithinGroupCall(SqlCall call) { + assert call.getOperator().getKind() == SqlKind.FILTER; + call = call.operand(0); + return call.getOperator().getKind() == SqlKind.WITHIN_GROUP; + } } // End SqlFilterOperator.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- 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 e77b4be..225d3b4 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,11 @@ public enum SqlKind { FILTER, /** + * WITHIN_GROUP operator + */ + WITHIN_GROUP, + + /** * Window specification */ WINDOW, http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java index f330463..0b01d91 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlRankFunction.java @@ -19,6 +19,7 @@ package org.apache.calcite.sql; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlReturnTypeInference; +import org.apache.calcite.util.Optionality; /** * Operator which aggregates sets of values into a result. @@ -35,7 +36,7 @@ public class SqlRankFunction extends SqlAggFunction { boolean requiresOrder) { super(kind.name(), null, kind, returnTypes, null, OperandTypes.NILADIC, SqlFunctionCategory.NUMERIC, requiresOrder, - true); + true, Optionality.FORBIDDEN); } //~ Methods ---------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java ---------------------------------------------------------------------- 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 83dad3a..652886f 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.sql; +import org.apache.calcite.rel.RelCollations; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; @@ -118,7 +119,7 @@ public interface SqlSplittableAggFunction { public AggregateCall other(RelDataTypeFactory typeFactory, AggregateCall e) { return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, - ImmutableIntList.of(), -1, + ImmutableIntList.of(), -1, RelCollations.EMPTY, typeFactory.createSqlType(SqlTypeName.BIGINT), null); } @@ -147,8 +148,8 @@ public interface SqlSplittableAggFunction { } int ordinal = extra.register(node); return AggregateCall.create(SqlStdOperatorTable.SUM0, false, false, - ImmutableList.of(ordinal), -1, aggregateCall.type, - aggregateCall.name); + ImmutableList.of(ordinal), -1, aggregateCall.collation, + aggregateCall.type, aggregateCall.name); } /** @@ -207,8 +208,10 @@ public interface SqlSplittableAggFunction { Registry<RexNode> extra, int offset, RelDataType inputRowType, AggregateCall aggregateCall, int leftSubTotal, int rightSubTotal) { assert (leftSubTotal >= 0) != (rightSubTotal >= 0); + assert aggregateCall.collation.getFieldCollations().isEmpty(); final int arg = leftSubTotal >= 0 ? leftSubTotal : rightSubTotal; - return aggregateCall.copy(ImmutableIntList.of(arg), -1); + return aggregateCall.copy(ImmutableIntList.of(arg), -1, + RelCollations.EMPTY); } } @@ -230,6 +233,7 @@ public interface SqlSplittableAggFunction { public AggregateCall other(RelDataTypeFactory typeFactory, AggregateCall e) { return AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, ImmutableIntList.of(), -1, + RelCollations.EMPTY, typeFactory.createSqlType(SqlTypeName.BIGINT), null); } @@ -260,8 +264,8 @@ public interface SqlSplittableAggFunction { } int ordinal = extra.register(node); return AggregateCall.create(getMergeAggFunctionOfTopSplit(), false, false, - ImmutableList.of(ordinal), -1, aggregateCall.type, - aggregateCall.name); + ImmutableList.of(ordinal), -1, aggregateCall.collation, + aggregateCall.type, aggregateCall.name); } protected abstract SqlAggFunction getMergeAggFunctionOfTopSplit(); http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/SqlWithinGroupOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWithinGroupOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlWithinGroupOperator.java new file mode 100644 index 0000000..3ec0ee8 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/SqlWithinGroupOperator.java @@ -0,0 +1,85 @@ +/* + * 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.rel.type.RelDataType; +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 static org.apache.calcite.util.Static.RESOURCE; + +/** + * An operator that applies a sort operation before rows are included in an aggregate function. + * + * <p>Operands are as follows:</p> + * + * <ul> + * <li>0: a call to an aggregate function ({@link SqlCall}) + * <li>1: order operation list + * </ul> + */ +public class SqlWithinGroupOperator extends SqlBinaryOperator { + + public SqlWithinGroupOperator() { + super("WITHIN GROUP", SqlKind.WITHIN_GROUP, 100, true, ReturnTypes.ARG0, + null, OperandTypes.ANY_ANY); + } + + @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { + assert call.operandCount() == 2; + call.operand(0).unparse(writer, 0, 0); + writer.keyword("WITHIN GROUP"); + final SqlWriter.Frame orderFrame = + writer.startList(SqlWriter.FrameTypeEnum.ORDER_BY_LIST, "(", ")"); + writer.keyword("ORDER BY"); + ((SqlNodeList) call.operand(1)).commaList(writer); + writer.endList(orderFrame); + } + + public void validateCall( + SqlCall call, + SqlValidator validator, + SqlValidatorScope scope, + SqlValidatorScope operandScope) { + assert call.getOperator() == this; + assert call.operandCount() == 2; + SqlCall aggCall = call.operand(0); + if (!aggCall.getOperator().isAggregator()) { + throw validator.newValidationError(call, + RESOURCE.withinGroupNotAllowed(aggCall.getOperator().getName())); + } + final SqlNodeList orderList = call.operand(1); + for (SqlNode order : orderList) { + RelDataType nodeType = + validator.deriveType(scope, order); + assert nodeType != null; + } + validator.validateAggregateParams(aggCall, null, orderList, scope); + } + + public RelDataType deriveType( + SqlValidator validator, + SqlValidatorScope scope, + SqlCall call) { + // Validate type of the inner aggregate call + return validateOperands(validator, scope, call); + } +} + +// End SqlWithinGroupOperator.java http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlAbstractGroupFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlAbstractGroupFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlAbstractGroupFunction.java index d1001dd..132c8cd 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlAbstractGroupFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlAbstractGroupFunction.java @@ -31,6 +31,7 @@ import org.apache.calcite.sql.validate.SelectScope; import org.apache.calcite.sql.validate.SqlValidator; import org.apache.calcite.sql.validate.SqlValidatorScope; import org.apache.calcite.sql.validate.SqlValidatorUtil; +import org.apache.calcite.util.Optionality; import org.apache.calcite.util.Static; /** @@ -55,7 +56,7 @@ public class SqlAbstractGroupFunction extends SqlAggFunction { SqlOperandTypeChecker operandTypeChecker, SqlFunctionCategory category) { super(name, null, kind, returnTypeInference, operandTypeInference, - operandTypeChecker, category, false, false); + operandTypeChecker, category, false, false, Optionality.FORBIDDEN); } @Override public void validateCall(SqlCall call, SqlValidator validator, http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java index 49208b9..32e9792 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlAnyValueAggFunction.java @@ -21,6 +21,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; @@ -44,7 +45,8 @@ public class SqlAnyValueAggFunction extends SqlAggFunction { OperandTypes.ANY, SqlFunctionCategory.SYSTEM, false, - false); + false, + Optionality.FORBIDDEN); Preconditions.checkArgument(kind == SqlKind.ANY_VALUE); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java index 6be1ce9..f1ffcf9 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlAvgAggFunction.java @@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; @@ -51,8 +52,10 @@ public class SqlAvgAggFunction extends SqlAggFunction { OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC, false, - false); - Preconditions.checkArgument(SqlKind.AVG_AGG_FUNCTIONS.contains(kind), "unsupported sql kind"); + false, + Optionality.FORBIDDEN); + Preconditions.checkArgument(SqlKind.AVG_AGG_FUNCTIONS.contains(kind), + "unsupported sql kind"); } @Deprecated // to be removed before 2.0 http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlCountAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCountAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlCountAggFunction.java index db54102..1678727 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCountAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCountAggFunction.java @@ -30,6 +30,7 @@ import org.apache.calcite.sql.type.SqlOperandTypeChecker; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.validate.SqlValidator; import org.apache.calcite.sql.validate.SqlValidatorScope; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -49,9 +50,11 @@ public class SqlCountAggFunction extends SqlAggFunction { this(name, SqlValidator.STRICT ? OperandTypes.ANY : OperandTypes.ONE_OR_MORE); } - public SqlCountAggFunction(String name, SqlOperandTypeChecker sqlOperandTypeChecker) { + public SqlCountAggFunction(String name, + SqlOperandTypeChecker sqlOperandTypeChecker) { super(name, null, SqlKind.COUNT, ReturnTypes.BIGINT, null, - sqlOperandTypeChecker, SqlFunctionCategory.NUMERIC, false, false); + sqlOperandTypeChecker, SqlFunctionCategory.NUMERIC, false, false, + Optionality.FORBIDDEN); } //~ Methods ---------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlCovarAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCovarAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlCovarAggFunction.java index 8591959..764339d 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCovarAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCovarAggFunction.java @@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; @@ -48,7 +49,8 @@ public class SqlCovarAggFunction extends SqlAggFunction { OperandTypes.NUMERIC_NUMERIC, SqlFunctionCategory.NUMERIC, false, - false); + false, + Optionality.FORBIDDEN); Preconditions.checkArgument(SqlKind.COVAR_AVG_AGG_FUNCTIONS.contains(kind), "unsupported sql kind: " + kind); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlFirstLastValueAggFunction.java ---------------------------------------------------------------------- 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 72dccd1..397acf8 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 @@ -24,6 +24,7 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableList; @@ -48,7 +49,8 @@ public class SqlFirstLastValueAggFunction extends SqlAggFunction { OperandTypes.ANY, SqlFunctionCategory.NUMERIC, false, - true); + true, + Optionality.FORBIDDEN); Preconditions.checkArgument(kind == SqlKind.FIRST_VALUE || kind == SqlKind.LAST_VALUE); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlHistogramAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlHistogramAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlHistogramAggFunction.java index a3fb5f2..864f8b4 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlHistogramAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlHistogramAggFunction.java @@ -23,6 +23,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -53,7 +54,8 @@ public class SqlHistogramAggFunction extends SqlAggFunction { OperandTypes.NUMERIC_OR_STRING, SqlFunctionCategory.NUMERIC, false, - false); + false, + Optionality.FORBIDDEN); this.type = type; } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlLeadLagAggFunction.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 d52c82b..55dc0cf 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 @@ -28,6 +28,7 @@ import org.apache.calcite.sql.type.SqlSingleOperandTypeChecker; import org.apache.calcite.sql.type.SqlTypeFamily; import org.apache.calcite.sql.type.SqlTypeTransform; import org.apache.calcite.sql.type.SqlTypeTransforms; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableList; @@ -78,7 +79,8 @@ public class SqlLeadLagAggFunction extends SqlAggFunction { OPERAND_TYPES, SqlFunctionCategory.NUMERIC, false, - true); + true, + Optionality.FORBIDDEN); Preconditions.checkArgument(kind == SqlKind.LEAD || kind == SqlKind.LAG); } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java index 438c7f1..edbc4b0 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlMinMaxAggFunction.java @@ -24,6 +24,7 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlSplittableAggFunction; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableList; @@ -75,7 +76,8 @@ public class SqlMinMaxAggFunction extends SqlAggFunction { OperandTypes.COMPARABLE_ORDERED, SqlFunctionCategory.SYSTEM, false, - false); + false, + Optionality.FORBIDDEN); this.argTypes = ImmutableList.of(); this.minMaxKind = MINMAX_COMPARABLE; Preconditions.checkArgument(kind == SqlKind.MIN http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.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 b1a2a86..6e66653 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 @@ -21,6 +21,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; /** * <code>NTH_VALUE</code> windowed aggregate function @@ -31,7 +32,7 @@ public class SqlNthValueAggFunction extends SqlAggFunction { public SqlNthValueAggFunction(SqlKind kind) { super(kind.name(), null, kind, ReturnTypes.ARG0_NULLABLE_IF_EMPTY, null, OperandTypes.ANY_NUMERIC, SqlFunctionCategory.NUMERIC, false, - true); + true, Optionality.FORBIDDEN); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java index 18677f5..d451c0b 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlNtileAggFunction.java @@ -21,6 +21,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; /** * <code>NTILE</code> aggregate function @@ -37,7 +38,8 @@ public class SqlNtileAggFunction extends SqlAggFunction { OperandTypes.POSITIVE_INTEGER_LITERAL, SqlFunctionCategory.NUMERIC, false, - true); + true, + Optionality.FORBIDDEN); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java index 6e745df..6f4518e 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.java @@ -23,6 +23,7 @@ import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -51,7 +52,8 @@ public class SqlSingleValueAggFunction extends SqlAggFunction { OperandTypes.ANY, SqlFunctionCategory.SYSTEM, false, - false); + false, + Optionality.FORBIDDEN); this.type = type; } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.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 827a516..ac09932 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 @@ -46,6 +46,7 @@ import org.apache.calcite.sql.SqlUnnestOperator; import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.sql.SqlValuesOperator; import org.apache.calcite.sql.SqlWindow; +import org.apache.calcite.sql.SqlWithinGroupOperator; import org.apache.calcite.sql.SqlWriter; import org.apache.calcite.sql.type.InferTypes; import org.apache.calcite.sql.type.OperandTypes; @@ -57,6 +58,7 @@ import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.sql.validate.SqlModality; import org.apache.calcite.sql2rel.AuxiliaryConverter; import org.apache.calcite.util.Litmus; +import org.apache.calcite.util.Optionality; import org.apache.calcite.util.Pair; import com.google.common.collect.ImmutableList; @@ -177,6 +179,9 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { * aggregate function. */ public static final SqlFilterOperator FILTER = new SqlFilterOperator(); + /** <code>WITHIN_GROUP</code> operator performs aggregations on ordered data input. */ + public static final SqlWithinGroupOperator WITHIN_GROUP = new SqlWithinGroupOperator(); + /** {@code CUBE} operator, occurs within {@code GROUP BY} clause * or nested within a {@code GROUPING SETS}. */ public static final SqlInternalOperator CUBE = @@ -1980,7 +1985,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { ReturnTypes.TO_MULTISET, null, OperandTypes.ANY, - SqlFunctionCategory.SYSTEM, false, false) { + SqlFunctionCategory.SYSTEM, false, false, + Optionality.OPTIONAL) { }; /** @@ -1992,7 +1998,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { ReturnTypes.ARG0, null, OperandTypes.MULTISET, - SqlFunctionCategory.SYSTEM, false, false) { + SqlFunctionCategory.SYSTEM, false, false, + Optionality.FORBIDDEN) { }; /** http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlSumAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlSumAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlSumAggFunction.java index eac5731..941a4e7 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSumAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSumAggFunction.java @@ -24,6 +24,7 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlSplittableAggFunction; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -54,7 +55,8 @@ public class SqlSumAggFunction extends SqlAggFunction { OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC, false, - false); + false, + Optionality.FORBIDDEN); this.type = type; } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/fun/SqlSumEmptyIsZeroAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlSumEmptyIsZeroAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlSumEmptyIsZeroAggFunction.java index 1fd9bf1..b74e9f9 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlSumEmptyIsZeroAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlSumEmptyIsZeroAggFunction.java @@ -25,6 +25,7 @@ import org.apache.calcite.sql.SqlSplittableAggFunction; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -48,7 +49,8 @@ public class SqlSumEmptyIsZeroAggFunction extends SqlAggFunction { OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC, false, - false); + false, + Optionality.FORBIDDEN); } //~ Methods ---------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java ---------------------------------------------------------------------- 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 a00a523..2962459 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 @@ -156,6 +156,10 @@ class AggChecker extends SqlBasicVisitor<Void> { call.operand(0).accept(this); return null; } + if (call.getKind() == SqlKind.WITHIN_GROUP) { + call.operand(0).accept(this); + return null; + } // Visit the operand in window function if (call.getKind() == SqlKind.OVER) { for (SqlNode operand : call.<SqlCall>operand(0).getOperandList()) { http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java index 0cf8c0f..186daf5 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java @@ -31,6 +31,7 @@ import org.apache.calcite.sql.type.SqlOperandTypeChecker; import org.apache.calcite.sql.type.SqlOperandTypeInference; import org.apache.calcite.sql.type.SqlReturnTypeInference; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.Optionality; import org.apache.calcite.util.Util; import com.google.common.collect.Lists; @@ -57,10 +58,12 @@ public class SqlUserDefinedAggFunction extends SqlAggFunction { SqlReturnTypeInference returnTypeInference, SqlOperandTypeInference operandTypeInference, SqlOperandTypeChecker operandTypeChecker, AggregateFunction function, - boolean requiresOrder, boolean requiresOver, RelDataTypeFactory typeFactory) { + boolean requiresOrder, boolean requiresOver, + Optionality requiresGroupOrder, RelDataTypeFactory typeFactory) { super(Util.last(opName.names), opName, SqlKind.OTHER_FUNCTION, returnTypeInference, operandTypeInference, operandTypeChecker, - SqlFunctionCategory.USER_DEFINED_FUNCTION, requiresOrder, requiresOver); + SqlFunctionCategory.USER_DEFINED_FUNCTION, requiresOrder, requiresOver, + requiresGroupOrder); this.function = function; this.typeFactory = typeFactory; } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java index a4b4297..2045eac 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java @@ -294,12 +294,14 @@ public interface SqlValidator { /** * Validates parameters for aggregate function. * - * @param aggCall Function containing COLUMN_LIST parameter - * @param filter Filter, or null + * @param aggCall Call to aggregate function + * @param filter Filter ({@code FILTER (WHERE)} clause), or null + * @param orderList Ordering specification ({@code WITHING GROUP} clause), + * or null * @param scope Syntactic scope */ void validateAggregateParams(SqlCall aggCall, SqlNode filter, - SqlValidatorScope scope); + SqlNodeList orderList, SqlValidatorScope scope); /** * Validates a COLUMN_LIST parameter http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index f7ce4d6..626399f 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -45,6 +45,7 @@ import org.apache.calcite.sql.JoinConditionType; import org.apache.calcite.sql.JoinType; import org.apache.calcite.sql.SqlAccessEnum; import org.apache.calcite.sql.SqlAccessType; +import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlCallBinding; @@ -4868,7 +4869,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { targetWindow.setWindowCall(null); call.validate(this, scope); - validateAggregateParams(call, null, scope); + validateAggregateParams(call, null, null, scope); // Disable nested aggregates post validation inWindow = false; @@ -5141,7 +5142,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { } public void validateAggregateParams(SqlCall aggCall, SqlNode filter, - SqlValidatorScope scope) { + SqlNodeList orderList, SqlValidatorScope scope) { // For "agg(expr)", expr cannot itself contain aggregate function // invocations. For example, "SUM(2 * MAX(x))" is illegal; when // we see it, we'll report the error for the SUM (not the MAX). @@ -5175,6 +5176,40 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { throw newValidationError(filter, RESOURCE.aggregateInFilterIllegal()); } } + if (orderList != null) { + for (SqlNode param : orderList) { + if (a.findAgg(param) != null) { + throw newValidationError(aggCall, + RESOURCE.aggregateInWithinGroupIllegal()); + } + } + } + + final SqlAggFunction op = (SqlAggFunction) aggCall.getOperator(); + switch (op.requiresGroupOrder()) { + case MANDATORY: + if (orderList == null || orderList.size() == 0) { + throw newValidationError(aggCall, + RESOURCE.aggregateMissingWithinGroupClause(op.getName())); + } + break; + case OPTIONAL: + break; + case IGNORED: + // rewrite the order list to empty + if (orderList != null) { + orderList.getList().clear(); + } + break; + case FORBIDDEN: + if (orderList != null && orderList.size() != 0) { + throw newValidationError(aggCall, + RESOURCE.withinGroupClauseIllegalInAggregate(op.getName())); + } + break; + default: + throw new AssertionError(op); + } } public void validateCall( http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java index e9031ba..5d6fc16 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java @@ -20,6 +20,7 @@ import org.apache.calcite.linq4j.Ord; import org.apache.calcite.plan.RelOptCluster; import org.apache.calcite.plan.RelOptUtil; import org.apache.calcite.rel.RelCollation; +import org.apache.calcite.rel.RelCollations; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; @@ -794,12 +795,11 @@ public class RelFieldTrimmer implements ReflectiveVisitor { aggregate.getGroupSet().rebuild(); // 2. agg functions for (AggregateCall aggCall : aggregate.getAggCallList()) { - for (int i : aggCall.getArgList()) { - inputFieldsUsed.set(i); - } + inputFieldsUsed.addAll(aggCall.getArgList()); if (aggCall.filterArg >= 0) { inputFieldsUsed.set(aggCall.filterArg); } + inputFieldsUsed.addAll(RelCollations.ordinals(aggCall.collation)); } // Create input with trimmed columns. @@ -871,6 +871,7 @@ public class RelFieldTrimmer implements ReflectiveVisitor { .distinct(aggCall.isDistinct()) .filter(filterArg) .approximate(aggCall.isApproximate()) + .sort(relBuilder.fields(aggCall.collation)) .as(aggCall.name); mapping.set(j, groupCount + indicatorCount + newAggCallList.size()); newAggCallList.add(newAggCall); http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- 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 9750664..9512312 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -199,6 +199,7 @@ import java.util.Objects; import java.util.Set; import java.util.TreeSet; import java.util.function.Supplier; +import java.util.stream.Collectors; import javax.annotation.Nonnull; import static org.apache.calcite.sql.SqlUtil.stripAs; @@ -1124,9 +1125,10 @@ public class SqlToRelConverter { LogicalAggregate.create(seek, ImmutableBitSet.of(), null, ImmutableList.of( AggregateCall.create(SqlStdOperatorTable.COUNT, false, - false, ImmutableList.of(), -1, longType, null), + false, ImmutableList.of(), -1, RelCollations.EMPTY, + longType, null), AggregateCall.create(SqlStdOperatorTable.COUNT, false, - false, args, -1, longType, null))); + false, args, -1, RelCollations.EMPTY, longType, null))); LogicalJoin join = LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true), ImmutableSet.of(), JoinRelType.INNER); @@ -2727,6 +2729,10 @@ public class SqlToRelConverter { replaceSubQueries(bb, aggregateFinder.filterList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN); + // also replace sub-queries inside ordering spec in the aggregates + replaceSubQueries(bb, aggregateFinder.orderList, + RelOptUtil.Logic.TRUE_FALSE_UNKNOWN); + // If group-by clause is missing, pretend that it has zero elements. if (groupList == null) { groupList = SqlNodeList.EMPTY; @@ -4667,7 +4673,9 @@ public class SqlToRelConverter { if (agg != null) { final SqlOperator op = call.getOperator(); if (window == null - && (op.isAggregator() || op.getKind() == SqlKind.FILTER)) { + && (op.isAggregator() + || op.getKind() == SqlKind.FILTER + || op.getKind() == SqlKind.WITHIN_GROUP)) { return agg.lookupAggregates(call); } } @@ -4918,7 +4926,8 @@ public class SqlToRelConverter { public Void visit(SqlCall call) { switch (call.getKind()) { case FILTER: - translateAgg((SqlCall) call.operand(0), call.operand(1), call); + case WITHIN_GROUP: + translateAgg(call); return null; case SELECT: // rchen 2006-10-17: @@ -4952,7 +4961,7 @@ public class SqlToRelConverter { inOver = false; } else { // We're beyond the one ignored level - translateAgg(call, null, call); + translateAgg(call); return null; } } @@ -4967,8 +4976,24 @@ public class SqlToRelConverter { return null; } - private void translateAgg(SqlCall call, SqlNode filter, SqlCall outerCall) { + private void translateAgg(SqlCall call) { + translateAgg(call, null, null, call); + } + + private void translateAgg(SqlCall call, SqlNode filter, + SqlNodeList orderList, 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); + return; + case WITHIN_GROUP: + assert orderList == null; + translateAgg(call.operand(0), filter, call.operand(1), outerCall); + return; + } final List<Integer> args = new ArrayList<>(); int filterArg = -1; final List<RelDataType> argTypes = @@ -5026,6 +5051,24 @@ public class SqlToRelConverter { distinct = true; approximate = true; } + final RelCollation collation; + if (orderList == null || orderList.size() == 0) { + collation = RelCollations.EMPTY; + } else { + collation = RelCollations.of( + orderList.getList() + .stream() + .map(order -> + bb.convertSortExpression(order, + RelFieldCollation.Direction.ASCENDING, + RelFieldCollation.NullDirection.UNSPECIFIED)) + .map(fieldCollation -> + new RelFieldCollation( + lookupOrCreateGroupExpr(fieldCollation.left), + fieldCollation.getDirection(), + fieldCollation.getNullDirection())) + .collect(Collectors.toList())); + } final AggregateCall aggCall = AggregateCall.create( aggFunction, @@ -5033,6 +5076,7 @@ public class SqlToRelConverter { approximate, args, filterArg, + collation, type, nameMap.get(outerCall.toString())); final AggregatingSelectScope.Resolved r = @@ -5358,6 +5402,7 @@ public class SqlToRelConverter { private static class AggregateFinder extends SqlBasicVisitor<Void> { final SqlNodeList list = new SqlNodeList(SqlParserPos.ZERO); final SqlNodeList filterList = new SqlNodeList(SqlParserPos.ZERO); + final SqlNodeList orderList = new SqlNodeList(SqlParserPos.ZERO); @Override public Void visit(SqlCall call) { // ignore window aggregates and ranking functions (associated with OVER operator) @@ -5375,6 +5420,16 @@ public class SqlToRelConverter { return null; } + if (call.getOperator().getKind() == SqlKind.WITHIN_GROUP) { + // the WHERE in a WITHIN_GROUP must be tracked too so we can call replaceSubQueries on it. + // see https://issues.apache.org/jira/browse/CALCITE-1910 + final SqlNode aggCall = call.getOperandList().get(0); + final SqlNodeList orderList = (SqlNodeList) call.getOperandList().get(1); + list.add(aggCall); + orderList.getList().forEach(this.orderList::add); + return null; + } + if (call.getOperator().isAggregator()) { list.add(call); return null; http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/tools/RelBuilder.java ---------------------------------------------------------------------- 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 323f65a..3b0be9c 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -94,6 +94,7 @@ import java.math.BigDecimal; import java.util.AbstractList; import java.util.ArrayDeque; import java.util.ArrayList; +import java.util.Collections; import java.util.Deque; import java.util.HashSet; import java.util.LinkedList; @@ -785,17 +786,18 @@ public class RelBuilder { public AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct, boolean approximate, RexNode filter, String alias, Iterable<? extends RexNode> operands) { - return aggregateCall(aggFunction, distinct, filter, alias, operands) - .approximate(approximate); + return aggregateCall(aggFunction, distinct, approximate, filter, + ImmutableList.of(), alias, ImmutableList.copyOf(operands)); } /** Creates a call to an aggregate function. * * <p>To add other operands, apply - * {@link AggCall#filter(RexNode...)}, * {@link AggCall#distinct()}, - * {@link AggCall#as}, - * {@link AggCall#sort} to the result. */ + * {@link AggCall#approximate(boolean)}, + * {@link AggCall#filter(RexNode...)}, + * {@link AggCall#sort}, + * {@link AggCall#as} to the result. */ public AggCall aggregateCall(SqlAggFunction aggFunction, Iterable<? extends RexNode> operands) { return aggregateCall(aggFunction, false, false, null, ImmutableList.of(), @@ -805,10 +807,11 @@ public class RelBuilder { /** Creates a call to an aggregate function. * * <p>To add other operands, apply - * {@link AggCall#filter(RexNode...)}, * {@link AggCall#distinct()}, - * {@link AggCall#as}, - * {@link AggCall#sort} to the result. */ + * {@link AggCall#approximate(boolean)}, + * {@link AggCall#filter(RexNode...)}, + * {@link AggCall#sort}, + * {@link AggCall#as} to the result. */ public AggCall aggregateCall(SqlAggFunction aggFunction, RexNode... operands) { return aggregateCall(aggFunction, false, false, null, ImmutableList.of(), @@ -816,7 +819,7 @@ public class RelBuilder { } /** Creates a call to an aggregate function with all applicable operands. */ - AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct, + protected AggCall aggregateCall(SqlAggFunction aggFunction, boolean distinct, boolean approximate, RexNode filter, ImmutableList<RexNode> orderKeys, String alias, ImmutableList<RexNode> operands) { return new AggCallImpl(aggFunction, distinct, approximate, filter, alias, @@ -828,11 +831,24 @@ public class RelBuilder { return count(false, null, operands); } + /** Creates a call to the {@code COUNT} aggregate function. */ + public AggCall count(Iterable<? extends RexNode> operands) { + return count(false, null, operands); + } + /** 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, null, alias, - ImmutableList.copyOf(operands)); + return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, null, + ImmutableList.of(), alias, ImmutableList.copyOf(operands)); + } + + /** Creates a call to the {@code COUNT} aggregate function, + * optionally distinct and with an alias. */ + public AggCall count(boolean distinct, String alias, + Iterable<? extends RexNode> operands) { + return aggregateCall(SqlStdOperatorTable.COUNT, distinct, false, null, + ImmutableList.of(), alias, ImmutableList.copyOf(operands)); } /** Creates a call to the {@code COUNT(*)} aggregate function. */ @@ -848,8 +864,8 @@ 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, null, alias, - ImmutableList.of(operand)); + return aggregateCall(SqlStdOperatorTable.SUM, distinct, false, null, + ImmutableList.of(), alias, ImmutableList.of(operand)); } /** Creates a call to the {@code AVG} aggregate function. */ @@ -860,8 +876,8 @@ 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, null, alias, - ImmutableList.of(operand)); + return aggregateCall(SqlStdOperatorTable.AVG, distinct, false, null, + ImmutableList.of(), alias, ImmutableList.of(operand)); } /** Creates a call to the {@code MIN} aggregate function. */ @@ -872,8 +888,8 @@ 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, null, alias, - ImmutableList.of(operand)); + return aggregateCall(SqlStdOperatorTable.MIN, false, false, null, + ImmutableList.of(), alias, ImmutableList.of(operand)); } /** Creates a call to the {@code MAX} aggregate function, @@ -884,8 +900,8 @@ public class RelBuilder { /** Creates a call to the {@code MAX} aggregate function. */ public AggCall max(String alias, RexNode operand) { - return aggregateCall(SqlStdOperatorTable.MAX, false, null, alias, - ImmutableList.of(operand)); + return aggregateCall(SqlStdOperatorTable.MAX, false, false, null, + ImmutableList.of(), alias, ImmutableList.of(operand)); } // Methods for patterns @@ -1447,10 +1463,17 @@ public class RelBuilder { if (aggCall1.filter != null && !aggCall1.aggFunction.allowsFilter()) { throw new IllegalArgumentException("FILTER not allowed"); } + RelCollation collation = + RelCollations.of(aggCall1.orderKeys + .stream() + .map(orderKey -> + collation(orderKey, RelFieldCollation.Direction.ASCENDING, + null, Collections.emptyList())) + .collect(Collectors.toList())); aggregateCall = AggregateCall.create(aggCall1.aggFunction, aggCall1.distinct, - aggCall1.approximate, args, - filterArg, groupSet.cardinality(), r, null, aggCall1.alias); + aggCall1.approximate, args, filterArg, collation, + groupSet.cardinality(), r, null, aggCall1.alias); } else { aggregateCall = ((AggCallImpl2) aggCall).aggregateCall; } http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java index c7a3d22..8462d95 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -17,6 +17,10 @@ package org.apache.calcite.util; import org.apache.calcite.DataContext; +import org.apache.calcite.adapter.enumerable.AggregateLambdaFactory; +import org.apache.calcite.adapter.enumerable.OrderedAggregateLambdaFactory; +import org.apache.calcite.adapter.enumerable.SequencedAdderAggregateLambdaFactory; +import org.apache.calcite.adapter.enumerable.SourceSorter; import org.apache.calcite.adapter.java.ReflectiveSchema; import org.apache.calcite.adapter.jdbc.JdbcSchema; import org.apache.calcite.avatica.util.DateTimeUtils; @@ -433,7 +437,21 @@ public enum BuiltInMethod { CONTEXT_ROOT(Context.class, "root", true), DATA_CONTEXT_GET_QUERY_PROVIDER(DataContext.class, "getQueryProvider"), METADATA_REL(Metadata.class, "rel"), - STRUCT_ACCESS(SqlFunctions.class, "structAccess", Object.class, int.class, String.class); + STRUCT_ACCESS(SqlFunctions.class, "structAccess", Object.class, int.class, + String.class), + SOURCE_SORTER(SourceSorter.class, Function2.class, Function1.class, + Comparator.class), + ORDERED_AGGREGATE_LAMBDA_FACTORY(OrderedAggregateLambdaFactory.class, + Function0.class, List.class), + SEQUENCED_ADDER_AGGREGATE_LAMBDA_FACTORY(SequencedAdderAggregateLambdaFactory.class, + Function0.class, List.class), + AGG_LAMBDA_FACTORY_ACC_INITIALIZER(AggregateLambdaFactory.class, + "accumulatorInitializer"), + AGG_LAMBDA_FACTORY_ACC_ADDER(AggregateLambdaFactory.class, "accumulatorAdder"), + AGG_LAMBDA_FACTORY_ACC_RESULT_SELECTOR(AggregateLambdaFactory.class, + "resultSelector", Function2.class), + AGG_LAMBDA_FACTORY_ACC_SINGLE_GROUP_RESULT_SELECTOR(AggregateLambdaFactory.class, + "singleGroupResultSelector", Function1.class); public final Method method; public final Constructor constructor; http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/java/org/apache/calcite/util/Optionality.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/util/Optionality.java b/core/src/main/java/org/apache/calcite/util/Optionality.java new file mode 100644 index 0000000..43da19d --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/Optionality.java @@ -0,0 +1,41 @@ +/* + * 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.util; + +/** + * Four states that describe whether a particular behavior or + * property is allowed and/or not allowed. + */ +public enum Optionality { + /** A property is <em>mandatory</em> if an instance must possess it; + * it is an error if it does not. */ + MANDATORY, + + /** A property is <em>optional</em> if an instance may or may not possess it; + * neither state is an error. */ + OPTIONAL, + + /** A property is <em>ignored</em> if an instance may or may not possess it; + * if it possesses the property, the effect is as if it does not. */ + IGNORED, + + /** A property is <em>forbidden</em> if an instance must not possess it; + * it is an error if the instance has the property. */ + FORBIDDEN +} + +// End Optionality.java http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties ---------------------------------------------------------------------- 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 faefd68..edb93b9 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -109,6 +109,9 @@ AggregateIllegalInClause=Aggregate expression is illegal in {0} clause WindowedAggregateIllegalInClause=Windowed aggregate expression is illegal in {0} clause NestedAggIllegal=Aggregate expressions cannot be nested AggregateInFilterIllegal=FILTER must not contain aggregate expression +AggregateInWithinGroupIllegal=WITHIN GROUP must not contain aggregate expression +AggregateMissingWithinGroupClause=Aggregate expression ''{0}'' must contain a within group clause +WithinGroupClauseIllegalInAggregate=Aggregate expression ''{0}'' must not contain a within group clause AggregateIllegalInOrderBy=Aggregate expression is illegal in ORDER BY clause of non-aggregating SELECT CondMustBeBoolean={0} clause must be a condition HavingMustBeBoolean=HAVING clause must be a condition @@ -146,6 +149,7 @@ OrderByOverlap=ORDER BY not allowed in both base and referenced windows RefWindowWithFrame=Referenced window cannot have framing declarations TypeNotSupported=Type ''{0}'' is not supported FunctionQuantifierNotAllowed=DISTINCT/ALL not allowed with {0} function +WithinGroupNotAllowed=WITHIN GROUP not allowed with {0} function SomeButNotAllArgumentsAreNamed=Some but not all arguments are named DuplicateArgumentName=Duplicate argument name ''{0}'' DefaultForOptionalParameter=DEFAULT is only allowed for optional parameters http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java ---------------------------------------------------------------------- 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 c411888..7b63497 100644 --- a/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java +++ b/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java @@ -17,6 +17,7 @@ package org.apache.calcite.plan; import org.apache.calcite.adapter.java.ReflectiveSchema; +import org.apache.calcite.rel.RelCollations; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.externalize.RelJsonReader; @@ -140,10 +141,10 @@ public class RelWriterTest { ImmutableList.of( AggregateCall.create(SqlStdOperatorTable.COUNT, true, false, ImmutableList.of(1), -1, - bigIntType, "c"), + RelCollations.EMPTY, bigIntType, "c"), AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, ImmutableList.of(), -1, - bigIntType, "d"))); + RelCollations.EMPTY, bigIntType, "d"))); aggregate.explain(writer); return writer.asString(); }); http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/plan/volcano/TraitPropagationTest.java ---------------------------------------------------------------------- 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 2822c1c..e901868 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,8 @@ public class TraitPropagationTest { // aggregate on s, count AggregateCall aggCall = AggregateCall.create(SqlStdOperatorTable.COUNT, - false, false, Collections.singletonList(1), -1, sqlBigInt, "cnt"); + false, false, Collections.singletonList(1), -1, RelCollations.EMPTY, + sqlBigInt, "cnt"); RelNode agg = new LogicalAggregate(cluster, cluster.traitSetOf(Convention.NONE), project, false, ImmutableBitSet.of(0), null, Collections.singletonList(aggCall)); http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index 94b98b7..7a5181e 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -274,8 +274,8 @@ public class RelToSqlConverterTest { final RelNode root = builder .scan("EMP") .aggregate(builder.groupKey(), - builder.aggregateCall(SqlStdOperatorTable.SUM0, false, null, "s", - builder.field(3))) + builder.aggregateCall(SqlStdOperatorTable.SUM0, builder.field(3)) + .as("s")) .build(); final String expectedMysql = "SELECT COALESCE(SUM(`MGR`), 0) AS `s`\n" + "FROM `scott`.`EMP`"; @@ -2763,6 +2763,53 @@ public class RelToSqlConverterTest { callsUnparseCallOnSqlSelect[0], is(true)); } + @Test public void testWithinGroup1() { + final String query = "select \"product_class_id\", collect(\"net_weight\") " + + "within group (order by \"net_weight\" desc) " + + "from \"product\" group by \"product_class_id\""; + final String expected = "SELECT \"product_class_id\", COLLECT(\"net_weight\") " + + "WITHIN GROUP (ORDER BY \"net_weight\" DESC)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""; + sql(query).ok(expected); + } + + @Test public void testWithinGroup2() { + final String query = "select \"product_class_id\", collect(\"net_weight\") " + + "within group (order by \"low_fat\", \"net_weight\" desc nulls last) " + + "from \"product\" group by \"product_class_id\""; + final String expected = "SELECT \"product_class_id\", COLLECT(\"net_weight\") " + + "WITHIN GROUP (ORDER BY \"low_fat\", \"net_weight\" DESC NULLS LAST)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""; + sql(query).ok(expected); + } + + @Test public void testWithinGroup3() { + final String query = "select \"product_class_id\", collect(\"net_weight\") " + + "within group (order by \"net_weight\" desc), " + + "min(\"low_fat\")" + + "from \"product\" group by \"product_class_id\""; + final String expected = "SELECT \"product_class_id\", COLLECT(\"net_weight\") " + + "WITHIN GROUP (ORDER BY \"net_weight\" DESC), MIN(\"low_fat\")\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""; + sql(query).ok(expected); + } + + @Test public void testWithinGroup4() { + // filter in AggregateCall is not unparsed + final String query = "select \"product_class_id\", collect(\"net_weight\") " + + "within group (order by \"net_weight\" desc) filter (where \"net_weight\" > 0)" + + "from \"product\" group by \"product_class_id\""; + final String expected = "SELECT \"product_class_id\", COLLECT(\"net_weight\") " + + "WITHIN GROUP (ORDER BY \"net_weight\" DESC)\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_class_id\""; + sql(query).ok(expected); + } + + /** Fluid interface to run tests. */ static class Sql { private final SchemaPlus schema; http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java ---------------------------------------------------------------------- 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 6ad2769..d8dddc3 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 @@ -8194,6 +8194,64 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test public void testWithinGroupClause1() { + final String sql = "select col1,\n" + + " collect(col2) within group (order by col3)\n" + + "from t\n" + + "order by col1 limit 10"; + final String expected = "SELECT `COL1`," + + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`))\n" + + "FROM `T`\n" + + "ORDER BY `COL1`\n" + + "FETCH NEXT 10 ROWS ONLY"; + sql(sql).ok(expected); + } + + @Test public void testWithinGroupClause2() { + final String sql = "select collect(col2) within group (order by col3)\n" + + "from t\n" + + "order by col1 limit 10"; + final String expected = "SELECT" + + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`))\n" + + "FROM `T`\n" + + "ORDER BY `COL1`\n" + + "FETCH NEXT 10 ROWS ONLY"; + sql(sql).ok(expected); + } + + @Test public void testWithinGroupClause3() { + final String sql = "select collect(col2) within group (^)^ " + + "from t order by col1 limit 10"; + sql(sql).fails("(?s).*Encountered \"\\)\" at line 1, column 36\\..*"); + } + + @Test public void testWithinGroupClause4() { + final String sql = "select col1,\n" + + " collect(col2) within group (order by col3, col4)\n" + + "from t\n" + + "order by col1 limit 10"; + final String expected = "SELECT `COL1`," + + " (COLLECT(`COL2`) WITHIN GROUP (ORDER BY `COL3`, `COL4`))\n" + + "FROM `T`\n" + + "ORDER BY `COL1`\n" + + "FETCH NEXT 10 ROWS ONLY"; + sql(sql).ok(expected); + } + + @Test public void testWithinGroupClause5() { + final String sql = "select col1,\n" + + " collect(col2) within group (\n" + + " 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" + + "FROM `T`\n" + + "ORDER BY `COL1`\n" + + "FETCH NEXT 10 ROWS ONLY"; + sql(sql).ok(expected); + } + //~ Inner Interfaces ------------------------------------------------------- /** http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 4484762..da7d099 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -5622,6 +5622,8 @@ public abstract class SqlOperatorBaseTest { final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; tester.checkAgg("collect(x)", values, Collections.singletonList("[0, 2, 2]"), (double) 0); + tester.checkAgg("collect(x) within group(order by x desc)", values, + Collections.singletonList("[2, 2, 0]"), (double) 0); Object result1 = -3; if (!enable) { return; http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- 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 cd87e5f..888ed6c 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -6657,6 +6657,81 @@ public class JdbcTest { connection.close(); } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2224";>[CALCITE-2224] + * WITHIN GROUP clause for aggregate functions</a>. */ + @Test public void testWithinGroupClause1() { + final String sql = "select X,\n" + + " collect(Y) within group (order by Y desc) as \"SET\"\n" + + "from (values (1, 'a'), (1, 'b'),\n" + + " (3, 'c'), (3, 'd')) AS t(X, Y)\n" + + "group by X\n" + + "limit 10"; + CalciteAssert.that().query(sql) + .returnsUnordered("X=1; SET=[b, a]", + "X=3; SET=[d, c]"); + } + + @Test public void testWithinGroupClause2() { + final String sql = "select X,\n" + + " collect(Y) within group (order by Y desc) as SET_1,\n" + + " collect(Y) within group (order by Y asc) as SET_2\n" + + "from (values (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd')) AS t(X, Y)\n" + + "group by X\n" + + "limit 10"; + CalciteAssert + .that() + .query(sql) + .returnsUnordered("X=1; SET_1=[b, a]; SET_2=[a, b]", + "X=3; SET_1=[d, c]; SET_2=[c, d]"); + } + + @Test public void testWithinGroupClause3() { + final String sql = "select" + + " collect(Y) within group (order by Y desc) as SET_1,\n" + + " collect(Y) within group (order by Y asc) as SET_2\n" + + "from (values (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd')) AS t(X, Y)\n" + + "limit 10"; + CalciteAssert.that().query(sql) + .returns("SET_1=[d, c, b, a]; SET_2=[a, b, c, d]\n"); + } + + @Test public void testWithinGroupClause4() { + final String sql = "select" + + " collect(Y) within group (order by Y desc) as SET_1,\n" + + " collect(Y) within group (order by Y asc) as SET_2\n" + + "from (values (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd')) AS t(X, Y)\n" + + "group by X\n" + + "limit 10"; + CalciteAssert.that().query(sql) + .returnsUnordered("SET_1=[b, a]; SET_2=[a, b]", + "SET_1=[d, c]; SET_2=[c, d]"); + } + + @Test public void testWithinGroupClause5() { + CalciteAssert + .that() + .query("select collect(array[X, Y])\n" + + " within group (order by Y desc) as \"SET\"\n" + + "from (values ('b', 'a'), ('a', 'b'), ('a', 'c'),\n" + + " ('a', 'd')) AS t(X, Y)\n" + + "limit 10") + .returns("SET=[[a, d], [a, c], [a, b], [b, a]]\n"); + } + + @Test public void testWithinGroupClause6() { + final String sql = "select collect(\"commission\")" + + " within group (order by \"commission\")\n" + + "from \"hr\".\"emps\""; + CalciteAssert.that() + .with(CalciteAssert.Config.REGULAR) + .query(sql) + .explainContains("EnumerableAggregate(group=[{}], " + + "EXPR$0=[COLLECT($4) WITHIN GROUP ([4])])") + .returns("EXPR$0=[250, 500, 1000]\n"); + } + private static String sums(int n, boolean c) { final StringBuilder b = new StringBuilder(); for (int i = 0; i < n; i++) { http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java ---------------------------------------------------------------------- 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 ec94d94..0c216b3 100644 --- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java @@ -1350,7 +1350,7 @@ public class RelMetadataTest extends SqlToRelTestBase { ImmutableList.of( AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, ImmutableIntList.of(), - -1, 2, join, null, null))); + -1, RelCollations.EMPTY, 2, join, null, null))); rowSize = mq.getAverageRowSize(aggregate); columnSizes = mq.getAverageColumnSizes(aggregate); assertThat(columnSizes.size(), equalTo(3)); http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- 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 92f98a8..94707fe 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -2862,6 +2862,34 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).with(tester).ok(); } + @Test public void testWithinGroup1() { + final String sql = "select deptno,\n" + + " collect(empno) within group (order by deptno, hiredate desc)\n" + + "from emp\n" + + "group by deptno"; + sql(sql).ok(); + } + + @Test public void testWithinGroup2() { + final String sql = "select dept.deptno,\n" + + " collect(sal) within group (order by sal desc) as s,\n" + + " collect(sal) within group (order by 1)as s1,\n" + + " collect(sal) within group (order by sal)\n" + + " filter (where sal > 2000) as s2\n" + + "from emp\n" + + "join dept using (deptno)\n" + + "group by dept.deptno"; + sql(sql).ok(); + } + + @Test public void testWithinGroup3() { + final String sql = "select deptno,\n" + + " collect(empno) within group (order by empno not in (1, 2)), count(*)\n" + + "from emp\n" + + "group by deptno"; + sql(sql).ok(); + } + /** * Visitor that checks that every {@link RelNode} in a tree is valid. * http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- 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 abfdc57..a1d4233 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -7116,6 +7116,40 @@ public class SqlValidatorTest extends SqlValidatorTestCase { .fails("FILTER must not contain aggregate expression"); } + @Test public void testWithinGroup() { + sql("select deptno,\n" + + " collect(empno) within group(order by 1)\n" + + "from emp\n" + + "group by deptno").ok(); + sql("select collect(empno) within group(order by 1)\n" + + "from emp\n" + + "group by ()").ok(); + sql("select deptno,\n" + + " collect(empno) within group(order by deptno)\n" + + "from emp\n" + + "group by deptno").ok(); + sql("select deptno,\n" + + " collect(empno) within group(order by deptno, hiredate desc)\n" + + "from emp\n" + + "group by deptno").ok(); + sql("select deptno,\n" + + " collect(empno) within group(\n" + + " order by cast(deptno as varchar), hiredate desc)\n" + + "from emp\n" + + "group by deptno").ok(); + sql("select collect(empno) within group(order by 1)\n" + + "from emp\n" + + "group by deptno").ok(); + sql("select collect(empno) within group(order by 1)\n" + + "from emp").ok(); + sql("select ^power(deptno, 1) within group(order by 1)^ from emp") + .fails("(?s).*WITHIN GROUP not allowed with POWER function.*"); + sql("select ^collect(empno)^ within group(order by count(*))\n" + + "from emp\n" + + "group by deptno") + .fails("WITHIN GROUP must not contain aggregate expression"); + } + @Test public void testCorrelatingVariables() { // reference to unqualified correlating column check("select * from emp where exists (\n" @@ -8645,6 +8679,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + "NEXT_VALUE -\n" + "PATTERN_EXCLUDE -\n" + "PATTERN_PERMUTE -\n" + + "WITHIN GROUP left\n" + "\n" + "PATTERN_QUANTIFIER -\n" + "\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/java/org/apache/calcite/tools/PlannerTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java index e0ed1c1..3b68d16 100644 --- a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java +++ b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java @@ -72,6 +72,7 @@ import org.apache.calcite.sql.util.ListSqlOperatorTable; import org.apache.calcite.sql.validate.SqlValidator; import org.apache.calcite.sql.validate.SqlValidatorScope; import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.util.Optionality; import org.apache.calcite.util.Util; import com.google.common.base.Throwables; @@ -1060,7 +1061,8 @@ public class PlannerTest { public static class MyCountAggFunction extends SqlAggFunction { public MyCountAggFunction() { super("MY_COUNT", null, SqlKind.OTHER_FUNCTION, ReturnTypes.BIGINT, null, - OperandTypes.ANY, SqlFunctionCategory.NUMERIC, false, false); + OperandTypes.ANY, SqlFunctionCategory.NUMERIC, false, false, + Optionality.FORBIDDEN); } @SuppressWarnings("deprecation") http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- 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 35d69e5..db6d2a9 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -5296,4 +5296,42 @@ LogicalProject(ANYEMPNO=[$1]) ]]> </Resource> </TestCase> + <TestCase name="testWithinGroup1"> + <Resource name="sql"> + <![CDATA[select deptno, collect(empno) within group (order by deptno, hiredate desc) from emp group by deptno]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COLLECT($1) WITHIN GROUP ([1, 2 DESC])]) + LogicalProject(DEPTNO=[$7], EMPNO=[$0], HIREDATE=[$4]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testWithinGroup2"> + <Resource name="sql"> + <![CDATA[select dept.deptno, collect(sal) within group (order by sal desc) as s, collect(sal) within group (order by 1)as s1, collect(sal) within group (order by sal) filter (where sal > 2000) as s2 from emp join dept using (deptno) group by dept.deptn]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0}], S=[COLLECT($1) WITHIN GROUP ([1 DESC])], S1=[COLLECT($1) WITHIN GROUP ([2])], S2=[COLLECT($1) WITHIN GROUP ([1]) FILTER $3]) + LogicalProject(DEPTNO=[$9], SAL=[$5], $f2=[1], $f3=[>($5, 2000)]) + LogicalJoin(condition=[=($7, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testWithinGroup3"> + <Resource name="sql"> + <![CDATA[select deptno, collect(empno) filter (where empno not in (1, 2)), count(*) from emp group by deptno]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COLLECT($1) WITHIN GROUP ([2])], EXPR$2=[COUNT()]) + LogicalProject(DEPTNO=[$7], EMPNO=[$0], $f2=[AND(<>($0, 1), <>($0, 2))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> </Root> http://git-wip-us.apache.org/repos/asf/calcite/blob/7bc9f140/core/src/test/resources/sql/agg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq index 24b75b3..d093a8a 100755 --- a/core/src/test/resources/sql/agg.iq +++ b/core/src/test/resources/sql/agg.iq @@ -2381,4 +2381,127 @@ group by MONTH(HIREDATE); !ok +# [CALCITE-2224] WITHIN GROUP clause for aggregate functions +select deptno, collect(empno) within group (order by empno asc) as empnos +from "scott".emp +group by deptno; + ++--------+--------------------------------------+ +| DEPTNO | EMPNOS | ++--------+--------------------------------------+ +| 10 | [7782, 7839, 7934] | +| 20 | [7369, 7566, 7788, 7876, 7902] | +| 30 | [7499, 7521, 7654, 7698, 7844, 7900] | ++--------+--------------------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{7}], EMPNOS=[COLLECT($0) WITHIN GROUP ([0])]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +select deptno, collect(empno) within group (order by empno desc) as empnos +from "scott".emp +group by deptno; + ++--------+--------------------------------------+ +| DEPTNO | EMPNOS | ++--------+--------------------------------------+ +| 10 | [7934, 7839, 7782] | +| 20 | [7902, 7876, 7788, 7566, 7369] | +| 30 | [7900, 7844, 7698, 7654, 7521, 7499] | ++--------+--------------------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{7}], EMPNOS=[COLLECT($0) WITHIN GROUP ([0 DESC])]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +select deptno, collect(empno) within group (order by empno desc) +filter (where empno > 7500) as empnos +from "scott".emp +group by deptno; + ++--------+--------------------------------+ +| DEPTNO | EMPNOS | ++--------+--------------------------------+ +| 10 | [7934, 7839, 7782] | +| 20 | [7902, 7876, 7788, 7566] | +| 30 | [7900, 7844, 7698, 7654, 7521] | ++--------+--------------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{0}], EMPNOS=[COLLECT($1) WITHIN GROUP ([1 DESC]) FILTER $2]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[7500], expr#9=[>($t0, $t8)], DEPTNO=[$t7], EMPNO=[$t0], $f2=[$t9]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +select deptno, collect(empno) within group (order by empno desc) as empnos1, +collect(empno) within group (order by empno asc) as empnos2 +from "scott".emp +group by deptno; + ++--------+--------------------------------------+--------------------------------------+ +| DEPTNO | EMPNOS1 | EMPNOS2 | ++--------+--------------------------------------+--------------------------------------+ +| 10 | [7934, 7839, 7782] | [7782, 7839, 7934] | +| 20 | [7902, 7876, 7788, 7566, 7369] | [7369, 7566, 7788, 7876, 7902] | +| 30 | [7900, 7844, 7698, 7654, 7521, 7499] | [7499, 7521, 7654, 7698, 7844, 7900] | ++--------+--------------------------------------+--------------------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{7}], EMPNOS1=[COLLECT($0) WITHIN GROUP ([0 DESC])], EMPNOS2=[COLLECT($0) WITHIN GROUP ([0])]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Aggregate WITHIN GROUP with JOIN +select dept.deptno, + collect(sal) within group (order by sal desc) as s, + collect(sal) within group (order by 1)as s1, + collect(sal) within group (order by sal) filter (where sal > 2000) as s2 +from "scott".emp +join "scott".dept using (deptno) +group by dept.deptno; + ++--------+-------------------------------------------------------+-------------------------------------------------------+-----------------------------+ +| DEPTNO | S | S1 | S2 | ++--------+-------------------------------------------------------+-------------------------------------------------------+-----------------------------+ +| 10 | [5000.00, 2450.00, 1300.00] | [2450.00, 5000.00, 1300.00] | [2450.00, 5000.00] | +| 20 | [3000.00, 3000.00, 2975.00, 1100.00, 800.00] | [800.00, 2975.00, 3000.00, 1100.00, 3000.00] | [2975.00, 3000.00, 3000.00] | +| 30 | [2850.00, 1600.00, 1500.00, 1250.00, 1250.00, 950.00] | [1600.00, 1250.00, 1250.00, 2850.00, 1500.00, 950.00] | [2850.00] | ++--------+-------------------------------------------------------+-------------------------------------------------------+-----------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{0}], S=[COLLECT($1) WITHIN GROUP ([1 DESC])], S1=[COLLECT($1) WITHIN GROUP ([2])], S2=[COLLECT($1) WITHIN GROUP ([1]) FILTER $3]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[1], expr#5=[2000], expr#6=[>($t2, $t5)], expr#7=[IS TRUE($t6)], DEPTNO=[$t0], SAL=[$t2], $f2=[$t4], $f3=[$t7]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +select deptno, collect(empno + 1) within group (order by 1) as empnos +from "scott".emp +group by deptno; + ++--------+--------------------------------------+ +| DEPTNO | EMPNOS | ++--------+--------------------------------------+ +| 10 | [7783, 7840, 7935] | +| 20 | [7370, 7567, 7789, 7877, 7903] | +| 30 | [7500, 7522, 7655, 7699, 7845, 7901] | ++--------+--------------------------------------+ +(3 rows) + +!ok +EnumerableAggregate(group=[{0}], EMPNOS=[COLLECT($1) WITHIN GROUP ([2])]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1], expr#9=[+($t0, $t8)], DEPTNO=[$t7], $f1=[$t9], $f2=[$t8]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + # End agg.iq
