[MDEV-32675] JSON_ARRAYAGG includes wrong TEXT row Created: 2023-11-04  Updated: 2023-11-07

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.5.21
Fix Version/s: 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: A D Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: JSON_ARRAYAGG
Environment:

mariadb -e 'select version()\G'

                                                      • 1. row ***************************
                                                        version(): 10.5.21-MariaDB-0+deb11u1-log

uname -a
Linux ### 5.10.0-25-amd64 #1 SMP Debian 5.10.191-1 (2023-08-16) x86_64 GNU/Linux


 Description    

CREATE TABLE `yyy_mariadb_bug` (
 
        `group_id` INT(11) NULL DEFAULT NULL,
 
        `row_id` INT(11) NULL DEFAULT NULL,
 
        `varchar_val` VARCHAR(25) NULL DEFAULT NULL,
 
        `text_val` TEXT NULL DEFAULT NULL
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (1, 1, 'varchar_group_1_row_1', 'text_group_1_row_1');
 
INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (1, 2, 'varchar_group_1_row_2', 'text_group_1_row_2');
 
INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (2, 1, 'varchar_group_2_row_1', 'text_group_2_row_1');
 
INSERT INTO `yyy_mariadb_bug` (`group_id`, `row_id`, `varchar_val`, `text_val`) VALUES (2, 2, 'varchar_group_2_row_2', 'text_group_2_row_2');
 
 
 
SELECT * FROM yyy_mariadb_bug;
 
 
 
SELECT group_id, JSON_OBJECT(
 
'arr',(SELECT JSON_ARRAYAGG(JSON_OBJECT(
 
'v',varchar_val,
 
't',text_val)))) as json_arr FROM yyy_mariadb_bug GROUP BY group_id;

Actual result:

group_id        row_id  varchar_val     text_val
 
1       1       varchar_group_1_row_1   text_group_1_row_1
 
1       2       varchar_group_1_row_2   text_group_1_row_2
 
2       1       varchar_group_2_row_1   text_group_2_row_1
 
2       2       varchar_group_2_row_2   text_group_2_row_2
 
group_id        json_arr
 
1       {"arr": [{"v": "varchar_group_1_row_1", "t": "text_group_1_row_1"},{"v": "varchar_group_1_row_2", "t": "text_group_1_row_1"}]}
 
2       {"arr": [{"v": "varchar_group_2_row_1", "t": "text_group_2_row_1"},{"v": "varchar_group_2_row_2", "t": "text_group_2_row_1"}]}

Expected result:

group_id        json_arr
 
1       {"arr": [{"v": "varchar_group_1_row_1", "t": "text_group_1_row_1"},{"v": "varchar_group_1_row_2", "t": "text_group_1_row_2"}]}
 
2       {"arr": [{"v": "varchar_group_2_row_1", "t": "text_group_2_row_1"},{"v": "varchar_group_2_row_2", "t": "text_group_2_row_2"}]}

i.e. The second element of the array should come from row 2 ...

 {"v": "varchar_group_1_row_2", "t": "text_group_1_row_2"}
 
 {"v": "varchar_group_2_row_2", "t": "text_group_2_row_2"}

... but instead ...

 {"v": "varchar_group_1_row_2", "t": "text_group_1_row_1"}
 
 {"v": "varchar_group_2_row_2", "t": "text_group_2_row_1"}

... is included - the correct varchar value from row 2 is included, but the wrong text value from row 1 is included, it should contain text_group_(1|2)_row_2 from the row 2s.

 Comments   
Comment by Alice Sherepa [ 2023-11-06 ]

The query without subselect returns expected results:

MariaDB [test]>  SELECT group_id, JSON_OBJECT( 'arr',(  JSON_ARRAYAGG(JSON_OBJECT( 'v',varchar_val, 't',text_val)))) as json_arr FROM yyy_mariadb_bug GROUP BY group_id;
 
+----------+--------------------------------------------------------------------------------------------------------------------------------+
 
| group_id | json_arr                                                                                                                       |
 
+----------+--------------------------------------------------------------------------------------------------------------------------------+
 
|        1 | {"arr": [{"v": "varchar_group_1_row_1", "t": "text_group_1_row_1"},{"v": "varchar_group_1_row_2", "t": "text_group_1_row_2"}]} |
 
|        2 | {"arr": [{"v": "varchar_group_2_row_1", "t": "text_group_2_row_1"},{"v": "varchar_group_2_row_2", "t": "text_group_2_row_2"}]} |
 
+----------+--------------------------------------------------------------------------------------------------------------------------------+
 
2 rows in set (0,001 sec)

with subselect:

MariaDB [test]> explain extended SELECT group_id, JSON_OBJECT( 'arr',(select  JSON_ARRAYAGG(JSON_OBJECT( 'v',varchar_val, 't',text_val)))) as json_arr FROM yyy_mariadb_bug GROUP BY group_id;
 
+------+--------------------+-----------------+------+---------------+------+---------+------+------+----------+----------------+
 
| id   | select_type        | table           | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 
+------+--------------------+-----------------+------+---------------+------+---------+------+------+----------+----------------+
 
|    1 | PRIMARY            | yyy_mariadb_bug | ALL  | NULL          | NULL | NULL    | NULL | 4    |   100.00 | Using filesort |
 
|    2 | DEPENDENT SUBQUERY | NULL            | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
 
+------+--------------------+-----------------+------+---------------+------+---------+------+------+----------+----------------+
 
2 rows in set, 4 warnings (0,001 sec)
 
 
 
Note (Code 1276): Field or reference 'test.yyy_mariadb_bug.varchar_val' of SELECT #2 was resolved in SELECT #1
 
Note (Code 1276): Field or reference 'test.yyy_mariadb_bug.text_val' of SELECT #2 was resolved in SELECT #1
 
Note (Code 1981): Aggregate function 'json_arrayagg()' of SELECT #2 belongs to SELECT #1
 
Note (Code 1003): /* select#1 */ select `test`.`yyy_mariadb_bug`.`group_id` AS `group_id`,json_object('arr',<expr_cache><`test`.`yyy_mariadb_bug`.`varchar_val`,`test`.`yyy_mariadb_bug`.`text_val`,json_arrayagg(json_object('v',`test`.`yyy_mariadb_bug`.`varchar_val`,'t',`test`.`yyy_mariadb_bug`.`text_val`)),json_arrayagg(json_object('v',`test`.`yyy_mariadb_bug`.`varchar_val`,'t',`test`.`yyy_mariadb_bug`.`text_val`))>((/* select#2 */ select json_arrayagg(json_object('v',`test`.`yyy_mariadb_bug`.`varchar_val`,'t',`test`.`yyy_mariadb_bug`.`text_val`))))) AS `json_arr` from `test`.`yyy_mariadb_bug` group by `test`.`yyy_mariadb_bug`.`group_id`
 
 
 
MariaDB [test]> set sql_mode='only_full_group_by';
 
Query OK, 0 rows affected (0,000 sec)
 
 
 
MariaDB [test]>  SELECT group_id, JSON_OBJECT( 'arr',(select  JSON_ARRAYAGG(JSON_OBJECT( 'v',varchar_val, 't',text_val)))) as json_arr FROM yyy_mariadb_bug GROUP BY group_id;
 
ERROR 1055 (42000): 'test.yyy_mariadb_bug.varchar_val' isn't in GROUP BY

Generated at Thu Feb 08 10:33:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.