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

UNION looses JSON property

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
    • Fix Version/s: 10.5, 10.6, 10.7, 10.8
    • Component/s: Data types, JSON
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (j JSON);
      INSERT INTO t1 VALUES (JSON_OBJECT('b','c'));
      SELECT JSON_OBJECT('a', j) FROM (SELECT j FROM t1) d1;
      

      +---------------------+
      | JSON_OBJECT('a', j) |
      +---------------------+
      | {"a": {"b": "c"}}   |
      +---------------------+
      

      Looks good so far.

      SELECT JSON_OBJECT('a', j) FROM (SELECT j FROM t1 UNION ALL SELECT j FROM t1) d1;
      

      +-------------------------+
      | JSON_OBJECT('a', j)     |
      +-------------------------+
      | {"a": "{\"b\": \"c\"}"} |
      | {"a": "{\"b\": \"c\"}"} |
      +-------------------------+
      

      The value was erroneously escaped.

      If I run similar queries in Oracle, the values does not get escaped:

      CREATE TABLE t1 (j VARCHAR(10000) CONSTRAINT ensure_json CHECK (po_document IS JSON));
      INSERT INTO t1 VALUES (JSON_OBJECT('b','c'));
      SELECT JSON_OBJECT('a' VALUE j) FROM (SELECT j FROM t1) d1;
      SELECT JSON_OBJECT('a' VALUE j) FROM (SELECT j FROM t1 UNION ALL SELECT j FROM t1) d1;
      

      JSON_OBJECT('A'VALUEJ)
      {"a" : {"b" : "c"}}
      

      JSON_OBJECT('A'VALUEJ)
      {"a" : "{"b":"c"}"}
      {"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.