MySQL Bugs: #39970: count distinct reports zero

Bug #39970	count distinct reports zero
Submitted:	10 Oct 2008 9:37	Modified:	16 Oct 2008 11:00
Reporter:	Finotello Andrea	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51a, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any (Linux, MS WindowsW2K3 community edition)
Assigned to:		CPU Architecture:	Any
Tags:	COUNT DISTINCT

Description:
a select count(disctinct col1,col2) on a table where col2 have null values reports wrong total rows

How to repeat:
create table `test_count`( `ID` int NOT NULL , `LONGFK` bigint , `VALUE` varchar(255) , PRIMARY KEY (`ID`))  ;

insert into `test_count`(`ID`,`LONGFK`,`VALUE`) values ( '1',NULL,'BLA');
insert into `test_count`(`ID`,`LONGFK`,`VALUE`) values ( '2',NULL,'LOL');
insert into `test_count`(`ID`,`LONGFK`,`VALUE`) values ( '3',NULL,'BYE');

select count(distinct ID,LONGFK) as TOT from TEST_COUNT; // aspected result is 3 but mysql reports 0

then if you

update `test_count` set `LONGFK`='4' where `ID`='2'; 

and repeat 

select count(distinct ID,LONGFK) as TOT from TEST_COUNT; // aspected result is 3 but mysql reports 1

Thank you for the report.

Verified as described.

Workaround: select count(distinct id,ifnull(longfk,0)) as tot from test_count;

Probably documentation issue as COUNT counts not null values only and not clear how it should work in such case.

Sveta already explained why it's not a bug.

She forgot to add that COUNT(DISTINCT a,b,c)) is the same as COUNT(DISTINCT CONCAT(a,b,c)).