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

JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5
    • 10.5.4
    • Optimizer
    • None

    Description

      Here is an example

      create table t1(a varchar(255));
      insert into t1 values ('red'), ('blue'), (NULL);

      MariaDB [test]> select json_arrayagg(a) from t1;
      +---------------------+
      | json_arrayagg(a)    |
      +---------------------+
      | ["red","blue",null] |
      +---------------------+
      1 row in set (0.002 sec)
      

      So without ORDER BY clause inside JSON_ARRAYAGG we see null in the output

      MariaDB [test]> select json_arrayagg(a order by a desc) from t1;
      +----------------------------------+
      | json_arrayagg(a order by a desc) |
      +----------------------------------+
      | ["red","blue",""]                |
      +----------------------------------+
      1 row in set (0.003 sec)
       
      MariaDB [test]> select json_arrayagg(a order by a asc) from t1;
      +---------------------------------+
      | json_arrayagg(a order by a asc) |
      +---------------------------------+
      | ["","blue","red"]               |
      +---------------------------------+
      1 row in set (0.002 sec)
      

      With ORDER BY clause there is no more null in the result

      Attachments

        Issue Links

          People

            varun Varun Gupta (Inactive)
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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