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 <[email protected]> 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" <[email protected]> 写道： >> 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 <[email protected]> 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 <[email protected]> έγραψε: >>> >>>> 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"<[email protected]>; >>>> 发送时间: 2019年2月1日(星期五) 中午1:09 >>>> 收件人: "[email protected]"<[email protected]>; >>>> >>>> 主题: 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 <[email protected]> >>>> έγραψε: >>>> >>>>> +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 <[email protected]> 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 >>>>> >>>>>
