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

Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5, 10.6, 10.6.4, 10.6.5
    • Fix Version/s: 10.5, 10.6, 10.7, 10.8
    • Component/s: JSON
    • Labels:
      None

      Description

      Run the following query on a fresh installation of MariaDB 10.6.x:

      openxs@ao756:~/dbs/maria10.6$ bin/mysql --socket=/tmp/mariadb.sock
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 6
      Server version: 10.6.5-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from
      mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user))
      as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';
      +--------------------------------------+
      | j                                    |
      +--------------------------------------+
      | {"attr2": "[{\"attr1\": \"root\"}]"} |
      +--------------------------------------+
      1 row in set (0.007 sec)
      

      It will return a single row for the "root" user, but note that attributes and values in the array have backslash before the ". While with MySQL:

      openxs@ao756:~/dbs/maria10.6$ bin/mysql -uroot --socket=/tmp/maysql8.sock
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MySQL connection id is 11
      Server version: 8.0.26 Source distribution
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MySQL [(none)]> select json_object('attr2', ifnull(u.o, json_array())) j from mysql.user u1 left join (select user, json_arrayagg(json_object('attr1', user)) as o from mysql.user group by user) u on u1.user = u.user where u1.user = 'root';
      +--------------------------------+
      | j                              |
      +--------------------------------+
      | {"attr2": [{"attr1": "root"}]} |
      +--------------------------------+
      1 row in set (0.002 sec)
      

      we have expected quoting for the values in the array.

      Why is this difference, that I consider a bug in MariaDB?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.