Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.4
-
Ubuntu 18.04LTS Server, MariaDB 10.5.4
Description
When using without the GROUP BY, the square brackets are there, but when I add GROUP BY to the query the square brackets disappear.
create table owner( |
id int primary key, |
name varchar(50) |
);
|
|
|
create table test_table( |
id int primary key, |
type varchar(50), |
size int, |
color varchar(50), |
owner_id int, |
FOREIGN KEY(owner_id) REFERENCES owner(id) |
);
|
|
|
SELECT name, owner.id, JSON_ARRAYAGG(JSON_OBJECT('id',tt.id,'type',tt.type,'color',tt.color)) as materials from owner LEFT JOIN test_table tt on owner.id = tt.owner_id GROUP BY owner.id; |
Expected result:
| Name | Id | Materials |
|---|---|---|
| David | 1 | [{"id": 2, "type": "metal", "color": "blue"},{"id": 1, "type": "paper", "color": "red"}] |
| Joe | 2 | [{"id": 3, "type": "wood", "color": "green"}] |
| John | 3 | [{"id": 4, "type": "plastic", "color": "yellow"}] |
Actual result:
| Name | Id | Materials |
|---|---|---|
| David | 1 | {"id": 2, "type": "metal", "color": "blue"}
, {"id": 1, "type": "paper", "color": "red"} |
| Joe | 2 | {"id": 3, "type": "wood", "color": "green"} |
| John | 3 | {"id": 4, "type": "plastic", "color": "yellow"} |
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
I tried to make a fix for this but when i run the query I get this output
MariaDB [test]> SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id)) as materials
|
-> from t1 LEFT JOIN t2 on t1.id = t2.owner_id
|
-> GROUP BY t1.id;
|
+----+-------------------------------+
|
| id | materials |
|
+----+-------------------------------+
|
| 1 | ["{\"id\": 1}","{\"id\": 2}"] |
|
| 2 | ["{\"id\": 3}"] |
|
| 3 | ["{\"id\": 4}"] |
|
+----+-------------------------------+
|
3 rows in set (0.003 sec)
|
|
The json value here is printed in text form.
Looks like the fix for MDEV-22837 is not complete.
If I run this query, it is giving me the same result as for you, with brackets. But if I run the original query, it is giving me the result that I got, without the brackets. And also what I noticed is that if I select only the id, it works, but if I select anything else, even if it is alone in the JSON, it loses the brackets.
SELECT owner.id, JSON_ARRAYAGG(JSON_OBJECT('type',tt.type)) as materials from owner LEFT JOIN test_table tt on owner.id = tt.owner_id GROUP BY owner.id; |
| id | materials |
|---|---|
| 1 | {"type": "paper"}
, {"type": "metal"} |
| 2 | {"type": "wood"} |
| 3 | {"type": "plastic"} |
SELECT owner.id, JSON_ARRAYAGG(JSON_OBJECT('id',tt.id)) as materials from owner LEFT JOIN test_table tt on owner.id = tt.owner_id GROUP BY owner.id; |
| id | materials |
|---|---|
| 1 | [{"id": 1},{"id": 2}] |
| 2 | [{"id": 3}] |
| 3 | [{"id": 4}] |
Hi davidszoke I think the problem is reproducible with my case too, though i tested with the MYISAM storage engine. The case where the query does not work is when we use the temporary table to store the join and then perform the grouping in accordance to the GROUP BY clause.
The EXPLAIN shows
MariaDB [test]> EXPLAIN
|
-> SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id)) as materials
|
-> from t1 LEFT JOIN t2 on t1.id = t2.owner_id
|
-> GROUP BY t1.id;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 3 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+
|
2 rows in set (0.004 sec)
|
and the result is like
MariaDB [test]> SELECT t1.id, JSON_ARRAYAGG(JSON_OBJECT('id',t2.id)) as materials from t1 LEFT JOIN t2 on t1.id = t2.owner_id GROUP BY t1.id;
|
+----+---------------------+
|
| id | materials |
|
+----+---------------------+
|
| 1 | {"id": 1},{"id": 2} |
|
| 2 | {"id": 3} |
|
| 3 | {"id": 4} |
|
+----+---------------------+
|
3 rows in set (0.005 sec)
|
Here is a test case without JOIN which loses brackets in a similar way:
CREATE TABLE t1 (b VARCHAR(10)); |
INSERT INTO t1 VALUES ("foo"),("bar"); |
SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1; |
|
Actual result, 10.5 baca52655 |
MariaDB [test]> SELECT SQL_BUFFER_RESULT JSON_ARRAYAGG(b) FROM t1;
|
+------------------+
|
| JSON_ARRAYAGG(b) |
|
+------------------+
|
| foo,bar |
|
+------------------+
|
1 row in set (0.002 sec)
|
Same with sql_buffer_result variable instead of the SELECT option.
Without sql_buffer_result the result is as expected:
MariaDB [test]> SELECT JSON_ARRAYAGG(b) FROM t1; |
+------------------+ |
| JSON_ARRAYAGG(b) |
|
+------------------+ |
| ["foo","bar"] | |
+------------------+ |
1 row in set (0.001 sec) |
Relieved to find this thread. I've been having problems for days with inconsistent results from JSON_ARRAYAGG on two servers, one running 10.5.7 and one running 10.5.8. I assumed I was doing something wrong but it appears this is a known issue since last year.
As others have said above, sometimes the square brackets are missing entirely - this seems to be related to the GROUP BY clause as mentioned above..
And sometimes selecting a varchar column it includes the square brackets but misses out the quotes, so I end up with invalid JSON arrays such as [these, should, be, in, quotes].
(I don't have the MariaDB knowhow to determine what causes this.)
I've put together a hack in my code to massage the output to the correct format but it feels fragile so I thought I'd ask if there is a fix in the pipeline?
Here is a test case with values