MySQL COUNT(DISTINCT()) unexpected results

Answer 1

could you possibly intersect the differing results to see what the extra rows are?

Answer 2

In the first case, what happens if you change it to "select distinct(email) from newsletter order by email;" I don't think it should make a difference, but it might.

Answer 3

My guess is that there are some null values in email column. Try

select count(*) from newsletter where email is null;

Answer 4

Yes, as Maglob said you may be getting some NULLs in your email column. Try with something like:

SELECT COUNT(COALESCE(email, 0)) FROM newsletter

That would give you the results you expect. Oh, wait. The definition says that email does not accept NULL values. If that is truly the case, then it is odd.

Answer 5

One possible explanation is that rows were deleted from the table between your two queries. But I assume you've run these multiple times in various orders so I doubt that's it.

What rowcount do you get from this query:

SELECT email FROM newsletter GROUP BY email;