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

UNION looses JSON property

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
    • 10.5, 10.6
    • Data types, JSON
    • 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

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.