zhztheplayer commented on a change in pull request #1107: CALCITE-2870: Support
for JSON query quotes behavior
URL: https://github.com/apache/calcite/pull/1107#discussion_r275116466
##########
File path:
core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
##########
@@ -4465,6 +4465,14 @@ private void checkNullOperand(SqlTester tester, String
op) {
+ "with conditional wrapper)",
"[100]", "VARCHAR(2000)");
+ //omit quotes test
+ tester.checkString("json_query('{\"foo\":\"100\"}', 'strict $.foo' "
Review comment:
Sorry for the late reply.
Here is the SQL:2016 standard's JSON part:
https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
In the std, JSON_QUERY does include a optional output clause:
> \<JSON output clause\> ::= RETURNING <data type> [ FORMAT \<JSON
representation\> ]
So yes I think the functionality should be implemented as well, maybe by a
separated PR/JIRA.
Besides, I recall that currently JSON_QUERY is not designed for returning
scalar values (see examples in page 34 in std). It only returns JSON array or
object, otherwise an error is thrown. But the highlighted case your wrote shows
that a `JSON_QUERY` call produces an scalar string in the PR.
So in the PR the behavior on querying a scalar string is:
1. If `OMIT QUOTES` is specified, return a string value;
2. If `KEEP QUOTES` is specified, report an error;
3. If quote behavior is not specified, report an error.
Am I summarize them correctly? If yes, I think case 1 and case 2/3 are not
consistent. Maybe we should make `JSON_QUERY` be able to return scalar values
as well as `JSON_VALUE` by default (without abiding by the std). Have you
checked how does the same part work in other SQL impelemtations such as DB2[1]?
Does it ban scalar value for `JSON_QUERY` too?
[1]
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0070413.html
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
