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

JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause

    XMLWordPrintable

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

          Activity

            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.