MySQL Bugs: #26087: Empty Result Set Returned When COUNT(), GROUP BY, and WHERE Are Used Together

Bug #26087	Empty Result Set Returned When COUNT(), GROUP BY, and WHERE Are Used Together
Submitted:	5 Feb 2007 15:27	Modified:	5 Feb 2007 18:23
Reporter:	Jason DeVelvis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Unix
Assigned to:		CPU Architecture:	Any
Tags:	count, Empty Result Set, GROUP BY, where

Description:
When I run a query similar to 

Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful WHERE useful = 0 GROUP BY comment_id

And there are no records that match useful=0, I get an empty result set returned instead of the desired 0.

However, if I remove the Where clause, I get this result set returned:

nothelpful  comment_id  
3           26 

(Note: I'm using phpMyAdmin)

The contents of my wp_dgrs_useful table are as follows:

comment_id  useful  member_id  
26          1       1 
26          1       2 
26          1       3 

The strange thing is that when I use Count(*) and the where clause, I get the desired 0 returned:

Select COUNT(*) as nothelpful FROM wp_dgrs_useful WHERE useful=0

Returns:

nothelpful  
0 

How to repeat:
Create a new table and add three rows:

comment_id  useful  member_id  
26          1       1 
26          1       2 
26          1       3

Run the following query on it:

Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful WHERE useful = 0 GROUP BY comment_id

mySQL will return an empty result set instead of 0

Suggested fix:
No idea

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You can not count empty result set.

----<START QUOTE>----
The strange thing is that when I use Count(*) and the where clause, I get the
desired 0 returned:

Select COUNT(*) as nothelpful FROM wp_dgrs_useful WHERE useful=0

Returns:

nothelpful  
0 
----<END QUOTE>----

On all versions of current sources mysqld returns correct error:

mysql> Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful 
    -> WHERE useful = 0;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

---<Start Quote>---

On all versions of current sources mysqld returns correct error:

mysql> Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful 
    -> WHERE useful = 0;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
GROUP columns is illegal if there is no GROUP BY clause

---<End Quote>---

Of course it would, but that wasn't the query I posted about. My issue was with the seemingly small difference between the following queries:

Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful WHERE
useful = 0 GROUP BY comment_id;

Select COUNT(*) as nothelpful FROM wp_dgrs_useful WHERE useful=0;

So, according to the documentation, if I have the following table:

comment_id  useful  member_id  
26          1       1 
26          1       2 
26          1       3
27          0       1
27          1       2
27          0       3

I can get a total count where the useful column=0 but I can't group that by comment_id and have comment_id 26 show up in that list with 0 records counted? How else am I supposed to know that comment_id=26 has no rows with useful=0 without wasting more processor time and memory running other queries and functions?

This is definitely a bug, count should return 0 instead of an empty result set, otherwise Count() is worthless with Group By unless you KNOW you'll always find records based on the where clause. But that simply isn't always the case.

*Addition: My issue with the query you ran in your first reply was because it wasn't among the queries I listed in my original post.

My brain was thinking faster than I was typing, so I skipped my explanation of why I quoted your response.

Thank you for the fixed queries.

I can not repeat issue using fixed queries and currend development sources:

drop database if exists bug26087;
create database bug26087;
use bug26087;
create table wp_dgrs_useful(comment_id int, useful int, member_id int);
insert into wp_dgrs_useful values(26, 1, 1), (26, 1, 2), (26, 1, 3);
insert into wp_dgrs_useful values(27, 0, 1), (27, 1, 2), (27, 0, 3);
Select COUNT(comment_id) as nothelpful, comment_id FROM wp_dgrs_useful WHERE
useful = 0 GROUP BY comment_id;
nothelpful      comment_id
2       27
Select COUNT(*) as nothelpful FROM wp_dgrs_useful WHERE useful=0;
nothelpful
2
drop database bug26087;

Hmmm, very strange, maybe there's something wrong with my settings or something. I'll check into that and if there's still a problem, I'll post again. Thank you for your time.