You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hongze Zhang <no...@126.com> on 2019/01/31 18:20:39 UTC
Integrating MySQL's JSON functions
Hi all,
There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL function JSON_TYPE[2], and I want to know what do you think about it.
JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL the use of this function is to get the type of a JSON doc. The similar operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)", however sometimes JSON_TYPE can do more than it.
As for other non-standard JSON functions, I made a simple summary table about JSON support (mainly as functions) details of some of most important SQL implementations. you can open it via link https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
And I suggest to introduce some JSON functions that is from MySQL and might be useful for Calcite but not supported, not Just JSON_TYPE:
1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
I think the PR[1] can be a good start.
Does anyone have thought about this? Looking forward to your reply.
[1] https://issues.apache.org/jira/browse/CALCITE-2791
[2] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
[3] https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
[4] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
Re: Integrating MySQL's JSON functions
Posted by Julian Hyde <jh...@apache.org>.
Yeah, the user has no way to know currently.
We ought to indicate in https://calcite.apache.org/docs/reference.html which functions are in the standard operator table, and which are in extension tables.
Sadly we don’t even document NVL on that page. Logged https://issues.apache.org/jira/browse/CALCITE-2846.
Julian
> On Feb 11, 2019, at 8:04 PM, Hongze Zhang <no...@126.com> wrote:
>
> Thanks for the suggestions Julian and Stamatis!
>
>
> I would support the idea about adding a MysqlSqlOperatorTable to Calcite, what I was not sure about is whether to put MySQL's JSON functions to it. I was wandering if user could easily know (e.g.) JSON_TYPE is a MySQL functions and JSON_VALUE is a built-in function.
> But I understand your opinions and actually I have no strong inclination as long as we can bring the feature to Calcite.
>
>
> Best,
> Hongze
>
>
>
>
>
>
>
>
>
> 在 2019-02-12 02:43:52，"Julian Hyde" <jh...@apache.org> 写道：
>> A MysqlSqlOperatorTable isn’t a bad idea. Each operator table could be a place to collect the operators for that DB.
>>
>> I have a couple of asks:
>> * Don’t include standard operators on that table
>> * If operators are shared with another DB (e.g. the LEAST function will occur in both OracleSqlOperatorTable and MysqlSqlOperatorTable) make sure that the code only occurs in one place.
>>
>> Julian
>>
>>
>>> On Feb 11, 2019, at 5:51 AM, Stamatis Zampetakis <za...@gmail.com> wrote:
>>>
>>> Hi Hongze, Qianjin,
>>>
>>> I apologise fo the late response.
>>>
>>> I still don't understand why it is problematic to create a separate
>>> MySQLOperatorTable.
>>> You can easily combine operator tables (using ChainedSqlOperatorTable for
>>> instance) and pass them around to places were otherwise you would put a
>>> single operator table (e.g., SqlValidator).
>>> It is also easy to use multiple operator tables at a higher level using the
>>> connection property FUN [1].
>>>
>>> Best,
>>> Stamatis
>>>
>>> [1]
>>> https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#FUN
>>>
>>>
>>> Στις Παρ, 1 Φεβ 2019 στις 6:39 π.μ., ο/η ForwardXu <x1...@qq.com> έγραψε:
>>>
>>>> hi statmatis:
>>>> It's also possible that we could do better in both ways, as Hongze Zhang
>>>> said. One of the initial reasons I implemented json_type was to use it in
>>>> flink as well. However, we know that flink is not open to support a dialect
>>>> like mysql.
>>>> best
>>>> qianjin
>>>>
>>>>
>>>>
>>>>
>>>> ------------------ 原始邮件 ------------------
>>>> 发件人: "Hongze Zhang"<no...@126.com>;
>>>> 发送时间: 2019年2月1日(星期五) 中午1:09
>>>> 收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
>>>>
>>>> 主题: Re: Re: Integrating MySQL's JSON functions
>>>>
>>>>
>>>>
>>>> Hi Stamatis,
>>>>
>>>> Thanks for mentioning MySQLOperatorTable!
>>>>
>>>> I just read some code about the usage of OracleOperatorTable in Calcite,
>>>> but I am now not strongly inclined to add MySQL's JSON functions to
>>>> MySQLOperatorTable.
>>>> MySQL's JSON functions are rarely conflict with what are from standard,
>>>> and they extends the JSON ability of MySQL in many sides. I think it might
>>>> be a good try to treat functions from both MySQL's and standard's as a
>>>> whole JSON operator suite.
>>>> Users may want to directly use any function they know, I think it is
>>>> better to not to confuse them with the truth that what's in standard and
>>>> what's not.
>>>> For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key :
>>>> value), I think we can just support both.
>>>>
>>>> What do you think?
>>>>
>>>>
>>>>
>>>> Hongze
>>>>
>>>> From: Stamatis Zampetakis
>>>> Date: 2019-02-01 06:32
>>>> To: dev
>>>> Subject: Re: Integrating MySQL's JSON functions
>>>> Nice document Hongze!
>>>>
>>>> Since the functions are only present in MySQL why not create a
>>>> MySQLOperatorTable and put them there? I went over the discussion in
>>>> CALCITE-2791 but I did not understand why it is preferable to put them in
>>>> the SqlStdOperatorTable.
>>>>
>>>> Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
>>>> έγραψε:
>>>>
>>>>> +1
>>>>>
>>>>> In the reference doc, let’s make it clear that these are in MySQL but not
>>>>> in the standard. (Unlike some of their other extensions to standard SQL,
>>>>> MySQL seem to have done a good job - they are well-designed and
>>>>> well-documented.)
>>>>>
>>>>>> On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
>>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>>
>>>>>> There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
>>>>> function JSON_TYPE[2], and I want to know what do you think about it.
>>>>>>
>>>>>>
>>>>>> JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
>>>>> the use of this function is to get the type of a JSON doc. The similar
>>>>> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
>>>>> however sometimes JSON_TYPE can do more than it.
>>>>>>
>>>>>> As for other non-standard JSON functions, I made a simple summary table
>>>>> about JSON support (mainly as functions) details of some of most
>>>> important
>>>>> SQL implementations. you can open it via link
>>>>>
>>>> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
>>>>>>
>>>>>>
>>>>>> And I suggest to introduce some JSON functions that is from MySQL and
>>>>> might be useful for Calcite but not supported, not Just JSON_TYPE:
>>>>>> 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
>>>>>> 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
>>>>>> I think the PR[1] can be a good start.
>>>>>>
>>>>>>
>>>>>> Does anyone have thought about this? Looking forward to your reply.
>>>>>>
>>>>>>
>>>>>> [1] https://issues.apache.org/jira/browse/CALCITE-2791
>>>>>> [2]
>>>>>
>>>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
>>>>>> [3]
>>>>> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
>>>>>> [4]
>>>>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>>>>>
>>>>>
Re:Re: Integrating MySQL's JSON functions
Posted by Hongze Zhang <no...@126.com>.
Thanks for the suggestions Julian and Stamatis!
I would support the idea about adding a MysqlSqlOperatorTable to Calcite, what I was not sure about is whether to put MySQL's JSON functions to it. I was wandering if user could easily know (e.g.) JSON_TYPE is a MySQL functions and JSON_VALUE is a built-in function.
But I understand your opinions and actually I have no strong inclination as long as we can bring the feature to Calcite.
Best,
Hongze
在 2019-02-12 02:43:52，"Julian Hyde" <jh...@apache.org> 写道：
>A MysqlSqlOperatorTable isn’t a bad idea. Each operator table could be a place to collect the operators for that DB.
>
>I have a couple of asks:
>* Don’t include standard operators on that table
>* If operators are shared with another DB (e.g. the LEAST function will occur in both OracleSqlOperatorTable and MysqlSqlOperatorTable) make sure that the code only occurs in one place.
>
>Julian
>
>
>> On Feb 11, 2019, at 5:51 AM, Stamatis Zampetakis <za...@gmail.com> wrote:
>>
>> Hi Hongze, Qianjin,
>>
>> I apologise fo the late response.
>>
>> I still don't understand why it is problematic to create a separate
>> MySQLOperatorTable.
>> You can easily combine operator tables (using ChainedSqlOperatorTable for
>> instance) and pass them around to places were otherwise you would put a
>> single operator table (e.g., SqlValidator).
>> It is also easy to use multiple operator tables at a higher level using the
>> connection property FUN [1].
>>
>> Best,
>> Stamatis
>>
>> [1]
>> https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#FUN
>>
>>
>> Στις Παρ, 1 Φεβ 2019 στις 6:39 π.μ., ο/η ForwardXu <x1...@qq.com> έγραψε:
>>
>>> hi statmatis:
>>> It's also possible that we could do better in both ways, as Hongze Zhang
>>> said. One of the initial reasons I implemented json_type was to use it in
>>> flink as well. However, we know that flink is not open to support a dialect
>>> like mysql.
>>> best
>>> qianjin
>>>
>>>
>>>
>>>
>>> ------------------ 原始邮件 ------------------
>>> 发件人: "Hongze Zhang"<no...@126.com>;
>>> 发送时间: 2019年2月1日(星期五) 中午1:09
>>> 收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
>>>
>>> 主题: Re: Re: Integrating MySQL's JSON functions
>>>
>>>
>>>
>>> Hi Stamatis,
>>>
>>> Thanks for mentioning MySQLOperatorTable!
>>>
>>> I just read some code about the usage of OracleOperatorTable in Calcite,
>>> but I am now not strongly inclined to add MySQL's JSON functions to
>>> MySQLOperatorTable.
>>> MySQL's JSON functions are rarely conflict with what are from standard,
>>> and they extends the JSON ability of MySQL in many sides. I think it might
>>> be a good try to treat functions from both MySQL's and standard's as a
>>> whole JSON operator suite.
>>> Users may want to directly use any function they know, I think it is
>>> better to not to confuse them with the truth that what's in standard and
>>> what's not.
>>> For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key :
>>> value), I think we can just support both.
>>>
>>> What do you think?
>>>
>>>
>>>
>>> Hongze
>>>
>>> From: Stamatis Zampetakis
>>> Date: 2019-02-01 06:32
>>> To: dev
>>> Subject: Re: Integrating MySQL's JSON functions
>>> Nice document Hongze!
>>>
>>> Since the functions are only present in MySQL why not create a
>>> MySQLOperatorTable and put them there? I went over the discussion in
>>> CALCITE-2791 but I did not understand why it is preferable to put them in
>>> the SqlStdOperatorTable.
>>>
>>> Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
>>> έγραψε:
>>>
>>>> +1
>>>>
>>>> In the reference doc, let’s make it clear that these are in MySQL but not
>>>> in the standard. (Unlike some of their other extensions to standard SQL,
>>>> MySQL seem to have done a good job - they are well-designed and
>>>> well-documented.)
>>>>
>>>>> On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
>>>>>
>>>>> Hi all,
>>>>>
>>>>>
>>>>> There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
>>>> function JSON_TYPE[2], and I want to know what do you think about it.
>>>>>
>>>>>
>>>>> JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
>>>> the use of this function is to get the type of a JSON doc. The similar
>>>> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
>>>> however sometimes JSON_TYPE can do more than it.
>>>>>
>>>>> As for other non-standard JSON functions, I made a simple summary table
>>>> about JSON support (mainly as functions) details of some of most
>>> important
>>>> SQL implementations. you can open it via link
>>>>
>>> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
>>>>>
>>>>>
>>>>> And I suggest to introduce some JSON functions that is from MySQL and
>>>> might be useful for Calcite but not supported, not Just JSON_TYPE:
>>>>> 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
>>>>> 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
>>>>> I think the PR[1] can be a good start.
>>>>>
>>>>>
>>>>> Does anyone have thought about this? Looking forward to your reply.
>>>>>
>>>>>
>>>>> [1] https://issues.apache.org/jira/browse/CALCITE-2791
>>>>> [2]
>>>>
>>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
>>>>> [3]
>>>> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
>>>>> [4]
>>>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>>>>
>>>>
Re: Integrating MySQL's JSON functions
Posted by Julian Hyde <jh...@apache.org>.
A MysqlSqlOperatorTable isn’t a bad idea. Each operator table could be a place to collect the operators for that DB.
I have a couple of asks:
* Don’t include standard operators on that table
* If operators are shared with another DB (e.g. the LEAST function will occur in both OracleSqlOperatorTable and MysqlSqlOperatorTable) make sure that the code only occurs in one place.
Julian
> On Feb 11, 2019, at 5:51 AM, Stamatis Zampetakis <za...@gmail.com> wrote:
>
> Hi Hongze, Qianjin,
>
> I apologise fo the late response.
>
> I still don't understand why it is problematic to create a separate
> MySQLOperatorTable.
> You can easily combine operator tables (using ChainedSqlOperatorTable for
> instance) and pass them around to places were otherwise you would put a
> single operator table (e.g., SqlValidator).
> It is also easy to use multiple operator tables at a higher level using the
> connection property FUN [1].
>
> Best,
> Stamatis
>
> [1]
> https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#FUN
>
>
> Στις Παρ, 1 Φεβ 2019 στις 6:39 π.μ., ο/η ForwardXu <x1...@qq.com> έγραψε:
>
>> hi statmatis:
>> It's also possible that we could do better in both ways, as Hongze Zhang
>> said. One of the initial reasons I implemented json_type was to use it in
>> flink as well. However, we know that flink is not open to support a dialect
>> like mysql.
>> best
>> qianjin
>>
>>
>>
>>
>> ------------------ 原始邮件 ------------------
>> 发件人: "Hongze Zhang"<no...@126.com>;
>> 发送时间: 2019年2月1日(星期五) 中午1:09
>> 收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
>>
>> 主题: Re: Re: Integrating MySQL's JSON functions
>>
>>
>>
>> Hi Stamatis,
>>
>> Thanks for mentioning MySQLOperatorTable!
>>
>> I just read some code about the usage of OracleOperatorTable in Calcite,
>> but I am now not strongly inclined to add MySQL's JSON functions to
>> MySQLOperatorTable.
>> MySQL's JSON functions are rarely conflict with what are from standard,
>> and they extends the JSON ability of MySQL in many sides. I think it might
>> be a good try to treat functions from both MySQL's and standard's as a
>> whole JSON operator suite.
>> Users may want to directly use any function they know, I think it is
>> better to not to confuse them with the truth that what's in standard and
>> what's not.
>> For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key :
>> value), I think we can just support both.
>>
>> What do you think?
>>
>>
>>
>> Hongze
>>
>> From: Stamatis Zampetakis
>> Date: 2019-02-01 06:32
>> To: dev
>> Subject: Re: Integrating MySQL's JSON functions
>> Nice document Hongze!
>>
>> Since the functions are only present in MySQL why not create a
>> MySQLOperatorTable and put them there? I went over the discussion in
>> CALCITE-2791 but I did not understand why it is preferable to put them in
>> the SqlStdOperatorTable.
>>
>> Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
>> έγραψε:
>>
>>> +1
>>>
>>> In the reference doc, let’s make it clear that these are in MySQL but not
>>> in the standard. (Unlike some of their other extensions to standard SQL,
>>> MySQL seem to have done a good job - they are well-designed and
>>> well-documented.)
>>>
>>>> On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
>>>>
>>>> Hi all,
>>>>
>>>>
>>>> There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
>>> function JSON_TYPE[2], and I want to know what do you think about it.
>>>>
>>>>
>>>> JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
>>> the use of this function is to get the type of a JSON doc. The similar
>>> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
>>> however sometimes JSON_TYPE can do more than it.
>>>>
>>>> As for other non-standard JSON functions, I made a simple summary table
>>> about JSON support (mainly as functions) details of some of most
>> important
>>> SQL implementations. you can open it via link
>>>
>> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
>>>>
>>>>
>>>> And I suggest to introduce some JSON functions that is from MySQL and
>>> might be useful for Calcite but not supported, not Just JSON_TYPE:
>>>> 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
>>>> 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
>>>> I think the PR[1] can be a good start.
>>>>
>>>>
>>>> Does anyone have thought about this? Looking forward to your reply.
>>>>
>>>>
>>>> [1] https://issues.apache.org/jira/browse/CALCITE-2791
>>>> [2]
>>>
>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
>>>> [3]
>>> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
>>>> [4]
>>> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>>>
>>>
Re: Re: Integrating MySQL's JSON functions
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Hongze, Qianjin,
I apologise fo the late response.
I still don't understand why it is problematic to create a separate
MySQLOperatorTable.
You can easily combine operator tables (using ChainedSqlOperatorTable for
instance) and pass them around to places were otherwise you would put a
single operator table (e.g., SqlValidator).
It is also easy to use multiple operator tables at a higher level using the
connection property FUN [1].
Best,
Stamatis
[1]
https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#FUN
Στις Παρ, 1 Φεβ 2019 στις 6:39 π.μ., ο/η ForwardXu <x1...@qq.com> έγραψε:
> hi statmatis:
> It's also possible that we could do better in both ways, as Hongze Zhang
> said. One of the initial reasons I implemented json_type was to use it in
> flink as well. However, we know that flink is not open to support a dialect
> like mysql.
> best
> qianjin
>
>
>
>
> ------------------ 原始邮件 ------------------
> 发件人: "Hongze Zhang"<no...@126.com>;
> 发送时间: 2019年2月1日(星期五) 中午1:09
> 收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
>
> 主题: Re: Re: Integrating MySQL's JSON functions
>
>
>
> Hi Stamatis,
>
> Thanks for mentioning MySQLOperatorTable!
>
> I just read some code about the usage of OracleOperatorTable in Calcite,
> but I am now not strongly inclined to add MySQL's JSON functions to
> MySQLOperatorTable.
> MySQL's JSON functions are rarely conflict with what are from standard,
> and they extends the JSON ability of MySQL in many sides. I think it might
> be a good try to treat functions from both MySQL's and standard's as a
> whole JSON operator suite.
> Users may want to directly use any function they know, I think it is
> better to not to confuse them with the truth that what's in standard and
> what's not.
> For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key :
> value), I think we can just support both.
>
> What do you think?
>
>
>
> Hongze
>
> From: Stamatis Zampetakis
> Date: 2019-02-01 06:32
> To: dev
> Subject: Re: Integrating MySQL's JSON functions
> Nice document Hongze!
>
> Since the functions are only present in MySQL why not create a
> MySQLOperatorTable and put them there? I went over the discussion in
> CALCITE-2791 but I did not understand why it is preferable to put them in
> the SqlStdOperatorTable.
>
> Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
> έγραψε:
>
> > +1
> >
> > In the reference doc, let’s make it clear that these are in MySQL but not
> > in the standard. (Unlike some of their other extensions to standard SQL,
> > MySQL seem to have done a good job - they are well-designed and
> > well-documented.)
> >
> > > On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
> > >
> > > Hi all,
> > >
> > >
> > > There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
> > function JSON_TYPE[2], and I want to know what do you think about it.
> > >
> > >
> > > JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
> > the use of this function is to get the type of a JSON doc. The similar
> > operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
> > however sometimes JSON_TYPE can do more than it.
> > >
> > > As for other non-standard JSON functions, I made a simple summary table
> > about JSON support (mainly as functions) details of some of most
> important
> > SQL implementations. you can open it via link
> >
> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
> > >
> > >
> > > And I suggest to introduce some JSON functions that is from MySQL and
> > might be useful for Calcite but not supported, not Just JSON_TYPE:
> > > 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> > > 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> > > I think the PR[1] can be a good start.
> > >
> > >
> > > Does anyone have thought about this? Looking forward to your reply.
> > >
> > >
> > > [1] https://issues.apache.org/jira/browse/CALCITE-2791
> > > [2]
> >
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> > > [3]
> > https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> > > [4]
> > https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
> >
> >
回复： Re: Integrating MySQL's JSON functions
Posted by ForwardXu <x1...@qq.com>.
hi statmatis:
It's also possible that we could do better in both ways, as Hongze Zhang said. One of the initial reasons I implemented json_type was to use it in flink as well. However, we know that flink is not open to support a dialect like mysql.
best
qianjin
------------------ 原始邮件 ------------------
发件人: "Hongze Zhang"<no...@126.com>;
发送时间: 2019年2月1日(星期五) 中午1:09
收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
主题: Re: Re: Integrating MySQL's JSON functions
Hi Stamatis,
Thanks for mentioning MySQLOperatorTable!
I just read some code about the usage of OracleOperatorTable in Calcite, but I am now not strongly inclined to add MySQL's JSON functions to MySQLOperatorTable.
MySQL's JSON functions are rarely conflict with what are from standard, and they extends the JSON ability of MySQL in many sides. I think it might be a good try to treat functions from both MySQL's and standard's as a whole JSON operator suite.
Users may want to directly use any function they know, I think it is better to not to confuse them with the truth that what's in standard and what's not.
For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key : value), I think we can just support both.
What do you think?
Hongze
From: Stamatis Zampetakis
Date: 2019-02-01 06:32
To: dev
Subject: Re: Integrating MySQL's JSON functions
Nice document Hongze!
Since the functions are only present in MySQL why not create a
MySQLOperatorTable and put them there? I went over the discussion in
CALCITE-2791 but I did not understand why it is preferable to put them in
the SqlStdOperatorTable.
Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
έγραψε:
> +1
>
> In the reference doc, let’s make it clear that these are in MySQL but not
> in the standard. (Unlike some of their other extensions to standard SQL,
> MySQL seem to have done a good job - they are well-designed and
> well-documented.)
>
> > On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
> >
> > Hi all,
> >
> >
> > There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
> function JSON_TYPE[2], and I want to know what do you think about it.
> >
> >
> > JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
> the use of this function is to get the type of a JSON doc. The similar
> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
> however sometimes JSON_TYPE can do more than it.
> >
> > As for other non-standard JSON functions, I made a simple summary table
> about JSON support (mainly as functions) details of some of most important
> SQL implementations. you can open it via link
> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
> >
> >
> > And I suggest to introduce some JSON functions that is from MySQL and
> might be useful for Calcite but not supported, not Just JSON_TYPE:
> > 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> > 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> > I think the PR[1] can be a good start.
> >
> >
> > Does anyone have thought about this? Looking forward to your reply.
> >
> >
> > [1] https://issues.apache.org/jira/browse/CALCITE-2791
> > [2]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> > [3]
> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> > [4]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>
>
Re: Re: Integrating MySQL's JSON functions
Posted by Hongze Zhang <no...@126.com>.
Hi Stamatis,
Thanks for mentioning MySQLOperatorTable!
I just read some code about the usage of OracleOperatorTable in Calcite, but I am now not strongly inclined to add MySQL's JSON functions to MySQLOperatorTable.
MySQL's JSON functions are rarely conflict with what are from standard, and they extends the JSON ability of MySQL in many sides. I think it might be a good try to treat functions from both MySQL's and standard's as a whole JSON operator suite.
Users may want to directly use any function they know, I think it is better to not to confuse them with the truth that what's in standard and what's not.
For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key : value), I think we can just support both.
What do you think?
Hongze
From: Stamatis Zampetakis
Date: 2019-02-01 06:32
To: dev
Subject: Re: Integrating MySQL's JSON functions
Nice document Hongze!
Since the functions are only present in MySQL why not create a
MySQLOperatorTable and put them there? I went over the discussion in
CALCITE-2791 but I did not understand why it is preferable to put them in
the SqlStdOperatorTable.
Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
έγραψε:
> +1
>
> In the reference doc, let’s make it clear that these are in MySQL but not
> in the standard. (Unlike some of their other extensions to standard SQL,
> MySQL seem to have done a good job - they are well-designed and
> well-documented.)
>
> > On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
> >
> > Hi all,
> >
> >
> > There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
> function JSON_TYPE[2], and I want to know what do you think about it.
> >
> >
> > JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
> the use of this function is to get the type of a JSON doc. The similar
> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
> however sometimes JSON_TYPE can do more than it.
> >
> > As for other non-standard JSON functions, I made a simple summary table
> about JSON support (mainly as functions) details of some of most important
> SQL implementations. you can open it via link
> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
> >
> >
> > And I suggest to introduce some JSON functions that is from MySQL and
> might be useful for Calcite but not supported, not Just JSON_TYPE:
> > 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> > 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> > I think the PR[1] can be a good start.
> >
> >
> > Does anyone have thought about this? Looking forward to your reply.
> >
> >
> > [1] https://issues.apache.org/jira/browse/CALCITE-2791
> > [2]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> > [3]
> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> > [4]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>
>
Re: Integrating MySQL's JSON functions
Posted by Stamatis Zampetakis <za...@gmail.com>.
Nice document Hongze!
Since the functions are only present in MySQL why not create a
MySQLOperatorTable and put them there? I went over the discussion in
CALCITE-2791 but I did not understand why it is preferable to put them in
the SqlStdOperatorTable.
Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde <jh...@apache.org>
έγραψε:
> +1
>
> In the reference doc, let’s make it clear that these are in MySQL but not
> in the standard. (Unlike some of their other extensions to standard SQL,
> MySQL seem to have done a good job - they are well-designed and
> well-documented.)
>
> > On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
> >
> > Hi all,
> >
> >
> > There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
> function JSON_TYPE[2], and I want to know what do you think about it.
> >
> >
> > JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
> the use of this function is to get the type of a JSON doc. The similar
> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
> however sometimes JSON_TYPE can do more than it.
> >
> > As for other non-standard JSON functions, I made a simple summary table
> about JSON support (mainly as functions) details of some of most important
> SQL implementations. you can open it via link
> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
> >
> >
> > And I suggest to introduce some JSON functions that is from MySQL and
> might be useful for Calcite but not supported, not Just JSON_TYPE:
> > 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> > 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> > I think the PR[1] can be a good start.
> >
> >
> > Does anyone have thought about this? Looking forward to your reply.
> >
> >
> > [1] https://issues.apache.org/jira/browse/CALCITE-2791
> > [2]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> > [3]
> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> > [4]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>
>
Re: Integrating MySQL's JSON functions
Posted by Julian Hyde <jh...@apache.org>.
+1
In the reference doc, let’s make it clear that these are in MySQL but not in the standard. (Unlike some of their other extensions to standard SQL, MySQL seem to have done a good job - they are well-designed and well-documented.)
> On Jan 31, 2019, at 10:20 AM, Hongze Zhang <no...@126.com> wrote:
>
> Hi all,
>
>
> There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL function JSON_TYPE[2], and I want to know what do you think about it.
>
>
> JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL the use of this function is to get the type of a JSON doc. The similar operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)", however sometimes JSON_TYPE can do more than it.
>
> As for other non-standard JSON functions, I made a simple summary table about JSON support (mainly as functions) details of some of most important SQL implementations. you can open it via link https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
>
>
> And I suggest to introduce some JSON functions that is from MySQL and might be useful for Calcite but not supported, not Just JSON_TYPE:
> 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> I think the PR[1] can be a good start.
>
>
> Does anyone have thought about this? Looking forward to your reply.
>
>
> [1] https://issues.apache.org/jira/browse/CALCITE-2791
> [2] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> [3] https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> [4] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
回复：Integrating MySQL's JSON functions
Posted by ForwardXu <x1...@qq.com>.
hi Hongze Zhang：
This makes sense to me, which is why I added the json_type function in the first place. Thank you very much.
best
qianjin
------------------ 原始邮件 ------------------
发件人: "Hongze Zhang"<no...@126.com>;
发送时间: 2019年2月1日(星期五) 凌晨2:20
收件人: "dev@calcite.apache.org"<de...@calcite.apache.org>;
主题: Integrating MySQL's JSON functions
Hi all,
There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL function JSON_TYPE[2], and I want to know what do you think about it.
JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL the use of this function is to get the type of a JSON doc. The similar operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)", however sometimes JSON_TYPE can do more than it.
As for other non-standard JSON functions, I made a simple summary table about JSON support (mainly as functions) details of some of most important SQL implementations. you can open it via link https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
And I suggest to introduce some JSON functions that is from MySQL and might be useful for Calcite but not supported, not Just JSON_TYPE:
1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
I think the PR[1] can be a good start.
Does anyone have thought about this? Looking forward to your reply.
[1] https://issues.apache.org/jira/browse/CALCITE-2791
[2] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
[3] https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
[4] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
Re: Integrating MySQL's JSON functions
Posted by Hongze Zhang <no...@126.com>.
Thanks for your help on this topic, Qianjin! It would be great if you are willing to contribute more. Be free to file JIRA issues / open PRs at the time when you think is right.
Best,
Hongze
Hongze
From: ForwardXu
Date: 2019-02-25 09:43
To: Hongze Zhang
Subject: Integrating MySQL's JSON functions
Hi Honeze Zhang,
Thank you very much for the late review and merge the PR for JSON_TYPE. I think JSON_TYPE has completed the first step of the whole Mysql JSON function. I think we can continue the JSON_DEPTH/JSON_LENGTH functions.
best
qianjin
Integrating MySQL's JSON functions
Posted by ForwardXu <x1...@qq.com>.
Hi Honeze Zhang,
Thank you very much for the late review and merge the PR for JSON_TYPE. I think JSON_TYPE has completed the first step of the whole Mysql JSON function. I think we can continue the JSON_DEPTH/JSON_LENGTH functions.
best
qianjin