Why does MySQL allow HAVING to use SELECT aliases?

Question

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 
HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5
Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

• Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
• Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?

Answer 1

Well when you have a question of this sort the best source of information IMHO is MySQL documentation. Now to the point. This is the behavior of MySql extension to GROUP BY which is enabled by default.

MySQL Extensions to GROUP BY
MySQL extends this behavior to permit the use of an alias in the HAVING clause for the aggregated column

If you want standard behavior you can disable this extension with sql_mode ONLY_FULL_GROUP_BY

SET [SESSION | GLOBAL] sql_mode = ONLY_FULL_GROUP_BY;

If you try to execute the above-mentioned query in ONLY_FULL_GROUP_BY sql_mode you'll get the following error message:

Non-grouping field 'Amount' is used in HAVING clause: SELECT YEAR(orderdate), COUNT(*) as Amount FROM Orders GROUP BY YEAR(orderdate) HAVING Amount > 1

Here is SQLFiddle demo

Therefore it's up to you how to configure and use your instance of MySQL.

Answer 2

Good question.

I think you should run these querys

EXPLAIN SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 
HAVING Amount>1;
SHOW WARNINGS;

and check how the query is rewritten. iam pretty sure the query optimizer replace Amount with COUNT(*)

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 
HAVING COUNT(*)>1;

Like it does with

select 
 *
from 
 test
where 
 id = 5 - 3

after query optimizer its something like this.

select 
 test.id as 'id'
from 
 test
where 
 test.id = 2