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.