[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite

["Vladimir Sitnikov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16737276#comment-16737276 ] 

Vladimir Sitnikov commented on CALCITE-1882:
--------------------------------------------

[~yuqi], I think the root cause of the issue is that Calcite always resolves functions from an operator table first:
https://github.com/apache/calcite/blob/d368fc570a5d0f836c049c6acd73f2dd9f467f79/core/src/main/java/org/apache/calcite/sql/parser/SqlAbstractParserImpl.java#L389-L406

In order to override "sum(..)", we need either allow customization of the {{SqlStdOperatorTable}} that is used in Parser. Alternative option is to provide an option to hide certain names from {{SqlStdOperatorTable}}.

For instance, if I use the following:
{code:java}
    // First, try a half-hearted resolution as a builtin function.
    // If we find one, use it; this will guarantee that we
    // preserve the correct syntax (i.e. don't quote builtin function
    /// name when regenerating SQL).
    if (funName.isSimple()
        && !funName.toString().equals("SUM")
        && !funName.toString().equals("AVG")) {
      final List<SqlOperator> list = new ArrayList<>();
      opTab.lookupOperatorOverloads(funName, funcType, SqlSyntax.FUNCTION, list);
      if (list.size() == 1) {
        fun = list.get(0);
      }
    }{code}
then the test case passes. In other words, it keeps SUM and AVG as {{SqlUnresolvedFunction}}, and resolves it later to user-defined functions.

[~julianhyde], did you have any plans/visions on the way to customize operator table used in Parser?

> Can't obtain the user defined aggregate function such as sum,avg by calcite
> ---------------------------------------------------------------------------
>
>                 Key: CALCITE-1882
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1882
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.12.0
>            Reporter: yuemeng
>            Assignee: yuemeng
>            Priority: Critical
>
> If we want to register a sum or avg aggregate function to deal with different data type such as sum(double) ,we implement a SqlUserDefinedAggFunction and register with name sum,but when we execute a sql like:
> {code}
> select id,sum(payment) from table test group by id
> {code}
> in fact,it always give the SqlSumAggFunction function which builtin by calcite,never find the exactly function which we register by ourself.
> During sql parse process,method createCall in SqlAbstractParserImpl will be called,
> {code}
>   protected SqlCall createCall(
>       SqlIdentifier funName,
>       SqlParserPos pos,
>       SqlFunctionCategory funcType,
>       SqlLiteral functionQualifier,
>       SqlNode[] operands) {
>     SqlOperator fun = null;
>     // First, try a half-hearted resolution as a builtin function.
>     // If we find one, use it; this will guarantee that we
>     // preserve the correct syntax (i.e. don't quote builtin function
>     /// name when regenerating SQL).
>     if (funName.isSimple()) {
>       final List<SqlOperator> list = Lists.newArrayList();
>       opTab.lookupOperatorOverloads(funName, funcType, SqlSyntax.FUNCTION, list);//we lookup in SqlStdOperatorTable,and always find buidin function for sum ,avg .eg
>       if (list.size() == 1) {
>         fun = list.get(0);
>       }
>     }
>     // Otherwise, just create a placeholder function.  Later, during
>     // validation, it will be resolved into a real function reference.
>     if (fun == null) {
>       fun = new SqlUnresolvedFunction(funName, null, null, null, null,
>           funcType);
>     }
>     return fun.createCall(functionQualifier, pos, operands);
>   }
> {code}
> but the problem will be appear in deriveType,because of we get the SqlSumAggFunction previously,and the sqlKind of SqlSumAggFunction is AVG,but the sqlKind of sql user defined agg function (sum) which we register by ourself is OTHER_FUNCTION,so it filter out our sum function.
> {code}
>   private static Iterator<SqlOperator>
>   filterOperatorRoutinesByKind(Iterator<SqlOperator> routines,
>       final SqlKind sqlKind) {
>     return Iterators.filter(routines,
>         new PredicateImpl<SqlOperator>() {
>           public boolean test(SqlOperator input) {
>             return input.getKind() == sqlKind;
>           }
>         });
>   }
> {code}
> that cause we never obtain sum function which registered by user .



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)