Aggregate function definition with two numeric arguments

[James Scicluna]

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