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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.22
• OS: Windows (Windows)
• 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.