Why is MySQL is giving an incorrect count for a simple query?

Question

I have the following query:

SELECT COUNT(resource_id) AS num
FROM resources_authors
WHERE author_id=1657

This returns the result '75', which is correct.

If I change the query to only search for distinct ids:

SELECT COUNT(DISTINCT resource_id) AS num
FROM resources_authors
WHERE author_id=1657

I get a lower result of '74', which is incorrect.

However, the table as a unique index on resource_id, and I have checked and double-checked that there is no duplicate data. For example, I've run:

SELECT resource_id, COUNT(resource_id) AS c
FROM resources_authors
WHERE author_id =1657
GROUP BY resource_id
ORDER BY c DESC

Even more oddly, if I add a LIMIT to the incorrect query, it then returns the correct result! So:

SELECT COUNT(DISTINCT resource_id)
FROM resources_authors
WHERE author_id=1657
LIMIT 0 , 100

correctly returns 75.

Have I hit a bug, or am I misunderstanding something? This is an INNODB table, MySQL 5.5.24 on Windows.

Answer 1

It is a known MySQL bug. The fix is noted in 5.5.35, 5.6.15, and 5.7.3 changelogs.