>
> I’m getting this error:
> java.sql.SQLException: Error while executing SQL "SELECT
> percentile(159900102,1) as d": There are not enough rules to produce a node
> with desired properties: convention=ENUMERABLE, sort=[].
> Missing conversion is LogicalAggregate[convention: NONE -> ENUMERABLE]
> There is 1 empty subset: rel#2187:RelSubset#1.ENUMERABLE.[], the relevant
> part of the original plan is as follows
>
I guess you are hitting this error because `EnumerableAggregateRule` cannot
apply for some reason, the most common case is that you didn't provide the
implementation, see here[1].
> And with this function definition:
> public static class PercentileAggFunction extends SqlAggFunction {
> public PercentileAggFunction() {
> super("percentile", null, SqlKind.OTHER_FUNCTION, ReturnTypes.DOUBLE,
> null, OperandTypes.family(SqlTypeFamily.NUMERIC,
> SqlTypeFamily.NUMERIC),
> SqlFunctionCategory.NUMERIC, false, false, Optionality.FORBIDDEN);
> }
> }
> @LibraryOperator(libraries = {HIVE})
> public static final PercentileAggFunction PERCENTILE = new
> PercentileAggFunction();
>
> I’m getting this error:
> java.sql.SQLException: Error while executing SQL "SELECT
> percentile(DISTINCT col, 0.3) as D, percentile(159900102,1) as d1 FROM
> (VALUES 0, 10, 10) AS tab(col)": From line 1, column 8 to line 1, column
> 36: No match found for function signature PERCENTILE(<NUMERIC>, <NUMERIC>
>
Your definition seems right, are you using the correct `OperatorTable` that
contains your percentile function? (I put your code into
SqlStdOperatorTable, and your query could pass the validation.)
[1]
https://github.com/apache/calcite/blob/c945b7f49b99538748c871557f6ac80957be2b6e/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableAggregate.java#L70-L75
James Scicluna <[email protected]> 于2022年10月26日周三 18:22写道：
> Hi team,
>
> I’m trying to add a definition for Hive’s percentile(BIGINT col, p)
> function<
> https://cwiki.apache.org/confluence/display/hive/languagemanual+udf#LanguageManualUDF-Built-inAggregateFunctions(UDAF)
> >.
>
> I tried to build this definition following on the similar percentile_cont<
> https://github.com/apache/calcite/blob/1e3c5213c15a0b4ef480c830bb52fddc725907c4/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L2288>
> and MYAGG<
> https://github.com/apache/calcite/blob/1e3c5213c15a0b4ef480c830bb52fddc725907c4/testkit/src/main/java/org/apache/calcite/test/MockSqlOperatorTable.java#L574>
> function definitions. But I’m getting errors when trying to test it via
> BabelQuidemTest<
> https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
> >.
>
> With this function definition:
> @LibraryOperator(libraries = {HIVE})
> public static final SqlAggFunction PERCENTILE =
> SqlBasicAggFunction
> .create(SqlKind.PERCENTILE,
> ReturnTypes.DOUBLE,
> OperandTypes.NUMERIC_NUMERIC)
> .withFunctionType(SqlFunctionCategory.SYSTEM);
>
> I’m getting this error:
> java.sql.SQLException: Error while executing SQL "SELECT
> percentile(159900102,1) as d": There are not enough rules to produce a node
> with desired properties: convention=ENUMERABLE, sort=[].
> Missing conversion is LogicalAggregate[convention: NONE -> ENUMERABLE]
> There is 1 empty subset: rel#2187:RelSubset#1.ENUMERABLE.[], the relevant
> part of the original plan is as follows
>
> And with this function definition:
> public static class PercentileAggFunction extends SqlAggFunction {
> public PercentileAggFunction() {
> super("percentile", null, SqlKind.OTHER_FUNCTION, ReturnTypes.DOUBLE,
> null, OperandTypes.family(SqlTypeFamily.NUMERIC,
> SqlTypeFamily.NUMERIC),
> SqlFunctionCategory.NUMERIC, false, false, Optionality.FORBIDDEN);
> }
> }
> @LibraryOperator(libraries = {HIVE})
> public static final PercentileAggFunction PERCENTILE = new
> PercentileAggFunction();
>
> I’m getting this error:
> java.sql.SQLException: Error while executing SQL "SELECT
> percentile(DISTINCT col, 0.3) as D, percentile(159900102,1) as d1 FROM
> (VALUES 0, 10, 10) AS tab(col)": From line 1, column 8 to line 1, column
> 36: No match found for function signature PERCENTILE(<NUMERIC>, <NUMERIC>
>
>
> Is there something wrong with these function definitions?
>
> Thanks,
>
> James
>
--
Best,
Benchao Li
