Description:
On a partitioned InnoDB table, COUNT(DISTINCT col) can return a value much smaller than
the correct one when the optimizer chooses a loose index scan, shown as
"Using index for group-by (scanning)" in the Extra column of EXPLAIN.
With the exact same rows in a non-partitioned table, the correct value is returned.
The magnitude of the error grows with the number of partitions. In our test with 100
distinct values:
- no partitioning -> 100 (correct)
- PARTITION BY LINEAR HASH, 4 partitions -> 25 (wrong)
- PARTITION BY LINEAR HASH, 256 partitions -> 1 (wrong)
A hint is not required to trigger this. With 100 rows the optimizer chooses the loose
index scan on its own, so a query that returns correct results today can silently start
returning wrong results as data volume or statistics change.
The primary key definition is not the cause. A non-partitioned table with the same
composite primary key (id, member_id) returns the correct value. Only the presence of
partitioning changes the result.
Other query shapes over the same table and the same data return correct results:
- COUNT(*)
- SUM() / MAX()
- SELECT ... GROUP BY member_id (counting the rows)
- SELECT COUNT(*) FROM (SELECT DISTINCT member_id ...) AS t
- COUNT(DISTINCT member_id) with IGNORE INDEX on the index that enables the loose index scan
So the loose index scan itself is not broken in general; the problem appears to be limited
to how the per-partition results of COUNT(DISTINCT) are merged.
Server variables do not help. The wrong value is still returned with
optimizer_switch='skip_scan=off' and with optimizer_switch='use_index_extensions=off'.
The error is not deterministic in a simple way. We also observed 0 being returned when all
matching rows resided in a single partition, and we observed correct results for the same
SQL and data after recreating the server instance. The reproduction steps below, however,
were stable across all environments we tried.
Documentation does not describe this as a limitation. The "GROUP BY Optimization" page lists
the conditions for Loose Index Scan and does not mention partitioned tables at all, and it
explicitly states that COUNT(DISTINCT) is supported:
"These support AVG(DISTINCT), SUM(DISTINCT), and COUNT(DISTINCT) ...
All other Loose Index Scan limitations still apply."
The "Restrictions and Limitations on Partitioning" page likewise documents no restriction on
GROUP BY optimization or aggregate functions.
Versions tested - all reproduce:
MySQL 5.7.44, 8.0.31, 8.0.43, 8.4.11, 9.7.2
Percona Server 8.0.46-37
Not reproduced:
MariaDB 11.8.9 returns the correct value, even when the loose index scan is forced with
FORCE INDEX. This suggests the behaviour is fixable at the implementation level.
We searched bugs.mysql.com and could not find an existing report for this. The closest
entries we found are Bug #106754 (performance, not wrong results), Bug #74806 (fixed in
5.6/5.7, unrelated to partitioning) and Bug #37235 (2008, MyISAM).
How to repeat:
-- Tested on MySQL 8.0.31 (also reproduces on 5.7.44, 8.0.43, 8.4.11 and 9.7.2)
DROP TABLE IF EXISTS t_plain, t_pk, t_part4, t_part256;
-- (a) no partitioning, single-column primary key
CREATE TABLE t_plain (
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
goods_id BIGINT NOT NULL,
detail_id BIGINT,
PRIMARY KEY (id),
UNIQUE KEY uq (member_id, goods_id, detail_id),
INDEX ix_g (goods_id)
);
-- (b) no partitioning, composite primary key
CREATE TABLE t_pk (
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
goods_id BIGINT NOT NULL,
detail_id BIGINT,
PRIMARY KEY (id, member_id),
UNIQUE KEY uq (member_id, goods_id, detail_id),
INDEX ix_g (goods_id)
);
-- (c) 4 partitions
CREATE TABLE t_part4 (
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
goods_id BIGINT NOT NULL,
detail_id BIGINT,
PRIMARY KEY (id, member_id),
UNIQUE KEY uq (member_id, goods_id, detail_id),
INDEX ix_g (goods_id)
) PARTITION BY LINEAR HASH(member_id) PARTITIONS 4;
-- (d) 256 partitions
CREATE TABLE t_part256 (
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
goods_id BIGINT NOT NULL,
detail_id BIGINT,
PRIMARY KEY (id, member_id),
UNIQUE KEY uq (member_id, goods_id, detail_id),
INDEX ix_g (goods_id)
) PARTITION BY LINEAR HASH(member_id) PARTITIONS 256;
-- 100 distinct member_id values, all with goods_id = 1
INSERT INTO t_plain (id, member_id, goods_id, detail_id)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100
)
SELECT n, n, 1, n FROM seq;
INSERT INTO t_pk SELECT * FROM t_plain;
INSERT INTO t_part4 SELECT * FROM t_plain;
INSERT INTO t_part256 SELECT * FROM t_plain;
-- The correct answer is 100 in every case.
SELECT 'no partitioning, PK(id)' AS t, COUNT(DISTINCT member_id) AS result FROM t_plain WHERE goods_id = 1
UNION ALL
SELECT 'no partitioning, PK(id,member_id)', COUNT(DISTINCT member_id) FROM t_pk WHERE goods_id = 1
UNION ALL
SELECT '4 partitions', COUNT(DISTINCT member_id) FROM t_part4 WHERE goods_id = 1
UNION ALL
SELECT '256 partitions', COUNT(DISTINCT member_id) FROM t_part256 WHERE goods_id = 1;
-- Actual result:
-- +-----------------------------------+--------+
-- | t | result |
-- +-----------------------------------+--------+
-- | no partitioning, PK(id) | 100 |
-- | no partitioning, PK(id,member_id) | 100 |
-- | 4 partitions | 25 | <- wrong
-- | 256 partitions | 1 | <- wrong
-- +-----------------------------------+--------+
-- The plan chosen for the failing query:
EXPLAIN SELECT COUNT(DISTINCT member_id) FROM t_part256 WHERE goods_id = 1;
-- key: uq
-- Extra: Using where; Using index for group-by (scanning)
-- The same table and data return the correct value when the loose index scan is avoided:
SELECT 'COUNT(*)' AS q, COUNT(*) AS result FROM t_part256 WHERE goods_id = 1
UNION ALL
SELECT 'GROUP BY', COUNT(*) FROM (SELECT member_id FROM t_part256 WHERE goods_id = 1 GROUP BY member_id) AS x
UNION ALL
SELECT 'DISTINCT in a derived table', COUNT(*) FROM (SELECT DISTINCT member_id FROM t_part256 WHERE goods_id = 1) AS x
UNION ALL
SELECT 'IGNORE INDEX (uq)', COUNT(DISTINCT member_id) FROM t_part256 IGNORE INDEX (uq) WHERE goods_id = 1;
-- All of the above return 100.
Suggested fix:
Either:
(a) do not apply the loose index scan optimization to aggregate DISTINCT functions
(COUNT/AVG/SUM DISTINCT) when the table is partitioned, or
(b) correctly merge the per-partition results of the aggregate DISTINCT so that values
appearing in different partitions are not discarded.
MariaDB returns the correct value for the same query and the same plan, so the behaviour
appears to be fixable rather than an inherent limitation of the optimization.
