This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 58120b2d84 [CALCITE-5564] Add parsing and validation for
PERCENTILE_CONT/PERCENTILE_DISC functions (enabled in BigQuery)
58120b2d84 is described below
commit 58120b2d84a47d4703b26df7c2d91e16b8c754e7
Author: Tanner Clary <[email protected]>
AuthorDate: Wed May 17 10:01:37 2023 -0700
[CALCITE-5564] Add parsing and validation for
PERCENTILE_CONT/PERCENTILE_DISC functions (enabled in BigQuery)
Close apache/calcite#3210
---
core/src/main/codegen/default_config.fmpp | 2 +
core/src/main/codegen/templates/Parser.jj | 58 +++++++++++++++++++++-
.../apache/calcite/runtime/CalciteResource.java | 3 ++
.../calcite/sql/fun/SqlBasicAggFunction.java | 9 ++++
.../calcite/sql/fun/SqlLibraryOperators.java | 28 +++++++++++
.../org/apache/calcite/sql/type/OperandTypes.java | 57 +++++++++++++--------
.../calcite/sql/validate/SqlValidatorImpl.java | 54 ++++++++++++--------
.../calcite/sql2rel/StandardConvertletTable.java | 1 -
.../calcite/runtime/CalciteResource.properties | 1 +
.../apache/calcite/sql/test/SqlAdvisorTest.java | 2 -
.../org/apache/calcite/test/SqlValidatorTest.java | 58 ++++++++++++++++++++++
site/_docs/reference.md | 6 ++-
.../apache/calcite/sql/parser/SqlParserTest.java | 46 +++++++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 28 +++++++++++
14 files changed, 304 insertions(+), 49 deletions(-)
diff --git a/core/src/main/codegen/default_config.fmpp
b/core/src/main/codegen/default_config.fmpp
index b1f6a59bf3..422926b0ee 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -209,6 +209,8 @@ parser: {
"PASSTHROUGH"
"PAST"
"PATH"
+ "PERCENTILE_CONT"
+ "PERCENTILE_DISC"
"PIVOT"
"PLACING"
"PLAN"
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index d818533bbf..fcee00802b 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4056,6 +4056,59 @@ SqlCall StringAggFunctionCall() :
}
}
+/**
+ * Parses both the standard and the BigQuery PERCENTILE_CONT/PERCENTILE_DISC
+ * functions.
+ *
+ * <p>The standard is of the form "PERCENTILE_CONT(fraction)" while BigQuery is
+ * of the form "PERCENTILE_CONT(value, fraction [ {RESPECT | IGNORE} NULLS ]
)".
+ * Handles the parsing of the operator and its operands but not the WITHIN
GROUP
+ * (for the standard) or OVER (for BigQuery) clauses.
+ */
+SqlCall PercentileFunctionCall() :
+{
+ final Span s;
+ SqlOperator op;
+ final SqlNode e;
+ final List<SqlNode> args = new ArrayList<SqlNode>();
+ final Pair<SqlParserPos, SqlOperator> nullTreatment;
+}
+{
+ (
+ <PERCENTILE_CONT> { op = SqlStdOperatorTable.PERCENTILE_CONT; }
+ | <PERCENTILE_DISC> { op = SqlStdOperatorTable.PERCENTILE_DISC; }
+ )
+ { s = span(); }
+ <LPAREN>
+ AddArg(args, ExprContext.ACCEPT_SUB_QUERY)
+ (
+ <RPAREN> {
+ return op.createCall(s.end(this), args);
+ }
+ |
+ <COMMA>
+ e = NumericLiteral() { args.add(e); }
+ (
+ nullTreatment = NullTreatment()
+ | { nullTreatment = null; }
+ )
+ <RPAREN>
+ {
+ op =
+ op == SqlStdOperatorTable.PERCENTILE_CONT
+ ? SqlLibraryOperators.PERCENTILE_CONT2
+ : SqlLibraryOperators.PERCENTILE_DISC2;
+ SqlCall call = op.createCall(s.end(this), args);
+ if (nullTreatment != null) {
+ // Wrap in RESPECT_NULLS or IGNORE_NULLS.
+ call = nullTreatment.right.createCall(nullTreatment.left, call);
+ }
+ return call;
+ }
+ )
+}
+
+
/**
* Parses an atomic row expression.
*/
@@ -7182,6 +7235,9 @@ SqlNode NamedFunctionCall() :
(
LOOKAHEAD(2)
call = StringAggFunctionCall()
+ |
+ LOOKAHEAD(1)
+ call = PercentileFunctionCall()
|
call = NamedCall()
)
@@ -7385,8 +7441,6 @@ 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 451e769516..e062ae1242 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -389,6 +389,9 @@ public interface CalciteResource {
@BaseMessage("Aggregate expression ''{0}'' must not contain a WITHIN GROUP
clause")
ExInst<SqlValidatorException> withinGroupClauseIllegalInAggregate(String a0);
+ @BaseMessage("Percentile functions must have 1 or 2 arguments")
+ ExInst<SqlValidatorException> percentileFunctionsArgumentLimit();
+
@BaseMessage("Aggregate expression is illegal in ORDER BY clause of
non-aggregating SELECT")
ExInst<SqlValidatorException> aggregateIllegalInOrderBy();
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 677998c12d..0c1009d098 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
@@ -222,6 +222,15 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
allowsNullTreatment, allowsSeparator, percentile);
}
+ /** Sets {@link #requiresOver()}. */
+ public SqlBasicAggFunction withOver(boolean over) {
+ return new SqlBasicAggFunction(getName(), getSqlIdentifier(), kind,
+ getReturnTypeInference(), getOperandTypeInference(),
+ getOperandTypeChecker(), staticFun, getFunctionType(), requiresOrder(),
+ over, requiresGroupOrder(), distinctOptionality, syntax,
+ allowsNullTreatment, allowsSeparator, percentile);
+ }
+
/** Sets {@link #requiresGroupOrder()}. */
public SqlBasicAggFunction withGroupOrder(Optionality groupOrder) {
return new SqlBasicAggFunction(getName(), getSqlIdentifier(), kind,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 4da7dd1e33..b5bed9572a 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -581,6 +581,34 @@ public abstract class SqlLibraryOperators {
public static final SqlAggFunction MIN_BY =
SqlStdOperatorTable.ARG_MIN.withName("MIN_BY");
+ /** The {@code PERCENTILE_CONT} function, BigQuery's
+ * equivalent to {@link SqlStdOperatorTable#PERCENTILE_CONT},
+ * but uses an {@code OVER} clause rather than {@code WITHIN GROUP}. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlAggFunction PERCENTILE_CONT2 =
+ SqlBasicAggFunction
+ .create("PERCENTILE_CONT", SqlKind.PERCENTILE_CONT,
+ ReturnTypes.DOUBLE,
+ OperandTypes.NUMERIC_UNIT_INTERVAL_NUMERIC_LITERAL)
+ .withFunctionType(SqlFunctionCategory.SYSTEM)
+ .withOver(true)
+ .withPercentile(true)
+ .withAllowsNullTreatment(true);
+
+ /** The {@code PERCENTILE_DISC} function, BigQuery's
+ * equivalent to {@link SqlStdOperatorTable#PERCENTILE_DISC},
+ * but uses an {@code OVER} clause rather than {@code WITHIN GROUP}. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlAggFunction PERCENTILE_DISC2 =
+ SqlBasicAggFunction
+ .create("PERCENTILE_DISC", SqlKind.PERCENTILE_DISC,
+ ReturnTypes.ARG0,
+ OperandTypes.NUMERIC_UNIT_INTERVAL_NUMERIC_LITERAL)
+ .withFunctionType(SqlFunctionCategory.SYSTEM)
+ .withOver(true)
+ .withPercentile(true)
+ .withAllowsNullTreatment(true);
+
/** The "DATE" function. It has the following overloads:
*
* <ul>
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 855161da3e..3b4de3ebcb 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
@@ -505,12 +505,12 @@ public abstract class OperandTypes {
i -> false) {
@Override public boolean checkSingleOperandType(
SqlCallBinding callBinding,
- SqlNode node,
+ SqlNode operand,
int iFormalOperand,
boolean throwOnFailure) {
if (!LITERAL.checkSingleOperandType(
callBinding,
- node,
+ operand,
iFormalOperand,
throwOnFailure)) {
return false;
@@ -518,13 +518,13 @@ public abstract class OperandTypes {
if (!super.checkSingleOperandType(
callBinding,
- node,
+ operand,
iFormalOperand,
throwOnFailure)) {
return false;
}
- final SqlLiteral arg = (SqlLiteral) node;
+ final SqlLiteral arg = (SqlLiteral) operand;
final BigDecimal value = arg.getValueAs(BigDecimal.class);
if (value.compareTo(BigDecimal.ZERO) < 0
|| hasFractionalPart(value)) {
@@ -554,34 +554,26 @@ public abstract class OperandTypes {
};
/**
- * Operand type-checking strategy type must be a numeric non-NULL
- * literal in the range 0 and 1 inclusive.
+ * Operand type-checking strategy where type must be a numeric non-NULL
+ * literal in the range 0 to 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)) {
+ SqlCallBinding callBinding, SqlNode operand,
+ int iFormalOperand, boolean throwOnFailure) {
+ if (!LITERAL.checkSingleOperandType(callBinding, operand,
+ iFormalOperand, throwOnFailure)) {
return false;
}
- if (!super.checkSingleOperandType(
- callBinding,
- node,
- iFormalOperand,
- throwOnFailure)) {
+ if (!super.checkSingleOperandType(callBinding, operand,
+ iFormalOperand, throwOnFailure)) {
return false;
}
- final SqlLiteral arg = (SqlLiteral) node;
+ final SqlLiteral arg = (SqlLiteral) operand;
final BigDecimal value = arg.getValueAs(BigDecimal.class);
if (value.compareTo(BigDecimal.ZERO) < 0
|| value.compareTo(BigDecimal.ONE) > 0) {
@@ -596,6 +588,29 @@ public abstract class OperandTypes {
}
};
+ /**
+ * Operand type-checking strategy where the first operand must be numeric and
+ * the second operand must be a numeric non-NULL literal in the range 0 to 1
+ * inclusive.
+ */
+ public static final SqlSingleOperandTypeChecker
+ NUMERIC_UNIT_INTERVAL_NUMERIC_LITERAL =
+ new FamilyOperandTypeChecker(
+ ImmutableList.of(SqlTypeFamily.NUMERIC, SqlTypeFamily.NUMERIC),
+ i -> false) {
+ @Override public boolean checkSingleOperandType(
+ SqlCallBinding callBinding, SqlNode operand,
+ int iFormalOperand, boolean throwOnFailure) {
+ if (iFormalOperand == 0) {
+ return super.checkSingleOperandType(callBinding, operand,
+ iFormalOperand, throwOnFailure);
+ }
+
+ return UNIT_INTERVAL_NUMERIC_LITERAL.checkSingleOperandType(
+ callBinding, operand, 0, throwOnFailure);
+ }
+ };
+
/**
* 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/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index b7ca08bcc6..387a36d33b 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
@@ -6148,28 +6148,40 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
throw new AssertionError(op);
}
+ // Because there are two forms of the PERCENTILE_CONT/PERCENTILE_DISC
functions,
+ // they are distinguished by their operand count and then validated
accordingly.
+ // For example, the standard single operand form requires group order
while the
+ // 2-operand form allows for null treatment and requires an OVER() clause.
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()));
+ switch (aggCall.operandCount()) {
+ case 1:
+ 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()));
+ }
+ break;
+ case 2:
+ assert op.allowsNullTreatment();
+ assert op.requiresOver();
+ assert op.requiresGroupOrder() == Optionality.FORBIDDEN;
+ break;
+ default:
+ throw newValidationError(aggCall,
RESOURCE.percentileFunctionsArgumentLimit());
}
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 60718dff6e..0ffb53031e 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -284,7 +284,6 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
// "POSITION(substring, string, position, occurrence)"
registerOp(SqlLibraryOperators.INSTR,
StandardConvertletTable::convertInstr);
-
// REVIEW jvs 24-Apr-2006: This only seems to be working from within a
// windowed agg. I have added an optimizer rule
// org.apache.calcite.rel.rules.AggregateReduceFunctionsRule which handles
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 28d065e0d2..86c6fbac26 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -131,6 +131,7 @@ AggregateInWithinGroupIllegal=WITHIN GROUP must not contain
aggregate expression
AggregateInWithinDistinctIllegal=WITHIN DISTINCT 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
+PercentileFunctionsArgumentLimit=Percentile functions must have 1 or 2
arguments
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
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 15a1e6c349..eba04ac157 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
@@ -210,8 +210,6 @@ 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/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 538333dfee..c1df80bd00 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6536,6 +6536,64 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.fails("'PERCENTILE_DISC' requires precisely one ORDER BY key");
}
+ @Test void testPercentileFunctionsBigQuery() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.BIG_QUERY);
+ final String sql = "select\n"
+ + " percentile_cont(sal, 0.25) over() as c,\n"
+ + " percentile_disc(sal, 0.5) over() as d\n"
+ + "from emp";
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .withOperatorTable(opTable)
+ .type("RecordType(DOUBLE NOT NULL C, INTEGER NOT NULL D) NOT NULL");
+ }
+
+ @Test void testPercentileContBigQueryFraction() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.BIG_QUERY);
+ final String sql = "select\n"
+ + "^percentile_cont(sal, 1.5)^ over() as c\n"
+ + "from emp as x";
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .withOperatorTable(opTable)
+ .fails("Argument to function 'PERCENTILE_CONT' must be a numeric "
+ + "literal between 0 and 1");
+ }
+
+ @Test void testPercentileContBigQueryAllowsNullTreatment() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.BIG_QUERY);
+ final String sql = "select\n"
+ + "percentile_cont(sal, 1 RESPECT NULLS) over() as c\n"
+ + "from emp";
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .withOperatorTable(opTable)
+ .type("RecordType(DOUBLE NOT NULL C) NOT NULL");
+ }
+
+ @Test void testPercentileDiscBigQueryFraction() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.BIG_QUERY);
+ final String sql = "select\n"
+ + "^percentile_disc(sal, 1.5)^ over() as c\n"
+ + "from emp";
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .withOperatorTable(opTable)
+ .fails("Argument to function 'PERCENTILE_DISC' must be a numeric "
+ + "literal between 0 and 1");
+ }
+
+ @Test void testPercentileDiscBigQueryAllowsNullTreatment() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.BIG_QUERY);
+ final String sql = "select\n"
+ + "percentile_disc(sal, 1 RESPECT NULLS) over() as c\n"
+ + "from emp";
+ sql(sql)
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .withOperatorTable(opTable)
+ .type("RecordType(INTEGER NOT NULL C) NOT NULL");
+ }
+
@Test void testCorrelatingVariables() {
// reference to unqualified correlating column
sql("select * from emp where exists (\n"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 7baa3d64df..3474d0e9e5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -827,8 +827,8 @@ PATH,
**PATTERN**,
**PER**,
**PERCENT**,
-**PERCENTILE_CONT**,
-**PERCENTILE_DISC**,
+PERCENTILE_CONT,
+PERCENTILE_DISC,
**PERCENT_RANK**,
**PERIOD**,
**PERMUTE**,
@@ -2843,6 +2843,8 @@ Dialect-specific aggregate functions.
| b | LOGICAL_OR(condition) | Synonym for `SOME`
| s | MAX_BY(value, comp) | Synonym for `ARG_MAX`
| s | MIN_BY(value, comp) | Synonym for `ARG_MIN`
+| b | PERCENTILE_CONT(value, fraction [ RESPECT NULLS | IGNORE NULLS ] )
OVER windowSpec | Synonym for standard `PERCENTILE_CONT` where
`PERCENTILE_CONT(value, fraction) OVER (ORDER BY value)` is equivalent to
standard `PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY value)`
+| b | PERCENTILE_DISC(value, fraction [ RESPECT NULLS | IGNORE NULLS ] )
OVER windowSpec | Synonym for standard `PERCENTILE_DISC` where
`PERCENTILE_DISC(value, fraction) OVER (ORDER BY value)` is equivalent to
standard `PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY value)`
| b p | STRING_AGG( [ ALL | DISTINCT ] value [, separator] [ ORDER BY
orderItem [, orderItem ]* ] ) | Synonym for `LISTAGG`
Usage Examples:
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index c09043c96e..a4db920839 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -749,6 +749,52 @@ public class SqlParserTest {
.fails("(?s)Encountered \"\\*\" at .*");
}
+ @Test void testPercentileCont() {
+ sql("select percentile_cont(.5) within group (order by 3) from t")
+ .ok("SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 3)\n"
+ + "FROM `T`");
+ }
+
+ @Test void testPercentileDisc() {
+ sql("select percentile_disc(.5) within group (order by 3) from t")
+ .ok("SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY 3)\n"
+ + "FROM `T`");
+ }
+
+ /** Tests BigQuery's variant of PERCENTILE_CONT, which uses OVER rather than
+ * WITHIN GROUP, and allows RESPECT/IGNORE NULLS inside the parentheses. */
+ @Test void testPercentileContBigQuery() {
+ sql("select percentile_cont(x, .5) over() from unnest(array[1,2,3,4]) as
x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_CONT(x, 0.5) OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, 2, 3, 4])) AS x");
+ sql("select percentile_cont(x, .5 RESPECT NULLS) over() from
unnest(array[1,2,3,4]) as x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_CONT(x, 0.5) RESPECT NULLS OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, 2, 3, 4])) AS x");
+ sql("select percentile_cont(x, .5 IGNORE NULLS) over() from
unnest(array[1,null,3,4]) as x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_CONT(x, 0.5) IGNORE NULLS OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, NULL, 3, 4])) AS x");
+ }
+
+ /** Tests BigQuery's variant of PERCENTILE_DISC, which uses OVER rather than
+ * WITHIN GROUP, and allows RESPECT/IGNORE NULLS inside the parentheses. */
+ @Test void testPercentileDiscBigQuery() {
+ sql("select percentile_disc(x, .5) over() from unnest(array[1,2,3,4]) as
x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_DISC(x, 0.5) OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, 2, 3, 4])) AS x");
+ sql("select percentile_disc(x, .5 RESPECT NULLS) over() from
unnest(array[1,2,3,4]) as x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_DISC(x, 0.5) RESPECT NULLS OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, 2, 3, 4])) AS x");
+ sql("select percentile_disc(x, .5 IGNORE NULLS) over() from
unnest(array[1,null,3,4]) as x")
+ .withDialect(BIG_QUERY)
+ .ok("SELECT (PERCENTILE_DISC(x, 0.5) IGNORE NULLS OVER ())\n"
+ + "FROM UNNEST((ARRAY[1, NULL, 3, 4])) AS x");
+ }
+
@Test void testHyphenatedTableName() {
sql("select * from bigquery^-^foo-bar.baz")
.fails("(?s)Encountered \"-\" at .*")
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 0c59d35074..df5d59b56f 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -9771,6 +9771,34 @@ public class SqlOperatorTest {
+ "between 0 and 1", false);
}
+ @Test void testPercentileContBigQueryFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.PERCENTILE_CONT2,
SqlOperatorFixture.VmName.EXPAND)
+ .withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkType("percentile_cont(1, .5)",
+ "DOUBLE NOT NULL");
+ f.checkType("percentile_cont(.5, .5 RESPECT NULLS)", "DOUBLE NOT NULL");
+ f.checkType("percentile_cont(1, .5 IGNORE NULLS)", "DOUBLE NOT NULL");
+ f.checkType("percentile_cont(2+3, .5 IGNORE NULLS)", "DOUBLE NOT NULL");
+ f.checkFails("^percentile_cont(1, 1.5)^",
+ "Argument to function 'PERCENTILE_CONT' must be a numeric literal "
+ + "between 0 and 1", false);
+ }
+
+ @Test void testPercentileDiscBigQueryFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.PERCENTILE_DISC2,
SqlOperatorFixture.VmName.EXPAND)
+ .withLibrary(SqlLibrary.BIG_QUERY);
+ f.checkType("percentile_disc(1, .5)",
+ "INTEGER NOT NULL");
+ f.checkType("percentile_disc(1, .5 RESPECT NULLS)", "INTEGER NOT NULL");
+ f.checkType("percentile_disc(0.75, .5 IGNORE NULLS)", "DECIMAL(3, 2) NOT
NULL");
+ f.checkType("percentile_disc(2+3, .5 IGNORE NULLS)", "INTEGER NOT NULL");
+ f.checkFails("^percentile_disc(1, 1.5)^",
+ "Argument to function 'PERCENTILE_DISC' must be a numeric literal "
+ + "between 0 and 1", false);
+ }
+
@Test void testCountFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.COUNT, VM_EXPAND);
