[MDEV-22840] JSON_ARRAYAGG gives wrong results with NULL values and ORDER by clause Created: 2020-06-09  Updated: 2020-06-16  Resolved: 2020-06-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5
Fix Version/s: 10.5.4

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-22011 DISTINCT with JSON_ARRAYAGG gives wro... Closed

 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

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-06-09 ]

Patch
https://github.com/MariaDB/server/commit/3fdc9d3380f8386c90ccbbb2f4e28b80eb2671d5

Comment by Sergei Petrunia [ 2020-06-12 ]

Ok to push

Generated at Thu Feb 08 09:17:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.