Description:
COUNT(*) .. GROUP BY returns wrong results sometimes, when grouping on an indexed field on a partitioned InnoDB table.
As far as I can tell, all three conditions must be met for the problem to appear -- removing the index, or removing the partitioning, or changing to MyISAM, will work around the issue.
Possibly related to MySQL bugs 37235, 35931, 37219, though those are MyISAM specific and this is InnoDB specific.
MariaDB 5.5.30 is also affected.
How to repeat:
-- These first two queries against this test table are correct:
[localhost:test] mysql> select somefield,count(somefield) from testcase2 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 1789797 |
| 1 | 35558 |
| 2 | 4279 |
+-----------+------------------+
3 rows in set (0.89 sec)
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where somefield=0 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 1789797 |
+-----------+------------------+
1 row in set (0.60 sec)
-- And now it gets weird.
-- First is correct, second is wrong, third (despite awkward phrasing) gives the results the second query should have,
-- probably because it doesn't use the index on 'somefield'.
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 822576 |
| 1 | 24124 |
| 2 | 2418 |
+-----------+------------------+
3 rows in set (5.56 sec)
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 435189 |
+-----------+------------------+
1 row in set (1.44 sec)
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and (somefield>=0 and somefield<1) group by somefield; /* CORRECT, if awkward */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 822576 |
+-----------+------------------+
1 row in set (4.80 sec)
-- Removing the partitioning fixes the issue.
[localhost:test] mysql> alter table testcase2 remove partitioning;
Query OK, 1829634 rows affected, 2 warnings (28.50 sec)
Records: 1829634 Duplicates: 0 Warnings: 2
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 822576 |
+-----------+------------------+
1 row in set (2.09 sec)
-- Putting the partitions back re-creates the issue.
[localhost:test] mysql> alter table testcase2 partition by range (rid)
-> (partition p05 values less than (5000000),
-> partition p10 values less than (10000000),
-> partition p15 values less than (15000000),
-> partition p20 values less than (20000000),
-> partition p25 values less than (25000000),
-> partition p30 values less than (30000000),
-> partition p35 values less than (35000000),
-> partition p40 values less than (40000000),
-> partition p45 values less than (45000000),
-> partition p50 values less than (50000000),
-> partition p55 values less than (55000000),
-> partition p60 values less than (60000000),
-> partition p65 values less than (65000000),
-> partition p70 values less than (70000000),
-> partition p75 values less than (75000000),
-> partition p80 values less than (80000000),
-> partition p85 values less than (85000000),
-> partition p90 values less than (90000000),
-> partition p95 values less than (95000000),
-> partition p99 values less than maxvalue);
Query OK, 1829634 rows affected, 40 warnings (30.05 sec)
Records: 1829634 Duplicates: 0 Warnings: 40
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG AGAIN */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 435189 |
+-----------+------------------+
1 row in set (1.41 sec)
-- Changing the partition boundaries changes the wrong counter to a different wrong counter...
[localhost:test] mysql> alter table testcase2 partition by range (rid)
-> (partition p15 values less than (15000000),
-> partition p30 values less than (30000000),
-> partition p45 values less than (45000000),
-> partition p60 values less than (60000000),
-> partition p75 values less than (75000000),
-> partition p90 values less than (90000000),
-> partition p99 values less than maxvalue);
Query OK, 1829634 rows affected, 14 warnings (29.18 sec)
Records: 1829634 Duplicates: 0 Warnings: 14
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* DIFFERENT, BUT STILL WRONG */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 691078 |
+-----------+------------------+
1 row in set (2.05 sec)
-- Change to MyISAM also fixes
[localhost:test] mysql> alter table testcase2 engine=myisam; /* still partitioned, just different storage engine */
Query OK, 1829634 rows affected (16.96 sec)
Records: 1829634 Duplicates: 0 Warnings: 0
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 822576 |
+-----------+------------------+
1 row in set (5.56 sec)
-- Back to InnoDB, problem comes back
[localhost:test] mysql> alter table testcase2 engine=innodb; /* still partitioned, just different storage engine */
Query OK, 1829634 rows affected, 14 warnings (24.53 sec)
Records: 1829634 Duplicates: 0 Warnings: 14
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 691078 |
+-----------+------------------+
1 row in set (1.95 sec)
-- Drop the index on the field we're grouping on, problem fixed
[localhost:test] mysql> alter table testcase2 drop index somefield;
Query OK, 0 rows affected, 14 warnings (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 14
[localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */
+-----------+------------------+
| somefield | count(somefield) |
+-----------+------------------+
| 0 | 822576 |
+-----------+------------------+
1 row in set (4.41 sec)
Description: COUNT(*) .. GROUP BY returns wrong results sometimes, when grouping on an indexed field on a partitioned InnoDB table. As far as I can tell, all three conditions must be met for the problem to appear -- removing the index, or removing the partitioning, or changing to MyISAM, will work around the issue. Possibly related to MySQL bugs 37235, 35931, 37219, though those are MyISAM specific and this is InnoDB specific. MariaDB 5.5.30 is also affected. How to repeat: -- These first two queries against this test table are correct: [localhost:test] mysql> select somefield,count(somefield) from testcase2 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 1789797 | | 1 | 35558 | | 2 | 4279 | +-----------+------------------+ 3 rows in set (0.89 sec) [localhost:test] mysql> select somefield,count(somefield) from testcase2 where somefield=0 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 1789797 | +-----------+------------------+ 1 row in set (0.60 sec) -- And now it gets weird. -- First is correct, second is wrong, third (despite awkward phrasing) gives the results the second query should have, -- probably because it doesn't use the index on 'somefield'. [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 822576 | | 1 | 24124 | | 2 | 2418 | +-----------+------------------+ 3 rows in set (5.56 sec) [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 435189 | +-----------+------------------+ 1 row in set (1.44 sec) [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and (somefield>=0 and somefield<1) group by somefield; /* CORRECT, if awkward */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 822576 | +-----------+------------------+ 1 row in set (4.80 sec) -- Removing the partitioning fixes the issue. [localhost:test] mysql> alter table testcase2 remove partitioning; Query OK, 1829634 rows affected, 2 warnings (28.50 sec) Records: 1829634 Duplicates: 0 Warnings: 2 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 822576 | +-----------+------------------+ 1 row in set (2.09 sec) -- Putting the partitions back re-creates the issue. [localhost:test] mysql> alter table testcase2 partition by range (rid) -> (partition p05 values less than (5000000), -> partition p10 values less than (10000000), -> partition p15 values less than (15000000), -> partition p20 values less than (20000000), -> partition p25 values less than (25000000), -> partition p30 values less than (30000000), -> partition p35 values less than (35000000), -> partition p40 values less than (40000000), -> partition p45 values less than (45000000), -> partition p50 values less than (50000000), -> partition p55 values less than (55000000), -> partition p60 values less than (60000000), -> partition p65 values less than (65000000), -> partition p70 values less than (70000000), -> partition p75 values less than (75000000), -> partition p80 values less than (80000000), -> partition p85 values less than (85000000), -> partition p90 values less than (90000000), -> partition p95 values less than (95000000), -> partition p99 values less than maxvalue); Query OK, 1829634 rows affected, 40 warnings (30.05 sec) Records: 1829634 Duplicates: 0 Warnings: 40 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG AGAIN */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 435189 | +-----------+------------------+ 1 row in set (1.41 sec) -- Changing the partition boundaries changes the wrong counter to a different wrong counter... [localhost:test] mysql> alter table testcase2 partition by range (rid) -> (partition p15 values less than (15000000), -> partition p30 values less than (30000000), -> partition p45 values less than (45000000), -> partition p60 values less than (60000000), -> partition p75 values less than (75000000), -> partition p90 values less than (90000000), -> partition p99 values less than maxvalue); Query OK, 1829634 rows affected, 14 warnings (29.18 sec) Records: 1829634 Duplicates: 0 Warnings: 14 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* DIFFERENT, BUT STILL WRONG */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 691078 | +-----------+------------------+ 1 row in set (2.05 sec) -- Change to MyISAM also fixes [localhost:test] mysql> alter table testcase2 engine=myisam; /* still partitioned, just different storage engine */ Query OK, 1829634 rows affected (16.96 sec) Records: 1829634 Duplicates: 0 Warnings: 0 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 822576 | +-----------+------------------+ 1 row in set (5.56 sec) -- Back to InnoDB, problem comes back [localhost:test] mysql> alter table testcase2 engine=innodb; /* still partitioned, just different storage engine */ Query OK, 1829634 rows affected, 14 warnings (24.53 sec) Records: 1829634 Duplicates: 0 Warnings: 14 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* WRONG */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 691078 | +-----------+------------------+ 1 row in set (1.95 sec) -- Drop the index on the field we're grouping on, problem fixed [localhost:test] mysql> alter table testcase2 drop index somefield; Query OK, 0 rows affected, 14 warnings (0.06 sec) Records: 0 Duplicates: 0 Warnings: 14 [localhost:test] mysql> select somefield,count(somefield) from testcase2 where lid=1 and somefield=0 group by somefield; /* CORRECT */ +-----------+------------------+ | somefield | count(somefield) | +-----------+------------------+ | 0 | 822576 | +-----------+------------------+ 1 row in set (4.41 sec)