MySQL Bugs: #45963: DWIM: Allow group functions in WHERE clause, by doing GROUP BY first

Bug #45963	DWIM: Allow group functions in WHERE clause, by doing GROUP BY first
Submitted:	6 Jul 2009 8:48	Modified:	6 Jul 2009 10:41
Reporter:	Chris Hennick	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.75-0ubuntu10.2 (Ubuntu)	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DWIM, GROUP BY, where

Description:
The following statement yields an error:

select name, max(allowed) from (copy_limit group by name) where max(allowed) < 4;

even though this statement is the intuitive way to accomplish what it intends.

How to repeat:
create table copy_limit (name varchar(50), place varchar(20), allowed int unsigned, primary key (name, place));
select name, max(allowed) from (copy_limit group by name) where max(allowed) < 4;

Suggested fix:
When not in strict SQL mode, treat the above statement as equivalent to:

select * from (select name, max(allowed) as max from copy_limit group by name) foo where max < 4;

tagging as "DWIM"

Check HAVING clause of SELECT statement (see http://dev.mysql.com/doc/refman/5.1/en/select.html for the details), please. I think it does exactly what you need, without any new syntax or subqueries in FROM clause.

Changing WHERE to HAVING does indeed work. So the simplest DWIM will probably just be to move the invalid WHERE terms to a HAVING clause (and, where necessary, split the query with a UNION).

So, looks like there is no real need to introduce any new syntax.