[ https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16101209#comment-16101209 ]
Julian Hyde commented on CALCITE-1882: -------------------------------------- Update: [~vlsi] is reviewing https://github.com/apache/calcite/pull/502. > 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 (v6.4.14#64029)