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

`DISTINCT` after `GROUP BY` incorrectly ignores a case-sensitive collation

    XMLWordPrintable

Details

    Description

      MariaDB can incorrectly remove rows when a query combines `DISTINCT` with a
      `GROUP BY` that contains both a computed expression and the expression's raw
      source column. In this form, duplicate elimination treats a case-sensitive
      string grouping column as case-insensitive.

      The table column below uses `utf8mb4_0900_as_cs`, so `sample_a` and `sample_A`
      are distinct values. The grouped relation contains four rows (two labels and
      two square-root values). MariaDB's direct `DISTINCT` query returns only two
      rows, retaining one case variant for each square-root value. Moving the same
      grouped query behind a derived table or temporary table returns all four rows.

      This is a MariaDB execution bug. The SQL is valid, and the materialized form
      does not introduce extra groups; it avoids the incorrect direct `DISTINCT`
      deduplication path.

      How to repeat

      ```sql
      DROP DATABASE IF EXISTS mariadb_distinct_group_test;
      CREATE DATABASE mariadb_distinct_group_test
          CHARACTER SET utf8mb4
          COLLATE utf8mb4_0900_as_cs;
      USE mariadb_distinct_group_test;
       
      CREATE TABLE labels (
          c6 VARCHAR(10)
             CHARACTER SET utf8mb4
             COLLATE utf8mb4_0900_as_cs NOT NULL
      );
       
      CREATE TABLE vals (
          c3 DECIMAL(10,2) NOT NULL
      );
       
      INSERT INTO labels (c6) VALUES ('sample_a'), ('sample_A');
      INSERT INTO vals (c3) VALUES (1.00), (4.00);
      ```
      
      

      First execute the grouped query without `DISTINCT`:

       
      ```sql
      SELECT l.c6, SQRT(v.c3) AS s
      FROM labels AS l
      CROSS JOIN vals AS v
      GROUP BY l.c6, SQRT(v.c3), v.c3;
      ```
      
      

      It returns four rows:

       
      ```text
      sample_a  1
      sample_a  2
      sample_A  1
      sample_A  2
      ```
      
      

      Now execute the equivalent direct `DISTINCT` query:

       
      ```sql
      SELECT DISTINCT l.c6, SQRT(v.c3) AS s
      FROM labels AS l
      CROSS JOIN vals AS v
      GROUP BY l.c6, SQRT(v.c3), v.c3;
      ```
      
      

      On MariaDB 12.3.2 this returns only two rows, for example:

       
      ```text
      sample_a  1
      sample_a  2
      ```
      
      

      The derived-table and temporary-table forms preserve all four rows:

       
      ```sql
      SELECT DISTINCT q.c6, q.s
      FROM (
          SELECT l.c6, SQRT(v.c3) AS s
          FROM labels AS l
          CROSS JOIN vals AS v
          GROUP BY l.c6, SQRT(v.c3), v.c3
      ) AS q;
       
      CREATE TEMPORARY TABLE grouped_cut AS
      SELECT l.c6, SQRT(v.c3) AS s, v.c3 AS group_source
      FROM labels AS l
      CROSS JOIN vals AS v
      GROUP BY l.c6, SQRT(v.c3), v.c3;
       
      SELECT DISTINCT c6, s
      FROM grouped_cut;
      ```
      
      

      As an additional control, forcing a binary comparison also returns four rows:

       
      ```sql
      SELECT DISTINCT BINARY l.c6, SQRT(v.c3) AS s
      FROM labels AS l
      CROSS JOIN vals AS v
      GROUP BY l.c6, SQRT(v.c3), v.c3;
      ```
      

      1. Expected result

      All equivalent forms should return four rows. The case-sensitive collation
      requires `sample_a` and `sample_A` to remain distinct, and the raw `v.c3`
      grouping key must not change the projected `DISTINCT` result.

      1. Actual result

      MariaDB 12.3.2 returns only two rows for the direct `DISTINCT` query, merging
      the two case variants. The derived-table and temporary-table forms return four
      rows.

      Attachments

        Activity

          People

            raghunandan.bhat Raghunandan Bhat
            chen7897 cl hl
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 0d
                0d
                Remaining:
                Remaining Estimate - 2d
                2d
                Logged:
                Time Spent - Not Specified
                Not Specified

                Git Integration

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