Bug #21027 COUNT(DISTINCT a, b) inconsistent with COUNT(*) FROM (SELECT DISTINCT a, b)
Submitted: 13 Jul 2006 7:47 Modified: 13 Jul 2006 12:13
Reporter: Mattias Jiderhamn Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[13 Jul 2006 7:47] Mattias Jiderhamn
Description:
Not sure if this is a bug, but it seems rather strange to me so I think somebody should check if this is the way it's supposed to behave.

How to repeat:
CREATE TABLE distinct_test (
 a INTEGER,
 b INTEGER
);

INSERT INTO distinct_test VALUES (1, 1);
INSERT INTO distinct_test VALUES (1, 2);
INSERT INTO distinct_test VALUES (1, NULL);

SELECT COUNT(*) FROM distinct_test;
# Result 3 as expected

SELECT DISTINCT a, b FROM distinct_test;
# 3 records as expected

SELECT COUNT(DISTINCT a, b) FROM distinct_test;
# Result 2. As expected as of http://bugs.mysql.com/bug.php?id=3199

SELECT COUNT(*) FROM (
  SELECT DISTINCT a, b FROM distinct_test
) AS foo;
# Result 3. Hmmm...

Suggested fix:
Make the two last queries return the same result OR document why they behave differently.
[13 Jul 2006 12:13] Valeriy Kravchuk
Thank you for a problem report. Sorry, but as explained in that bug #3199:

"Manual defines our nonstandard extension COUNT(DISTINCT a,b,c,...)
as being equivalent to standard COUNT(DISTINCT CONCAT(a,b,c,...))

COUNT(a,b,...,NULL) is NULL
and COUNT(DISTINCT expr) ignores NULLs, as it should."

SELECT COUNT(*) FROM anything;, to the contrary, returns number of rows in that table or row source. So, as SELECT DISTINCT a,b returns 3 rows, the result 3 is correct as well.