[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16891437#comment-16891437
]
Stamatis Zampetakis commented on CALCITE-1882:
--
Hi [~yuqi], I have the impression that after resolving CALCITE-2282 the
behavior you observed here should no longer be a problem. Can you please verify
if that solution works for you?
> 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
> Labels: pull-request-available
>
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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.14#76016)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
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 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16445123#comment-16445123
]
yuqi commented on CALCITE-1882:
---
Ok, [~vladimirsitnikov] , If you have time, please help to review the code.
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=1649#comment-1649
]
Julian Hyde commented on CALCITE-1882:
--
Other people besides me can review. Can you ask for reviewers on the dev list.
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16444071#comment-16444071
]
yuqi commented on CALCITE-1882:
---
[~julianhyde]
Code have updated, Could you help to review it ? thanks
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16170370#comment-16170370
]
Julian Hyde commented on CALCITE-1882:
--
Those kinds of changes are acceptable. Or, if it is straightforward, you change
the unparse function so that the test output remains the same.
Are there any other failures caused by this change?
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16119470#comment-16119470
]
yuqi commented on CALCITE-1882:
---
Let's take a sql for example, considering sql
{code:java}
select ln(power(2,2)) from ...
{code}
if we convert the ln and power function (in and power are buildin functions) in
createCall, the sql sentence will be
LN(POWER(2, 2))
but, as i mentioned before, if sqlCall function return unresolved function, the
function name in sql sentence wil be quoted as
`LN`(`POWER`(2, 2)), will means LN and POWER are just a placeholder not a clear
function, thus case the test fail.
so, I doubt that change in sqlCall will be a wise method to this question,
what is your opinion about this ? [~julianhyde]
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16107704#comment-16107704
]
Julian Hyde commented on CALCITE-1882:
--
Obviously, hundreds of failing test cases is not OK. What is the nature of the
failures?
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16106042#comment-16106042
]
yuqi commented on CALCITE-1882:
---
The reason that causes this problem is that in parser phase, the parser
directly convert 'SUM' to SqlSumAggFunction not SqlUnresolvedFuntion by name
and at this time user-defined aggregate function has not been registered. this
should be done in the validate phase as at this time converting 'Sum' to
SqlSumAggFunction will cause calcite filters all function that is not
SqlKind.SUM like user-defined aggregate funcion. Converting in the early phase
may accelerate the validate phase later, but it will omit the user-defined
aggregate function, which will lead validator can't find function.
My resolution:
Method of createCall in SqlAbstractParserImpl.java directly returns
SqlUnresolvedFunction not a clear specific function like SqlSumAggFunction. But
if do like this, hundreds of test case fail as createCall has changed and sql
changed greatly after parser. this is quite troublesome.
Is that OK? advice is appreciate, thanks
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16093421#comment-16093421
]
Julian Hyde commented on CALCITE-1882:
--
The built-in SUM aggregate function accepts any kind of numeric arguments. So
I'm not surprised that it wins.
Just curious, if you write a user-defined SUM aggregate function that accepts a
VARCHAR argument, can the validator find it? We haven't tested creating
user-defined functions with the same name as a built-in, so I'm not sure it
works.
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16091559#comment-16091559
]
yuqi commented on CALCITE-1882:
---
I think this is caused by the fact that when construct UserDefineAggFunction,
calcite set the parameter paramType null ,and thus can't distinct which is
buildin and which is created by us
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16082610#comment-16082610
]
Julian Hyde commented on CALCITE-1882:
--
It seems like a bug. Are you able to devise a test case and a fix? If so please
create a pull request.
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
[jira] [Commented] (CALCITE-1882) Can't obtain the user defined aggregate function such as sum,avg by calcite
[
https://issues.apache.org/jira/browse/CALCITE-1882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16081760#comment-16081760
]
yuemeng commented on CALCITE-1882:
--
[~julianhyde]
can you check this issue?
thanks a lot
> 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 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
> filterOperatorRoutinesByKind(Iterator routines,
> final SqlKind sqlKind) {
> return Iterators.filter(routines,
> new PredicateImpl() {
> 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)
