MySQL Bugs: #48762: Having clause does not limit resultset correctly without group by being used

Bug #48762	Having clause does not limit resultset correctly without group by being used
Submitted:	13 Nov 2009 14:46	Modified:	13 Dec 2009 15:41
Reporter:	Corey Tisdale	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.40	OS:	Any (Tested on OSx and Vista64)
Assigned to:		CPU Architecture:	Any
Tags:	having clause, improper return

Description:
When I run a certain query, I cannot get having col != 'val' to remove all rows with this value, even when I change the data type from character to numeric data (using cast and not using cast). This bug exists at least from 5.1.25 to 5.1.40, and using query browser on OSX and vista64, and running the server on OSX and vista64

How to repeat:
Import the database backup I will attach and run the query I will attach afterwards. Notice how there is still a row with NA in it, but only one. Remove the having clause and see many rows with NA 

Suggested fix:
A temporary fix for the end user is to add a group by clause that groups by all the columns in the query. This way you don't actually lose any rows to grouping, but mysql figures out how to use the having clause correctly. This seems round about. I am not sure how to fix internally

Sorry, but why don't you use WHERE to filter rows and use HAVING instead? Please, read the manuam, http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html:

"The SQL standard does not allow the HAVING clause to name any column that is not found in the GROUP BY clause if it is not enclosed in an aggregate function. MySQL allows the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate."

So, are you sure that all columns have the same value in your single group (and, without GROUP BY, any result set is considered a single group...)?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".