Bug #116606 Incorrect Grouping in JSON_OBJECTAGG Aggregation for Case-Sensitive Column
Submitted: 10 Nov 2024 6:44 Modified: 11 Nov 2024 6:39
Reporter: Wenqian Deng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S2 (Serious)
Version:9.0, 8.0.40, 8.4.3, 9.1.0 OS:Any
Assigned to: CPU Architecture:Any

[10 Nov 2024 6:44] Wenqian Deng
Description:
This seems to be a bug when using JSON_OBJECTAGG in conjunction with the GROUP BY clause on a case-sensitive column.

How to repeat:
-- Step 1: Create table t0 and insert data
CREATE TABLE t0 (c0 MEDIUMINT, c1 TINYBLOB, c2 VARCHAR(1));
INSERT INTO t0 (c0, c1, c2) VALUES (-3423794, '2R5WKFbhyCIo2NpcxzXfnfQly9vdmvKNNr7MmwZaABth1MDLMorgN1p72qo7xlZZ', 'k');
INSERT INTO t0 (c0, c1, c2) VALUES (6310371, 'xg85OUpKjmryEj07C39zuiobRciJfZsA3eaxxWSkk2vngQHekNlRvj2', 'K');
INSERT INTO t0 (c0, c1, c2) VALUES (-3400994, 'snpQSLiVByxjwbYBf8fPXWbjyQKoAJtLxJ4WfOM15pAav7G2QZaEnCcf', 'k');
 
-- Step 2: Query with JSON_OBJECTAGG and GROUP BY
mysql> SELECT JSON_OBJECTAGG(c0, c1), c2 FROM t0 GROUP BY c2;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| JSON_OBJECTAGG(c0, c1)                                                                                                                                                                                                                                                                                                                         | c2   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| {"6310371": "base64:type249:eGc4NU9VcEtqbXJ5RWowN0MzOXp1aW9iUmNpSmZac0EzZWF4eFdTa2sydm5nUUhla05sUnZqMg==", "-3400994": "base64:type249:c25wUVNMaVZCeXhqd2JZQmY4ZlBYV2JqeVFLb0FKdEx4SjRXZk9NMTVwQWF2N0cyUVphRW5DY2Y=", "-3423794": "base64:type249:MlI1V0tGYmh5Q0lvMk5wY3h6WGZuZlFseTl2ZG12S05OcjdNbXdaYUFCdGgxTURMTW9yZ04xcDcy\ncW83eGxaWg=="} | k    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
1 row in set (0.01 sec)

Actual Result:

The query returns a single JSON object for both 'k' and 'K' in c2, as if they were treated as the same value, causing data to be incorrectly grouped.

Expected Result:

The query should return two distinct JSON objects, one for each unique value of c2 ('k' and 'K'), reflecting the case-sensitive difference in the grouping column.
[11 Nov 2024 6:39] MySQL Verification Team
Hello Wenqian Deng,

Thank you for the report and test case.
Verified as described.

regards,
Umesh