revision-id: ab88849645dd58a8261a3612262254ff145e48c6 (mariadb-10.5.2-484-gab88849645d) parent(s): ead98fe5d34912578445d42e620c8ed95df4c433 author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-29 14:31:07 +0530 message: MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY Quick grouping is not supported for JSON_OBJECTAGG. The same for GROUP_CONCAT too so make sure that Item::quick_group is set to FALSE. We need to make sure that in the case of JSON_OBJECTAGG we don't create an index over grouping fields and update the result after each iteration. Instead we should first sort the result in accordance to the GROUP BY fields and then perform the grouping and write the result to the temp table. --- mysql-test/main/func_json.result | 14 ++++++++++++++ mysql-test/main/func_json.test | 12 ++++++++++++ sql/item_jsonfunc.cc | 1 + sql/item_jsonfunc.h | 1 + 4 files changed, 28 insertions(+) diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index b3d7a1d2ed1..aa9c9cdc8b5 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1328,5 +1328,19 @@ JSON_ARRAYAGG(col1) [{"color":"red", "size":1},{"color":"blue", "size":2}] drop table t1; # +# MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY +# +CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255)); +INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3'); +EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 SIMPLE B ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +e JSON_OBJECTAGG(B.a, B.v) +0 {"a1":"1", "a1":"1", "a1":"1", "a2":"2", "a2":"2", "a2":"2"} +1 {"b1":"3", "b1":"3", "b1":"3"} +DROP TABLE t1; +# # End of 10.5 tests # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 531f6cd758d..75a118a808f 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -826,6 +826,18 @@ insert into t1 values('{"color":"blue", "size":2}' ); select JSON_ARRAYAGG(col1) from t1; drop table t1; +--echo # +--echo # MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY +--echo # + +CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255)); +INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3'); + +EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; + +DROP TABLE t1; + --echo # --echo # End of 10.5 tests --echo # diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index b9a84775311..fe3fae5840c 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -3725,6 +3725,7 @@ Item_func_json_objectagg:: Item_func_json_objectagg(THD *thd, Item_func_json_objectagg *item) :Item_sum(thd, item) { + quick_group= FALSE; result.set_charset(collation.collation); result.append("{"); } diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h index 0408b3ad72f..69625831045 100644 --- a/sql/item_jsonfunc.h +++ b/sql/item_jsonfunc.h @@ -579,6 +579,7 @@ class Item_func_json_objectagg : public Item_sum Item_func_json_objectagg(THD *thd, Item *key, Item *value) : Item_sum(thd, key, value) { + quick_group= FALSE; result.append("{"); }