[
https://issues.apache.org/jira/browse/FLINK-34111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816622#comment-17816622
]
Sergey Nuyanzin edited comment on FLINK-34111 at 2/12/24 2:38 PM:
------------------------------------------------------------------
A couple of findings
1. MySQL seems not following JSON spec [1], [2] regarding escaping
{quote}
escape
'"'
'\'
'/'
'b'
'f'
'n'
'r'
't'
'u' hex hex hex hex
{quote}
Probably it's better to follow json spec rules
2. I would expect the {{INPUT_STRING}} as output for the query like
{code:sql}
SELECT json_unquote(json_quote(<INPUT_STRING>));
{code}
by the way MySQL seems following this rule (it is not stated explicitly in docs
however tests show that it does)
[1] https://www.json.org/json-en.html
[2] https://www.crockford.com/mckeeman.html
was (Author: sergey nuyanzin):
A couple of findings
1. MySQL seems not following JSON spec [1] regarding escaping
{quote}
escape
'"'
'\'
'/'
'b'
'f'
'n'
'r'
't'
'u' hex hex hex hex
{quote}
Probably it's better to follow json spec rules
2. I would expect the {{INPUT_STRING}} as output for the query like
{code:sql}
SELECT json_unquote(json_quote(<INPUT_STRING>));
{code}
by the way MySQL seems following this rule (it is not stated explicitly in docs
however tests show that it does)
[1] https://www.crockford.com/mckeeman.html
> Add JSON_QUOTE and JSON_UNQUOTE function
> ----------------------------------------
>
> Key: FLINK-34111
> URL: https://issues.apache.org/jira/browse/FLINK-34111
> Project: Flink
> Issue Type: Sub-task
> Components: Table SQL / API
> Reporter: Martijn Visser
> Priority: Major
> Labels: pull-request-available
>
> Escapes or unescapes a JSON string removing traces of offending characters
> that could prevent parsing.
> Proposal:
> - JSON_QUOTE: Quotes a string by wrapping it with double quote characters and
> escaping interior quote and other characters, then returning the result as a
> utf8mb4 string. Returns NULL if the argument is NULL.
> - JSON_UNQUOTE: Unquotes value and returns the result as a string. Returns
> NULL if the argument is NULL. An error occurs if the value starts and ends
> with double quotes but is not a valid JSON string literal.
> The following characters are reserved in JSON and must be properly escaped to
> be used in strings:
> Backspace is replaced with \b
> Form feed is replaced with \f
> Newline is replaced with \n
> Carriage return is replaced with \r
> Tab is replaced with \t
> Double quote is replaced with \"
> Backslash is replaced with \\
> This function exists in MySQL:
> -
> https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-quote
> -
> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-unquote
> It's still open in Calcite CALCITE-3130
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
