MySQL Bugs: #22874: ONLY_FULL_GROUP_BY conflicts with column alias

Bug #22874	ONLY_FULL_GROUP_BY conflicts with column alias
Submitted:	1 Oct 2006 17:23	Modified:	5 Oct 2006 9:24
Reporter:	Maciej Pilichowski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.24a	OS:	Linux (opensuse 10.0)
Assigned to:		CPU Architecture:	Any

Description:
Excerpt from the documentation:

You can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING clauses. Aliases can also be used to give columns better names: 
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;

But here is the problem -- turn ONLY_FULL_GROUP_BY on and run it again. Error.
Having is now not accepting alias "cnt" stating that is not-grouping field. True, but is aggregated column.

How to repeat:
See above.

Suggested fix:
Please check aliases if the "definition" is not an aggregate -- if yes, allow it in ONLY_FULL_GROUP_BY mode.

Thank you for the bug report. Sorry but this isn't a bug and behaves according
the SQL standard as an invalid column and the reason exactly is what you said:
cnt isn't a grouping column and can't be evaluated until the select list is done
and so after that too late.

> Sorry but this isn't a bug and behaves
> according
> the SQL standard as an invalid column 

Could you please give a quote?  Thanks in advance.

> cnt isn't a grouping column and can't be evaluated until the select
> list is done and so after that too late.

It is not a grouping column but it doesn't have to be. You can use aggregate function _OR_ grouping by column. They are both perfectly legal here.

When you have a statement like this
select count(*) as X from T having X>Y;

when X passes the test (or not) X won't change anymore. The having clause is guarantee for that. That is pretty the same reason you can this
select max(A) from T group by B; -- A is not in group-by
but you can't that
select A from T group by B; -- in SQL compliant way

Probably you are mistaken by "where" clause -- but I was not referring to it.

If you are not still convinced, another example, much clearer:

select F as X from T group by F having X>0;

Now, F and X are synonyms, but MySQL shows error again. MySQL simply does not check what the alias means. So please fix it for aggregates and grouped columns.

According to sections 7.9 <group by clause> and 7.10 <having clause> of SQL:2003 draft, expressins in HAVING depend on GROUP BY columns, that, in turn, depend on the FROM columns, NOT the select-list columns (and aliases you used there). 

So, this particular case seems to be not a bug.