Flink upgrade to 1.10: function

[seeksst]

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[Till Rohrmann]

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[Jark Wu-3]

Hi,

I guess you are already close to the truth. Since Flink 1.10 we upgraded Calcite to 1.21 which reserves JSON_VALUE as keyword.

So that a user define function can't use this name anymore. That's why JSON_VALUE(...) will always be parsed as the Calcite's builtin function definition. 

Currently, I don't have other ideas except using another function name... 

cc [hidden email] , do you know why JSON_VALUE is a reserved keyword in Calcite? Could we change it into non-reserved keyword?

Best,

Jark

On Fri, 17 Apr 2020 at 21:00, Till Rohrmann <[hidden email]> wrote:

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[seeksst]

Hi,

   Thank you for reply.

   I find it caused by SqlStdOperatorTable and have tried many ways to change it, but failed. Finally, I decided to copy it and renamed. Another thing that caught my attention is that i also define last_value function which args is same to SqlStdOperatorTable, and through CalciteConfig to replace SqlOperatorTable, so i can use CustomBasicOperatorTable replace BasicOperatorTable and register my defined last_value function. I change AggregateUtil to make it work. It seems not prefect, do you have any advice to avoid changing AggregateUtil?

  the different thing is that my JSON_VALUE function args is not similar to SqlStdOperatorTable.there may be no way to replace it. I learn more about Build-in Function by source code, JSON_VALUE may not realize.is it necessary to define many functions in SqlStdOperatorTable that not use? as i know, parser may decide many thing, and limit by calcite, so convert may get information which i don’t want and can’t change it. it seems hard to solve, and i have no idea.

   Best,

      L

 原始邮件 

发件人: Jark Wu<[hidden email]>

收件人: Till Rohrmann<[hidden email]>; Danny Chan<[hidden email]>

抄送: seeksst<[hidden email]>; user<[hidden email]>; Timo Walther<[hidden email]>

发送时间: 2020年4月17日(周五) 22:53

主题: Re: Flink upgrade to 1.10: function

Hi,

I guess you are already close to the truth. Since Flink 1.10 we upgraded Calcite to 1.21 which reserves JSON_VALUE as keyword.

So that a user define function can't use this name anymore. That's why JSON_VALUE(...) will always be parsed as the Calcite's builtin function definition. 

Currently, I don't have other ideas except using another function name... 

cc [hidden email] , do you know why JSON_VALUE is a reserved keyword in Calcite? Could we change it into non-reserved keyword?

Best,

Jark

On Fri, 17 Apr 2020 at 21:00, Till Rohrmann <[hidden email]> wrote:

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[Jark Wu-3]

Hi,

I think it is not caused by SqlStdOperatorTable, but the Calcite parser. So it is tricky to work around it. 

Maybe it needs to be fixed from Calcite side.

Best,

Jark

On Sat, 18 Apr 2020 at 01:05, seeksst <[hidden email]> wrote:

Hi,

   Thank you for reply.

   I find it caused by SqlStdOperatorTable and have tried many ways to change it, but failed. Finally, I decided to copy it and renamed. Another thing that caught my attention is that i also define last_value function which args is same to SqlStdOperatorTable, and through CalciteConfig to replace SqlOperatorTable, so i can use CustomBasicOperatorTable replace BasicOperatorTable and register my defined last_value function. I change AggregateUtil to make it work. It seems not prefect, do you have any advice to avoid changing AggregateUtil?

  the different thing is that my JSON_VALUE function args is not similar to SqlStdOperatorTable.there may be no way to replace it. I learn more about Build-in Function by source code, JSON_VALUE may not realize.is it necessary to define many functions in SqlStdOperatorTable that not use? as i know, parser may decide many thing, and limit by calcite, so convert may get information which i don’t want and can’t change it. it seems hard to solve, and i have no idea.

   Best,

      L

 原始邮件 

发件人: Jark Wu<[hidden email]>

收件人: Till Rohrmann<[hidden email]>; Danny Chan<[hidden email]>

抄送: seeksst<[hidden email]>; user<[hidden email]>; Timo Walther<[hidden email]>

发送时间: 2020年4月17日(周五) 22:53

主题: Re: Flink upgrade to 1.10: function

Hi,

I guess you are already close to the truth. Since Flink 1.10 we upgraded Calcite to 1.21 which reserves JSON_VALUE as keyword.

So that a user define function can't use this name anymore. That's why JSON_VALUE(...) will always be parsed as the Calcite's builtin function definition. 

Currently, I don't have other ideas except using another function name... 

cc [hidden email] , do you know why JSON_VALUE is a reserved keyword in Calcite? Could we change it into non-reserved keyword?

Best,

Jark

On Fri, 17 Apr 2020 at 21:00, Till Rohrmann <[hidden email]> wrote:

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[Danny Chan]

The JSON_VALUE was coded into the parser, which is always parsed as the builtin operator, so there is no change to override it yet.

I have fired an issue[1] to track this and hope we can resolve it in the next Calcite release.

[1] https://issues.apache.org/jira/browse/CALCITE-3943

Best,

Danny Chan

在 2020年4月17日 +0800 PM9:00，Till Rohrmann <[hidden email]>，写道：

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.

[Jark Wu-3]

Thanks Danny! 

On Tue, 21 Apr 2020 at 16:24, Danny Chan <[hidden email]> wrote:

The JSON_VALUE was coded into the parser, which is always parsed as the builtin operator, so there is no change to override it yet.

I have fired an issue[1] to track this and hope we can resolve it in the next Calcite release.

[1] https://issues.apache.org/jira/browse/CALCITE-3943

Best,

Danny Chan

在 2020年4月17日 +0800 PM9:00，Till Rohrmann <[hidden email]>，写道：

Hi,

thanks for reporting these problems. I'm pulling in Timo and Jark who are working on the SQL component. They might be able to help you with your problem.

Cheers,

Till

On Thu, Apr 16, 2020 at 11:10 AM seeksst <[hidden email]> wrote:

Hi, All

Recently, I try to upgrade flink from 1.8.2 to 1.10, but i meet some problem about function. In 1.8.2, there are just Built-In function and User-defined Functions, but in 1.10, there are 4 categories of funtions.

I defined a function which named JSON_VALUE in my system, it doesn’t exist in 1.8.2, but present to 1.10.0. of course i use it in sql, something like  'select JSON_VALUE(string, string) from table1’, no category or database. the problem is in 1.10.0, my function will be recognized as SqlJsonValueFunction, and args not match, so my sql is wrong.

        I read document about Ambiguous Function Reference, In my understanding, my function will be registered as temporary system function, and it should be chosen first. isn’t it? I try to debug it, and find some information:

First, sql will be parsed by ParseImpl, and JSON_VALUE will be parsed as SqlBasicCall, operator is SqlJsonValueFunction, it’s belonged to SYSTEM catalog and the kind is OTHER_FUNCTION. Then, SqlUtil.lookupRoutine will not find this SqlFunction, because it not in BasicOperatorTable. my function in FunctionCatalog, but SqlJsonValueFunction belonged to SYSTEM, not belong to USER_DEFINED, so program will not search it in FunctionCatalog.

How can i solve this problem without modifying sql and function name? my program can choose flink version and have many sql jobs, so i don’t wish to modify sql and function name.

Thansk.