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 2021/07/05 03:08:08 UTC
[calcite] branch master updated: [CALCITE-4644] Add PERCENTILE_CONT
and PERCENTILE_DISC functions (Rafay)
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
The following commit(s) were added to refs/heads/master by this push:
new 85953ce [CALCITE-4644] Add PERCENTILE_CONT and PERCENTILE_DISC functions (Rafay)
85953ce is described below
commit 85953ceb99e07b95129f4934797760a88709fcf6
Author: Rafay <qu...@gmail.com>
AuthorDate: Mon Jun 14 16:53:09 2021 -0700
[CALCITE-4644] Add PERCENTILE_CONT and PERCENTILE_DISC functions (Rafay)
In this change, we can parse and validate those functions,
but execution will be [CALCITE-4666].
Validation is based on new method SqlAggFunction.isPercentile()
(experimental and subject to change without notice).
There are connections between the RANGE clause in windowed
aggregate functions and interpolation used by PERCENTILE_CONT.
Therefore use the same logic to determine whether "x" is a
valid type in OVER (ORDER BY x RANGE r)" and
"PERCENTILE_CONT(f) WITHIN GROUP (ORER BY x)"
Close apache/calcite#2444
---
core/src/main/codegen/templates/Parser.jj | 2 +
.../apache/calcite/runtime/CalciteResource.java | 9 +++
.../org/apache/calcite/sql/SqlAggFunction.java | 11 ++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 7 +++
.../java/org/apache/calcite/sql/SqlWindow.java | 29 ++++-----
.../calcite/sql/fun/SqlBasicAggFunction.java | 34 ++++++++---
.../calcite/sql/fun/SqlStdOperatorTable.java | 30 ++++++++++
.../org/apache/calcite/sql/type/OperandTypes.java | 43 ++++++++++++++
.../org/apache/calcite/sql/type/SqlTypeFamily.java | 36 +++++++++++
.../calcite/sql/validate/SqlValidatorImpl.java | 27 +++++++++
.../calcite/runtime/CalciteResource.properties | 3 +
.../apache/calcite/sql/test/AbstractSqlTester.java | 5 ++
.../apache/calcite/sql/test/SqlAdvisorTest.java | 2 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 32 ++++++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 33 +++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 69 ++++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 58 ++++++++++++++++++
site/_docs/reference.md | 15 +++++
18 files changed, 417 insertions(+), 28 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index bab7a64..dd21035 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6933,6 +6933,8 @@ SqlIdentifier ReservedFunctionName() :
| <NULLIF>
| <OCTET_LENGTH>
| <PERCENT_RANK>
+ | <PERCENTILE_CONT>
+ | <PERCENTILE_DISC>
| <POWER>
| <RANK>
| <REGR_COUNT>
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 2cd4657..ec38767 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -472,6 +472,12 @@ public interface CalciteResource {
@BaseMessage("Type ''{0}'' is not supported")
ExInst<SqlValidatorException> typeNotSupported(String a0);
+ @BaseMessage("Invalid type ''{0}'' in ORDER BY clause of ''{1}'' function. Only NUMERIC types are supported")
+ ExInst<SqlValidatorException> unsupportedTypeInOrderBy(String a0, String a1);
+
+ @BaseMessage("''{0}'' requires precisely one ORDER BY key")
+ ExInst<SqlValidatorException> orderByRequiresOneKey(String a0);
+
@BaseMessage("DISTINCT/ALL not allowed with {0} function")
ExInst<SqlValidatorException> functionQuantifierNotAllowed(String a0);
@@ -540,6 +546,9 @@ public interface CalciteResource {
@BaseMessage("Argument to function ''{0}'' must be a positive integer literal")
ExInst<SqlValidatorException> argumentMustBePositiveInteger(String a0);
+ @BaseMessage("Argument to function ''{0}'' must be a numeric literal between {1,number,#} and {2,number,#}")
+ ExInst<SqlValidatorException> argumentMustBeNumericLiteralInRange(String a0, int min, int max);
+
@BaseMessage("Validation Error: {0}")
ExInst<CalciteException> validationError(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 9e04e45..208b34b 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.sql;
+import org.apache.calcite.linq4j.function.Experimental;
import org.apache.calcite.plan.Context;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -215,4 +216,14 @@ public abstract class SqlAggFunction extends SqlFunction implements Context {
public @Nullable SqlAggFunction getRollup() {
return null;
}
+
+ /** Returns whether this aggregate function is a PERCENTILE function.
+ * Such functions require a {@code WITHIN GROUP} clause that has precisely
+ * one sort key.
+ *
+ * <p>NOTE: This API is experimental and subject to change without notice. */
+ @Experimental
+ public boolean isPercentile() {
+ return false;
+ }
}
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 52e814e..bf58b2e 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -847,6 +847,12 @@ public enum SqlKind {
/** The {@code COLLECT} aggregate function. */
COLLECT,
+ /** The {@code PERCENTILE_CONT} aggregate function. */
+ PERCENTILE_CONT,
+
+ /** The {@code PERCENTILE_DISC} aggregate function. */
+ PERCENTILE_DISC,
+
/** The {@code FUSION} aggregate function. */
FUSION,
@@ -1074,6 +1080,7 @@ public enum SqlKind {
FUSION, SINGLE_VALUE, ROW_NUMBER, RANK, PERCENT_RANK, DENSE_RANK,
CUME_DIST, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_AND, BIT_OR, BIT_XOR,
LISTAGG, STRING_AGG, ARRAY_AGG, ARRAY_CONCAT_AGG, GROUP_CONCAT, COUNTIF,
+ PERCENTILE_CONT, PERCENTILE_DISC,
INTERSECTION, ANY_VALUE);
/**
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
index 8311d62..848fad0 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
@@ -705,28 +705,19 @@ public class SqlWindow extends SqlCall {
// if this is a range spec check and make sure the boundary type
// and order by type are compatible
if (orderTypeFam != null && !isRows) {
- RelDataType bndType = validator.deriveType(scope, boundVal);
- SqlTypeFamily bndTypeFam = bndType.getSqlTypeName().getFamily();
- switch (orderTypeFam) {
- case NUMERIC:
- if (SqlTypeFamily.NUMERIC != bndTypeFam) {
- throw validator.newValidationError(boundVal,
- RESOURCE.orderByRangeMismatch());
- }
- break;
- case DATE:
- case TIME:
- case TIMESTAMP:
- if (SqlTypeFamily.INTERVAL_DAY_TIME != bndTypeFam
- && SqlTypeFamily.INTERVAL_YEAR_MONTH != bndTypeFam) {
- throw validator.newValidationError(boundVal,
- RESOURCE.orderByRangeMismatch());
- }
- break;
- default:
+ final RelDataType boundType = validator.deriveType(scope, boundVal);
+ final SqlTypeFamily boundTypeFamily =
+ boundType.getSqlTypeName().getFamily();
+ final List<SqlTypeFamily> allowableBoundTypeFamilies =
+ orderTypeFam.allowableDifferenceTypes();
+ if (allowableBoundTypeFamilies.isEmpty()) {
throw validator.newValidationError(boundVal,
RESOURCE.orderByDataTypeProhibitsRange());
}
+ if (!allowableBoundTypeFamilies.contains(boundTypeFamily)) {
+ throw validator.newValidationError(boundVal,
+ RESOURCE.orderByRangeMismatch());
+ }
}
break;
default:
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
index deeb7fa..54b0725 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
@@ -51,6 +51,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
private final SqlSyntax syntax;
private final boolean allowsNullTreatment;
private final boolean allowsSeparator;
+ private final boolean percentile;
//~ Constructors -----------------------------------------------------------
@@ -60,7 +61,8 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
SqlOperandTypeChecker operandTypeChecker, SqlFunctionCategory funcType,
boolean requiresOrder, boolean requiresOver,
Optionality requiresGroupOrder, Optionality distinctOptionality,
- SqlSyntax syntax, boolean allowsNullTreatment, boolean allowsSeparator) {
+ SqlSyntax syntax, boolean allowsNullTreatment, boolean allowsSeparator,
+ boolean percentile) {
super(name, sqlIdentifier, kind,
requireNonNull(returnTypeInference, "returnTypeInference"), operandTypeInference,
requireNonNull(operandTypeChecker, "operandTypeChecker"),
@@ -70,6 +72,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
this.syntax = requireNonNull(syntax, "syntax");
this.allowsNullTreatment = allowsNullTreatment;
this.allowsSeparator = allowsSeparator;
+ this.percentile = percentile;
}
/** Creates a SqlBasicAggFunction whose name is the same as its kind. */
@@ -86,7 +89,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
return new SqlBasicAggFunction(name, null, kind, returnTypeInference, null,
operandTypeChecker, SqlFunctionCategory.NUMERIC, false, false,
Optionality.FORBIDDEN, Optionality.OPTIONAL, SqlSyntax.FUNCTION, false,
- false);
+ false, false);
}
//~ Methods ----------------------------------------------------------------
@@ -131,7 +134,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ allowsNullTreatment, allowsSeparator, percentile);
}
/** Sets {@link #getFunctionType()}. */
@@ -140,7 +143,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), category, requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ allowsNullTreatment, allowsSeparator, percentile);
}
@Override public SqlSyntax getSyntax() {
@@ -153,7 +156,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ allowsNullTreatment, allowsSeparator, percentile);
}
@Override public boolean allowsNullTreatment() {
@@ -166,7 +169,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ allowsNullTreatment, allowsSeparator, percentile);
}
/** Returns whether this aggregate function allows '{@code SEPARATOR string}'
@@ -180,8 +183,21 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
return new SqlBasicAggFunction(getName(), getSqlIdentifier(), kind,
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
- requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
+ allowsNullTreatment, allowsSeparator, percentile);
+ }
+
+ @Override public boolean isPercentile() {
+ return percentile;
+ }
+
+ /** Sets {@link #isPercentile()}. */
+ public SqlBasicAggFunction withPercentile(boolean percentile) {
+ return new SqlBasicAggFunction(getName(), getSqlIdentifier(), kind,
+ getReturnTypeInference(), getOperandTypeInference(),
+ getOperandTypeChecker(), getFunctionType(), requiresOrder(),
+ requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
+ allowsNullTreatment, allowsSeparator, percentile);
}
/** Sets {@link #requiresGroupOrder()}. */
@@ -190,6 +206,6 @@ public final class SqlBasicAggFunction extends SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), groupOrder, distinctOptionality, syntax,
- allowsNullTreatment, allowsSeparator);
+ allowsNullTreatment, allowsSeparator, percentile);
}
}
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 c228f39..6b93370 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
@@ -2234,6 +2234,36 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
.withGroupOrder(Optionality.OPTIONAL);
/**
+ * {@code PERCENTILE_CONT} inverse distribution aggregate function.
+ *
+ * <p>The argument must be a numeric literal in the range 0 to 1 inclusive
+ * (representing a percentage), and the return type is {@code DOUBLE}.
+ */
+ public static final SqlAggFunction PERCENTILE_CONT =
+ SqlBasicAggFunction
+ .create(SqlKind.PERCENTILE_CONT, ReturnTypes.DOUBLE,
+ OperandTypes.UNIT_INTERVAL_NUMERIC_LITERAL)
+ .withFunctionType(SqlFunctionCategory.SYSTEM)
+ .withGroupOrder(Optionality.MANDATORY)
+ .withPercentile(true);
+
+ /**
+ * {@code PERCENTILE_DISC} inverse distribution aggregate function.
+ *
+ * <p>The argument must be a numeric literal in the range 0 to 1 inclusive
+ * (representing a percentage), and the return type is {@code DOUBLE}.
+ * (The return type should determined by the type of the {@code ORDER BY}
+ * expression, but this cannot be determined by the function itself.)
+ */
+ public static final SqlAggFunction PERCENTILE_DISC =
+ SqlBasicAggFunction
+ .create(SqlKind.PERCENTILE_DISC, ReturnTypes.DOUBLE,
+ OperandTypes.UNIT_INTERVAL_NUMERIC_LITERAL)
+ .withFunctionType(SqlFunctionCategory.SYSTEM)
+ .withGroupOrder(Optionality.MANDATORY)
+ .withPercentile(true);
+
+ /**
* The LISTAGG operator. String aggregator function.
*/
public static final SqlAggFunction LISTAGG =
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 9596bde..1658cb8 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -370,6 +370,49 @@ public abstract class OperandTypes {
};
/**
+ * Operand type-checking strategy type must be a numeric non-NULL
+ * literal in the range 0 and 1 inclusive.
+ */
+ public static final SqlSingleOperandTypeChecker UNIT_INTERVAL_NUMERIC_LITERAL =
+ new FamilyOperandTypeChecker(ImmutableList.of(SqlTypeFamily.NUMERIC),
+ i -> false) {
+ @Override public boolean checkSingleOperandType(
+ SqlCallBinding callBinding,
+ SqlNode node,
+ int iFormalOperand,
+ boolean throwOnFailure) {
+ if (!LITERAL.checkSingleOperandType(
+ callBinding,
+ node,
+ iFormalOperand,
+ throwOnFailure)) {
+ return false;
+ }
+
+ if (!super.checkSingleOperandType(
+ callBinding,
+ node,
+ iFormalOperand,
+ throwOnFailure)) {
+ return false;
+ }
+
+ final SqlLiteral arg = (SqlLiteral) node;
+ final BigDecimal value = arg.getValueAs(BigDecimal.class);
+ if (value.compareTo(BigDecimal.ZERO) < 0
+ || value.compareTo(BigDecimal.ONE) > 0) {
+ if (throwOnFailure) {
+ throw callBinding.newError(
+ RESOURCE.argumentMustBeNumericLiteralInRange(
+ callBinding.getOperator().getName(), 0, 1));
+ }
+ return false;
+ }
+ return true;
+ }
+ };
+
+ /**
* Operand type-checking strategy where two operands must both be in the
* same type family.
*/
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFamily.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFamily.java
index 6f07924..d7a185e 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFamily.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFamily.java
@@ -21,6 +21,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.sql.SqlIntervalQualifier;
+import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.parser.SqlParserPos;
import com.google.common.collect.ImmutableList;
@@ -30,6 +31,7 @@ import org.checkerframework.checker.nullness.qual.Nullable;
import java.sql.Types;
import java.util.Collection;
+import java.util.List;
import java.util.Map;
/**
@@ -128,6 +130,40 @@ public enum SqlTypeFamily implements RelDataTypeFamily {
return JDBC_TYPE_TO_FAMILY.get(jdbcType);
}
+ /** For this type family, returns the allow types of the difference between
+ * two values of this family.
+ *
+ * <p>Equivalently, given an {@code ORDER BY} expression with one key,
+ * returns the allowable type families of the difference between two keys.
+ *
+ * <p>Example 1. For {@code ORDER BY empno}, a NUMERIC, the difference
+ * between two {@code empno} values is also NUMERIC.
+ *
+ * <p>Example 2. For {@code ORDER BY hireDate}, a DATE, the difference
+ * between two {@code hireDate} values might be an INTERVAL_DAY_TIME
+ * or INTERVAL_YEAR_MONTH.
+ *
+ * <p>The result determines whether a {@link SqlWindow} with a {@code RANGE}
+ * is valid (for example, {@code OVER (ORDER BY empno RANGE 10} is valid
+ * because {@code 10} is numeric);
+ * and whether a call to
+ * {@link org.apache.calcite.sql.fun.SqlStdOperatorTable#PERCENTILE_CONT PERCENTILE_CONT}
+ * is valid (for example, {@code PERCENTILE_CONT(0.25)} ORDER BY (hireDate)}
+ * is valid because {@code hireDate} values may be interpolated by adding
+ * values of type {@code INTERVAL_DAY_TIME}. */
+ public List<SqlTypeFamily> allowableDifferenceTypes() {
+ switch (this) {
+ case NUMERIC:
+ return ImmutableList.of(NUMERIC);
+ case DATE:
+ case TIME:
+ case TIMESTAMP:
+ return ImmutableList.of(INTERVAL_DAY_TIME, INTERVAL_YEAR_MONTH);
+ default:
+ return ImmutableList.of();
+ }
+ }
+
/** Returns the collection of {@link SqlTypeName}s included in this family. */
public Collection<SqlTypeName> getTypeNames() {
switch (this) {
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 2812ae5..6fe6207 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
@@ -89,6 +89,7 @@ import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandTypeChecker;
import org.apache.calcite.sql.type.SqlOperandTypeInference;
import org.apache.calcite.sql.type.SqlTypeCoercionRule;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.util.IdPair;
@@ -102,6 +103,7 @@ import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.ImmutableNullableList;
import org.apache.calcite.util.Litmus;
+import org.apache.calcite.util.Optionality;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Static;
import org.apache.calcite.util.Util;
@@ -5921,6 +5923,31 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
default:
throw new AssertionError(op);
}
+
+ if (op.isPercentile()) {
+ assert op.requiresGroupOrder() == Optionality.MANDATORY;
+ assert orderList != null;
+
+ // Validate that percentile function have a single ORDER BY expression
+ if (orderList.size() != 1) {
+ throw newValidationError(orderList,
+ RESOURCE.orderByRequiresOneKey(op.getName()));
+ }
+
+ // Validate that the ORDER BY field is of NUMERIC type
+ SqlNode node = orderList.get(0);
+ assert node != null;
+
+ final RelDataType type = deriveType(scope, node);
+ final @Nullable SqlTypeFamily family = type.getSqlTypeName().getFamily();
+ if (family == null
+ || family.allowableDifferenceTypes().isEmpty()) {
+ throw newValidationError(orderList,
+ RESOURCE.unsupportedTypeInOrderBy(
+ type.getSqlTypeName().getName(),
+ op.getName()));
+ }
+ }
}
@Override public void validateCall(
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 3969009..85768e7 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -159,6 +159,8 @@ PartitionNotAllowed=PARTITION BY not allowed with existing window reference
OrderByOverlap=ORDER BY not allowed in both base and referenced windows
RefWindowWithFrame=Referenced window cannot have framing declarations
TypeNotSupported=Type ''{0}'' is not supported
+UnsupportedTypeInOrderBy=Invalid type ''{0}'' in ORDER BY clause of ''{1}'' function. Only NUMERIC types are supported
+OrderByRequiresOneKey=''{0}'' requires precisely one ORDER BY key
FunctionQuantifierNotAllowed=DISTINCT/ALL not allowed with {0} function
WithinGroupNotAllowed=WITHIN GROUP not allowed with {0} function
WithinDistinctNotAllowed=WITHIN DISTINCT not allowed with {0} function
@@ -180,6 +182,7 @@ DuplicateColumnName=Duplicate column name ''{0}'' in output
Internal=Internal error: {0}
ArgumentMustBeLiteral=Argument to function ''{0}'' must be a literal
ArgumentMustBePositiveInteger=Argument to function ''{0}'' must be a positive integer literal
+ArgumentMustBeNumericLiteralInRange=Argument to function ''{0}'' must be a numeric literal between {1,number,#} and {2,number,#}
ValidationError=Validation Error: {0}
IllegalLocaleFormat=Locale ''{0}'' in an illegal format
ArgumentMustNotBeNull=Argument to function ''{0}'' must not be NULL
diff --git a/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java b/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java
index c55a7c6..8aefb4d 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/AbstractSqlTester.java
@@ -585,6 +585,11 @@ public abstract class AbstractSqlTester implements SqlTester, AutoCloseable {
* @return Query that evaluates a scalar expression
*/
protected String buildQuery2(String expression) {
+ if (expression.matches("(?i).*percentile_(cont|disc).*")) {
+ // PERCENTILE_CONT requires its argument to be a literal,
+ // so converting its argument to a column will cause false errors.
+ return buildQuery(expression);
+ }
// "values (1 < 5)"
// becomes
// "select p0 < p1 from (values (1, 5)) as t(p0, p1)"
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index c278aeb..e2957ea 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -198,6 +198,8 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(NULLIF)",
"KEYWORD(OCTET_LENGTH)",
"KEYWORD(OVERLAY)",
+ "KEYWORD(PERCENTILE_CONT)",
+ "KEYWORD(PERCENTILE_DISC)",
"KEYWORD(PERCENT_RANK)",
"KEYWORD(PERIOD)",
"KEYWORD(POSITION)",
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 1ca3b0a..e0e2531 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
@@ -8947,6 +8947,38 @@ public abstract class SqlOperatorBaseTest {
VM_JAVA);
}
+ @Test void testPercentileContFunc() {
+ tester.setFor(SqlStdOperatorTable.PERCENTILE_CONT, VM_FENNEL, VM_JAVA);
+ tester.checkType("percentile_cont(0.25) within group (order by 1)",
+ "DOUBLE NOT NULL");
+ tester.checkFails("percentile_cont(0.25) within group (^order by 'a'^)",
+ "Invalid type 'CHAR' in ORDER BY clause of 'PERCENTILE_CONT' function. "
+ + "Only NUMERIC types are supported", false);
+ tester.checkFails("percentile_cont(0.25) within group (^order by 1, 2^)",
+ "'PERCENTILE_CONT' requires precisely one ORDER BY key", false);
+ tester.checkFails(" ^percentile_cont(2 + 3)^ within group (order by 1)",
+ "Argument to function 'PERCENTILE_CONT' must be a literal", false);
+ tester.checkFails(" ^percentile_cont(2)^ within group (order by 1)",
+ "Argument to function 'PERCENTILE_CONT' must be a numeric literal "
+ + "between 0 and 1", false);
+ }
+
+ @Test void testPercentileDiscFunc() {
+ tester.setFor(SqlStdOperatorTable.PERCENTILE_DISC, VM_FENNEL, VM_JAVA);
+ tester.checkType("percentile_disc(0.25) within group (order by 1)",
+ "DOUBLE NOT NULL");
+ tester.checkFails("percentile_disc(0.25) within group (^order by 'a'^)",
+ "Invalid type 'CHAR' in ORDER BY clause of 'PERCENTILE_DISC' function. "
+ + "Only NUMERIC types are supported", false);
+ tester.checkFails("percentile_disc(0.25) within group (^order by 1, 2^)",
+ "'PERCENTILE_DISC' requires precisely one ORDER BY key", false);
+ tester.checkFails(" ^percentile_disc(2 + 3)^ within group (order by 1)",
+ "Argument to function 'PERCENTILE_DISC' must be a literal", false);
+ tester.checkFails(" ^percentile_disc(2)^ within group (order by 1)",
+ "Argument to function 'PERCENTILE_DISC' must be a numeric literal "
+ + "between 0 and 1", false);
+ }
+
@Test void testCountFunc() {
tester.setFor(SqlStdOperatorTable.COUNT, VM_EXPAND);
tester.checkType("count(*)", "BIGINT NOT NULL");
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 e81bb01..f8def7f 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3871,6 +3871,39 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-4644">[CALCITE-4644]
+ * Add PERCENTILE_CONT and PERCENTILE_DISC aggregate functions</a>. */
+ @Test void testPercentileCont() {
+ final String sql = "select\n"
+ + " percentile_cont(0.25) within group (order by deptno)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ @Test void testPercentileContWithGroupBy() {
+ final String sql = "select deptno,\n"
+ + " percentile_cont(0.25) within group (order by empno desc)\n"
+ + "from emp\n"
+ + "group by deptno";
+ sql(sql).ok();
+ }
+
+ @Test void testPercentileDisc() {
+ final String sql = "select\n"
+ + " percentile_disc(0.25) within group (order by deptno)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ @Test void testPercentileDiscWithGroupBy() {
+ final String sql = "select deptno,\n"
+ + " percentile_disc(0.25) within group (order by empno)\n"
+ + "from emp\n"
+ + "group by deptno";
+ sql(sql).ok();
+ }
+
@Test void testOrderByRemoval1() {
final String sql = "select * from (\n"
+ " select empno from emp order by deptno offset 0) t\n"
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 cfab141..1e98baa 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7537,6 +7537,75 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
.fails("WITHIN GROUP must not contain aggregate expression");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-4644">[CALCITE-4644]
+ * Add PERCENTILE_CONT and PERCENTILE_DISC aggregate functions</a>. */
+ @Test void testPercentile() {
+ final String sql = "select\n"
+ + " percentile_cont(0.25) within group (order by sal) as c,\n"
+ + " percentile_disc(0.5) within group (order by sal desc) as d\n"
+ + "from emp\n"
+ + "group by deptno";
+ sql(sql)
+ .type("RecordType(DOUBLE NOT NULL C, DOUBLE NOT NULL D) NOT NULL");
+ }
+
+ /** Tests that {@code PERCENTILE_CONT} only allows numeric fields. */
+ @Test void testPercentileContMustOrderByNumeric() {
+ final String sql = "select\n"
+ + " percentile_cont(0.25) within group (^order by ename^)\n"
+ + "from emp";
+ sql(sql)
+ .fails("Invalid type 'VARCHAR' in ORDER BY clause of "
+ + "'PERCENTILE_CONT' function. Only NUMERIC types are supported");
+ }
+
+ /** Tests that {@code PERCENTILE_CONT} only allows one sort key. */
+ @Test void testPercentileContMultipleOrderByFields() {
+ final String sql = "select\n"
+ + " percentile_cont(0.25) within group (^order by deptno, empno^)\n"
+ + "from emp";
+ sql(sql)
+ .fails("'PERCENTILE_CONT' requires precisely one ORDER BY key");
+ }
+
+ @Test void testPercentileContFractionMustBeLiteral() {
+ final String sql = "select\n"
+ + " ^percentile_cont(deptno)^ within group (order by empno)\n"
+ + "from emp\n"
+ + "group by deptno";
+ sql(sql)
+ .fails("Argument to function 'PERCENTILE_CONT' must be a literal");
+ }
+
+ @Test void testPercentileContFractionOutOfRange() {
+ final String sql = "select\n"
+ + " ^percentile_cont(1.5)^ within group (order by deptno)\n"
+ + "from emp";
+ sql(sql)
+ .fails("Argument to function 'PERCENTILE_CONT' must be a numeric "
+ + "literal between 0 and 1");
+ }
+
+ /** Tests that {@code PERCENTILE_DISC} only allows numeric fields. */
+ @Test void testPercentileDiscMustOrderByNumeric() {
+ final String sql = "select\n"
+ + " percentile_disc(0.25) within group (^order by ename^)\n"
+ + "from emp";
+ sql(sql)
+ .fails("Invalid type 'VARCHAR' in ORDER BY clause of "
+ + "'PERCENTILE_DISC' function. Only NUMERIC types are supported");
+ }
+
+ /** Tests that {@code PERCENTILE_DISC} only allows one sort key. */
+ @Test void testPercentileDiscMultipleOrderByFields() {
+ final String sql = "select\n"
+ + " percentile_disc(0.25) within group (^order by deptno, empno^)\n"
+ + "from emp";
+ sql(sql)
+ .fails("'PERCENTILE_DISC' requires precisely one ORDER BY key");
+ }
+
@Test void testCorrelatingVariables() {
// reference to unqualified correlating column
sql("select * from emp where exists (\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 ab1cae9..f12cc03 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4851,6 +4851,64 @@ LogicalProject(EXPR$0=[LAST_VALUE($7) OVER (ORDER BY $0)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testPercentileCont">
+ <Resource name="sql">
+ <![CDATA[select
+ percentile_cont(0.25) within group (order by deptno)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[PERCENTILE_CONT($0) WITHIN GROUP ([1])])
+ LogicalProject($f0=[0.25:DECIMAL(3, 2)], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPercentileContWithGroupBy">
+ <Resource name="sql">
+ <![CDATA[select deptno,
+ percentile_cont(0.25) within group (order by empno desc)
+from emp
+group by deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[PERCENTILE_CONT($1) WITHIN GROUP ([2 DESC])])
+ LogicalProject(DEPTNO=[$7], $f1=[0.25:DECIMAL(3, 2)], EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPercentileDisc">
+ <Resource name="sql">
+ <![CDATA[select
+ percentile_disc(0.25) within group (order by deptno)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{}], EXPR$0=[PERCENTILE_DISC($0) WITHIN GROUP ([1])])
+ LogicalProject($f0=[0.25:DECIMAL(3, 2)], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPercentileDiscWithGroupBy">
+ <Resource name="sql">
+ <![CDATA[select deptno,
+ percentile_disc(0.25) within group (order by empno)
+from emp
+group by deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalAggregate(group=[{0}], EXPR$1=[PERCENTILE_DISC($1) WITHIN GROUP ([2])])
+ LogicalProject(DEPTNO=[$7], $f1=[0.25:DECIMAL(3, 2)], EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testPivot">
<Resource name="sql">
<![CDATA[SELECT *
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index bd39d16..85b5259 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1861,6 +1861,21 @@ Not implemented:
* REGR_SLOPE(numeric1, numeric2)
* REGR_SXY(numeric1, numeric2)
+#### Ordered-Set Aggregate Functions
+
+The syntax is as for *aggregateCall*, except that `WITHIN GROUP` is
+required.
+
+In the following:
+
+* *fraction* is a numeric literal between 0 and 1, inclusive, and
+ represents a percentage
+
+| Operator syntax | Description
+|:---------------------------------- |:-----------
+| PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY orderItem) | Returns a percentile based on a continuous distribution of the column values, interpolating between adjacent input items if needed
+| PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY orderItem [, orderItem ]*) | Returns a percentile based on a discrete distribution of the column values returning the first input value whose position in the ordering equals or exceeds the specified fraction
+
### Window functions
Syntax: