Bug #3189 SUM and COUNT in subselects return null instead of 0
Submitted: 16 Mar 2004 8:15 Modified: 8 Oct 2005 13:07
Reporter: Sean Daniels Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1.1a OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[16 Mar 2004 8:15] Sean Daniels
Description:
SUM and COUNT operations in subselects return null when no records meet subselect criteria.

How to repeat:
select  p.id,
           (select count(pp.pageID) from tb_projects_pages pp where pp.projectID = p.id and 
pp.deleteDate is null) as totalPages
from    tb_projects p

For rows where the subselect criteria returns no result, the value of totalPages is NULL. The correct 
value should be 0.

Suggested fix:
Suggested fux would be to change the behavior so that 0 is returned in these cases.

As a workaround, I have been doing this:

select  p.id,
           ifnull((select count(pp.pageID) from tb_projects_pages pp where pp.projectID = p.id and 
pp.deleteDate is null),0) as totalPages
from    tb_projects p
[7 Oct 2005 22:06] Hartmut Holzgraefe
Can you please check whether you can reproduce this on current 4.1 versions
with your data? I couldn't, so i assume this has been fixed ...
[8 Oct 2005 13:01] Sean Daniels
I can no longer reproduce this bug in the version I am running in production (4.1.11).

Thanks!
[8 Oct 2005 13:07] MySQL Verification Team
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Thank you for the feedback.