Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35377

Incorrect Grouping in JSON_OBJECTAGG Aggregation for Case-Sensitive Column in MariaDB

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 11.4
    • N/A
    • N/A
    • 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.

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          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)
          

          alice Alice Sherepa added a comment - 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)

          People

            Unassigned Unassigned
            dwenking chaos
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.