Wrong results: SELECT DISTINCT / GROUP BY returns duplicate rows on a RANGE-partitioned table when served by a covering index range scan

-------------------------------------------------------------------------------
Description:
-------------------------------------------------------------------------------
On a RANGE-partitioned table, SELECT DISTINCT (and GROUP BY) can return
DUPLICATE rows when the optimizer serves the query from a covering index range
scan. Duplicate elimination is performed per partition and is NOT merged across
partitions, so a value that exists in more than one partition is emitted once
per partition instead of once overall.

The same query with the index disabled (IGNORE INDEX -> table scan), and the
same data in a non-partitioned table, both return the correct de-duplicated
result. So the wrong result comes from the index-based DISTINCT/GROUP BY path on
a partitioned table, not from the data.

The bug requires ALL of:
(1) the table is RANGE-partitioned;
(2) there is a composite secondary index whose first column is the DISTINCT/
GROUP BY column (here KEY i_dc1 (d, c1));
(3) the query has BOTH a range predicate on the partitioning column that
spans more than one partition (c1 <= 3) AND a predicate on the indexed
DISTINCT column (d IN (...)), so the optimizer chooses a covering index
range scan ("Using where; Using index").
Dropping any of these (only the d predicate, only the c1 range, IGNORE INDEX,
or no partitioning) yields the correct result. Both DISTINCT and GROUP BY are
affected.

-------------------------------------------------------------------------------
How to repeat:
-------------------------------------------------------------------------------
DROP DATABASE IF EXISTS pbug;
CREATE DATABASE pbug;
USE pbug;

CREATE TABLE pr (c1 INT, c4 BIGINT, d DATETIME, KEY i_dc1 (d, c1)) ENGINE=InnoDB
PARTITION BY RANGE (c1) (
PARTITION p0 VALUES LESS THAN (0),
PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (10),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

CREATE TABLE plain (c1 INT, c4 BIGINT, d DATETIME, KEY i_dc1 (d, c1)) ENGINE=InnoDB;

– each datetime value lives in TWO different partitions
– (c1 = -1 / -2 -> p0, c1 = 2 / 3 -> p1)
INSERT INTO pr (c1,c4,d) VALUES
(-1,10,'2020-07-01 00:00:01'), (2,10,'2020-07-01 00:00:01'),
(-2,10,'2023-08-31 16:00:00'), (3,10,'2023-08-31 16:00:00');
INSERT INTO plain (c1,c4,d) SELECT c1,c4,d FROM pr;

– BUG: returns 4 rows (each of the 2 values twice)
SELECT DISTINCT d FROM pr
WHERE c1 <= 3 AND d IN ('2020-07-01 00:00:01','2023-08-31 16:00:00');

– GROUP BY is affected the same way (returns 4 groups)
SELECT d FROM pr
WHERE c1 <= 3 AND d IN ('2020-07-01 00:00:01','2023-08-31 16:00:00')
GROUP BY d;

– Correct (2 rows) with the index disabled:
SELECT DISTINCT d FROM pr IGNORE INDEX (i_dc1)
WHERE c1 <= 3 AND d IN ('2020-07-01 00:00:01','2023-08-31 16:00:00');

– Correct (2 rows) on the non-partitioned twin:
SELECT DISTINCT d FROM plain
WHERE c1 <= 3 AND d IN ('2020-07-01 00:00:01','2023-08-31 16:00:00');

EXPLAIN SELECT DISTINCT d FROM pr
WHERE c1 <= 3 AND d IN ('2020-07-01 00:00:01','2023-08-31 16:00:00');

-------------------------------------------------------------------------------
Actual result (MariaDB 12.3.2):
-------------------------------------------------------------------------------
SELECT DISTINCT d FROM pr ... -> 4 rows (WRONG)
2020-07-01 00:00:01
2023-08-31 16:00:00
2020-07-01 00:00:01
2023-08-31 16:00:00
SELECT d FROM pr ... GROUP BY d -> 4 rows (WRONG)
SELECT DISTINCT d FROM pr IGNORE INDEX (i_dc1) ... -> 2 rows (correct)
SELECT DISTINCT d FROM plain ... -> 2 rows (correct)

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pr range i_dc1 i_dc1 11 NULL 4 Using where; Using index

-------------------------------------------------------------------------------
Expected result:
-------------------------------------------------------------------------------
SELECT DISTINCT d FROM pr ... -> 2 rows
2020-07-01 00:00:01
2023-08-31 16:00:00

-------------------------------------------------------------------------------
Notes / suggested fix:
-------------------------------------------------------------------------------
When DISTINCT / GROUP BY is resolved via an index scan on a partitioned table,
a final cross-partition duplicate-elimination (merge) step is required. The
current index-based DISTINCT/GROUP BY path appears to de-duplicate within each
partition independently and concatenate the per-partition results, so values
that occur in multiple partitions survive more than once.