Bug #81031 count(*) on innodb sometimes returns 0
Submitted: 11 Apr 2016 13:00 Modified: 24 May 2017 13:58
Reporter: Ashraf Amayreh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.6.28 OS:Linux
Assigned to: CPU Architecture:Any
Tags: index merge, index_merge_intersection, wrong results

[11 Apr 2016 13:00] Ashraf Amayreh
Description:
Doing a count(*) on innodb table SOMETIMES returns 0, remove count, you get results. Notice that the query is fired against mysql client, not through any code layers!

Here's the exact query

select count(*) from books where publisher_id = 46 AND deleted = 0 AND status_id = 3;

Just remove the count (and replace it with *), and you get results!

Explain shows the following

Using intersect(IDX_cms_books_publisher_id,IDX_cms_books_status_id,IDX_cms_books_deleted); Using where; Using index

If I turn off index merge intersection, problem disappears. So it must be related to the intersect merge feature

SET SESSION optimizer_switch="index_merge_intersection=off";
select count(*) from books where publisher_id = 46 AND deleted = 0 AND status_id = 3;

Working fine

How to repeat:
Create table with same indexes? Please note that this doesn't ALWAYS happen. So there's no sure way to repeat. But in general, it's 0 (wrong) 80% of the time.

Suggested fix:
This is a quick fix for now

SET SESSION optimizer_switch="index_merge_intersection=off";
[12 Apr 2016 9:58] Umesh Shastry
Hi Ashraf,

Thank you for the report.
I'm not able to reproduce with the dummy schema/data set. Could you please provide exact test case to reproduce this issue at our end(table schema and  subset of data to trigger the issue preferably mysqldump file ). If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh
[30 Dec 2016 6:14] Olivier N
Hello there 

I use Mysql 5.6.33 on my portal www.reedb.com and this bug still exists.
This problem is very critical!

example: 

select count(*) from <table> where active=1 and obj=10 
result 0

select count(<table.update>) from <table> where active=1 and obj=10 
result 8805

SET SESSION optimizer_switch="index_merge_intersection=off"
select count(*) from <table> where active=1 and obj=10 
result 8805

If you want, i can send you a dump of a table for tests.

thanks to Ashraf Amayreh - you made my day

best regards n.Olivier

www.reedb.com - real estate online
[30 Dec 2016 7:27] Shane Bester
TESTCASE:
---------

drop table if exists t;
create table t (id int,a int,b int,key (a),key (b)
) engine=innodb partition by hash (id) partitions 4;

insert into t values (0,4,1);
insert into t values (6,2,5);
insert into t values (6,7,5);
insert into t values (6,8,2);
insert into t values (1,7,2);
insert into t values (2,4,6);

select sql_no_cache * from t;
set session optimizer_switch="index_merge_intersection=off";
select sql_no_cache count(*) from t where a=4 and b=6;
set session optimizer_switch="index_merge_intersection=on";
select sql_no_cache count(*) from t where a=4 and b=6;
[30 Dec 2016 7:42] Shane Bester
Verified on 5.6.35.  

5.5.52, 5.7.17, 8.0.1 are not affected.
[14 Apr 2017 14:33] Brendan Howard
Also verified this bug exists in MySQL 5.6.34 via AWS RDS and is not consistent, sometimes appropriate number of results are returned, but after 2-3 weeks of uptime DB starts to return zero results for a majority of count(*) queries. 

index_merge_intersection=off

Fixes the issue.
[24 May 2017 13:58] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.37, 5.7.19, 8.0.2 release, and here's the changelog entry:

When using an index merge optimizer switch, a SELECT COUNT(*) operation
sometimes returned 0. Partitioning code incorrectly performed a memcpy
instead of a column copy of columns read by the index, causing the wrong
records to be copied.
[8 Nov 2017 21:21] Steven Pine
This issue still occurs for me, I am using the Percona release, so maybe I should open it with them, but according to them they are using your latest branch.

https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.37-82.2.ht...

When i run the select count with index () it returns the correct amount.

I have also temporarily set index_merge_intersection off which has resolved the error.
[14 Nov 2017 15:46] Øystein Grøvlen
Hi Steven, 

Since bug report is closed, please, open a new bug report with a reproducible test case for us to look at.
[7 Feb 2018 21:25] Dmitry V
Mysql 5.6.39 Innodb table has the same issue on my system.
[21 Mar 16:32] Simon Banaan
The database

Attachment: tmp_projectdays.sql (application/octet-stream, text), 313.56 KiB.