Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.4
-
None
Description
This seems to be a bug when using JSON_OBJECTAGG in conjunction with the GROUP BY clause on a case-sensitive column in MariaDB.
Reproduce Steps:
-- 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
|
MariaDB [test]> SELECT JSON_OBJECTAGG(c0,c1), c2 FROM t0 GROUP BY c2; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+ |
| JSON_OBJECTAGG(c0,c1) | c2 |
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+ |
| {"-3423794":"2R5WKFbhyCIo2NpcxzXfnfQly9vdmvKNNr7MmwZaABth1MDLMorgN1p72qo7xlZZ", "6310371":"xg85OUpKjmryEj07C39zuiobRciJfZsA3eaxxWSkk2vngQHekNlRvj2", "-3400994":"snpQSLiVByxjwbYBf8fPXWbjyQKoAJtLxJ4WfOM15pAav7G2QZaEnCcf"} | k | |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+ |
1 row in set (0.004 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.
If you want a column to be treated as case-sensitive, declare it with a case sensitive or binary collation, e.g:
MariaDB [test]> CREATE TABLE t0 (c0 MEDIUMINT, c1 TINYBLOB, c2 VARCHAR(1) COLLATE latin1_general_cs);
Query OK, 0 rows affected (0,028 sec)
<ciJfZsA3eaxxWSkk2vngQHekNlRvj2', 'K'), (-3400994, 'snpQSLiVByxjwbYBf8fPXWbjyQKoAJtLxJ4WfOM15pAav7G2QZaEnCcf', 'k');
Query OK, 3 rows affected (0,002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> SELECT JSON_OBJECTAGG(c0,c1), c2 FROM t0 GROUP BY c2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| JSON_OBJECTAGG(c0,c1) | c2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| {"6310371":"xg85OUpKjmryEj07C39zuiobRciJfZsA3eaxxWSkk2vngQHekNlRvj2"} | K |
| {"-3423794":"2R5WKFbhyCIo2NpcxzXfnfQly9vdmvKNNr7MmwZaABth1MDLMorgN1p72qo7xlZZ", "-3400994":"snpQSLiVByxjwbYBf8fPXWbjyQKoAJtLxJ4WfOM15pAav7G2QZaEnCcf"} | k |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
2 rows in set (0,001 sec)
or just:
MariaDB [test]> SELECT JSON_OBJECTAGG(c0,c1), c2 FROM t0 GROUP BY BINARY c2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| JSON_OBJECTAGG(c0,c1) | c2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| {"6310371":"xg85OUpKjmryEj07C39zuiobRciJfZsA3eaxxWSkk2vngQHekNlRvj2"} | K |
| {"-3423794":"2R5WKFbhyCIo2NpcxzXfnfQly9vdmvKNNr7MmwZaABth1MDLMorgN1p72qo7xlZZ", "-3400994":"snpQSLiVByxjwbYBf8fPXWbjyQKoAJtLxJ4WfOM15pAav7G2QZaEnCcf"} | k |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+------+
2 rows in set (0,001 sec)