Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.3.2
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;
|
```
|
- 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.
- 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.