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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5
    • Fix Version/s: 10.5.4
    • Component/s: Optimizer
    • Labels:
      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

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: