Description:
When a SELECT DISTINCT query filters on a column using an explicit COLLATE clause that differs from the column's declared collation, and the optimizer chooses an index skip scan for deduplication (Covering index skip scan for deduplication in EXPLAIN) to satisfy DISTINCT via a secondary index, rows that satisfy the WHERE condition are incorrectly excluded from the result.
The column a has collation latin1_general_ci (case-insensitive). The WHERE clause compares it using COLLATE latin1_bin (case-sensitive, byte-value comparison). A plain SELECT * with the same predicate correctly returns 4 matching rows (a, b, C, c). Deduplicating these 4 values under the column's own case-insensitive collation should yield 3 distinct rows (a, b, and one of C/c) — which is exactly what UNION-ing the query with itself correctly produces. However, SELECT DISTINCT on the same query returns only 1 row (C), silently dropping a and b entirely, even though they satisfy the WHERE condition and are not duplicates of C under any collation.
EXPLAIN shows the Filter node is applied on top of (i.e. after) the deduplicating skip scan:
-> Filter: (t1_ctype_collate.a > <cache>((_latin1'B' collate latin1_bin))) (cost=1.25 rows=4)
-> Covering index skip scan for deduplication on t1_ctype_collate using i (cost=1.25 rows=4)
This ordering indicates deduplication happens first: the skip scan groups index entries by the column's native (case-insensitive) collation and returns a single representative row per group. Only afterward is the WHERE predicate — evaluated under the differing latin1_bin collation — applied to each representative row. Since group members can disagree on the outcome of the case-sensitive predicate (e.g. 'B' fails > 'B' while 'b' passes), whichever member the skip scan happens to pick as the group's representative determines whether the entire group is kept or dropped, causing groups whose surviving representative fails the predicate to be lost entirely, even when another member of that same group would have passed.
How to repeat:
CREATE TABLE t1_ctype_collate (a VARCHAR(1) CHARACTER SET latin1 COLLATE latin1_general_ci);
INSERT INTO t1_ctype_collate VALUES ('A'), ('a'), ('B'), ('b'), ('C'), ('c');
CREATE INDEX i ON t1_ctype_collate(a);
-- Correct: 4 rows (a, b, C, c)
SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin;
-- Correct: 3 rows (a, b, C) -- self-UNION deduplicates correctly
SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin
UNION
SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin;
-- Incorrect: returns only 1 row (C), should return 3 (a, b, C)
SELECT DISTINCT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin;
EXPLAIN SELECT DISTINCT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin;
Expected result:
SELECT DISTINCT should return the same 3 rows (a, b, C) as the self-UNION query, since both should deduplicate under the column's declared collation while independently honoring the WHERE predicate's explicit collation for filtering.
Suggested fix:
When the index skip scan for deduplication is chosen for DISTINCT, and the WHERE clause applies a collation to the indexed column that differs from the index's native collation, either apply the WHERE predicate before/during grouping so each group member is checked individually, or disable this optimization for such queries, rather than testing only a single representative row per group and applying its result to the whole group.
Description: When a SELECT DISTINCT query filters on a column using an explicit COLLATE clause that differs from the column's declared collation, and the optimizer chooses an index skip scan for deduplication (Covering index skip scan for deduplication in EXPLAIN) to satisfy DISTINCT via a secondary index, rows that satisfy the WHERE condition are incorrectly excluded from the result. The column a has collation latin1_general_ci (case-insensitive). The WHERE clause compares it using COLLATE latin1_bin (case-sensitive, byte-value comparison). A plain SELECT * with the same predicate correctly returns 4 matching rows (a, b, C, c). Deduplicating these 4 values under the column's own case-insensitive collation should yield 3 distinct rows (a, b, and one of C/c) — which is exactly what UNION-ing the query with itself correctly produces. However, SELECT DISTINCT on the same query returns only 1 row (C), silently dropping a and b entirely, even though they satisfy the WHERE condition and are not duplicates of C under any collation. EXPLAIN shows the Filter node is applied on top of (i.e. after) the deduplicating skip scan: -> Filter: (t1_ctype_collate.a > <cache>((_latin1'B' collate latin1_bin))) (cost=1.25 rows=4) -> Covering index skip scan for deduplication on t1_ctype_collate using i (cost=1.25 rows=4) This ordering indicates deduplication happens first: the skip scan groups index entries by the column's native (case-insensitive) collation and returns a single representative row per group. Only afterward is the WHERE predicate — evaluated under the differing latin1_bin collation — applied to each representative row. Since group members can disagree on the outcome of the case-sensitive predicate (e.g. 'B' fails > 'B' while 'b' passes), whichever member the skip scan happens to pick as the group's representative determines whether the entire group is kept or dropped, causing groups whose surviving representative fails the predicate to be lost entirely, even when another member of that same group would have passed. How to repeat: CREATE TABLE t1_ctype_collate (a VARCHAR(1) CHARACTER SET latin1 COLLATE latin1_general_ci); INSERT INTO t1_ctype_collate VALUES ('A'), ('a'), ('B'), ('b'), ('C'), ('c'); CREATE INDEX i ON t1_ctype_collate(a); -- Correct: 4 rows (a, b, C, c) SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin; -- Correct: 3 rows (a, b, C) -- self-UNION deduplicates correctly SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin UNION SELECT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin; -- Incorrect: returns only 1 row (C), should return 3 (a, b, C) SELECT DISTINCT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin; EXPLAIN SELECT DISTINCT * FROM t1_ctype_collate WHERE a > _latin1 'B' COLLATE latin1_bin; Expected result: SELECT DISTINCT should return the same 3 rows (a, b, C) as the self-UNION query, since both should deduplicate under the column's declared collation while independently honoring the WHERE predicate's explicit collation for filtering. Suggested fix: When the index skip scan for deduplication is chosen for DISTINCT, and the WHERE clause applies a collation to the indexed column that differs from the index's native collation, either apply the WHERE predicate before/during grouping so each group member is checked individually, or disable this optimization for such queries, rather than testing only a single representative row per group and applying its result to the whole group.