Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27533

The result of `CONVERT(json USING ...)` is erroneously treated as JSON

    XMLWordPrintable

    Details

      Description

      I run this script:

      -- Note, to reproduce in 10.2 change CAST(..VARCHAR(30)) to CAST(..CHAR(30))
      SET NAMES utf8;
      SELECT json_object('a',CAST(json_object('b','c') AS VARCHAR(30)));
      

      +------------------------------------------------------------+
      | json_object('a',CAST(json_object('b','c') AS VARCHAR(30))) |
      +------------------------------------------------------------+
      | {"a": "{\"b\": \"c\"}"}                                    |
      +------------------------------------------------------------+
      

      The output looks good:

      • The result json_object('b','c') is a JSON value
      • Then it's passed to CAST(). The result of CAST() is a general purpose string, it's not a JSON value any more.
      • The result of CAST() is passed to the outer JSON_OBJECT(), and it correctly gets escaped.

      Now I run this script:

      SET NAMES utf8;
      SELECT json_object('a',CONVERT(json_object('b','c') USING utf8));
      

      +-----------------------------------------------------------+
      | json_object('a',CONVERT(json_object('b','c') USING utf8)) |
      +-----------------------------------------------------------+
      | {"a": {"b": "c"}}                                         |
      +-----------------------------------------------------------+
      

      Looks wrong. It should return the same result with the previous script, because the result of CONVERT() is a general purpose string, so it must be escaped when passed to the outer json_object().

      What other databases do

      If I run equivalent scripts in Oracle 21c, they both escape, which looks correct:

      SELECT json_object('a' VALUE CAST(json_object('b' VALUE 'c') AS VARCHAR(30))) FROM DUAL;
      

      JSON_OBJECT('A'VALUECAST(JSON_OBJECT('B'VALUE'C')ASVARCHAR(30)))
      {"a":"{\"b\":\"c\"}"}
      

      SELECT json_object('a' VALUE CONVERT(json_object('b' VALUE 'c'),'US7ASCII', 'WE8ISO8859P1')) FROM DUAL;
      

      JSON_OBJECT('A'VALUECONVERT(JSON_OBJECT('B'VALUE'C'),'US7ASCII','WE8ISO8859P1'))
      {"a":"{\"b\":\"c\"}"}
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.