Bug #69168 COUNT(*)...GROUP BY sometimes returns wrong results on partitioned InnoDB tables
Submitted: 7 May 2013 19:20 Modified: 18 Sep 2013 6:38
Reporter: Mike Andrews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.11, 5.6.12 OS:Any (FreeBSD, Linux)
Assigned to: CPU Architecture:Any
Tags: innodb partition index group by count, regression
Triage: Needs Triage: D2 (Serious)

[7 May 2013 19:20] Mike Andrews
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)
[7 May 2013 19:59] Sveta Smirnova
Thank you for the report.

Verified as described.
[27 Aug 2013 11:10] Aditya A
This is a duplicate of the bug in the oracle Bug system (Bug#16862316)
[27 Aug 2013 11:54] Joe Grasse
For those that don't have access to the oracle bug system.

Noted in 5.6.14, 5.7.3 changelogs.
 
For partitioned tables, queries could return different results
depending on whether Index Merge was used.
[18 Sep 2013 6:38] Erlend Dahl
As already noted, this bug has been fixed in 5.6.14, 5.7.3.