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:
- GROUP BY
- 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
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?
SELECT C, ROW_NUMBER() OVER (ORDER BY X) AS RN FROM T GROUP BY C HAVING RN = 1will be problematic as the
ROW_NUMBERruns after the
SELECT @rownum:=@rownum + 1 as row .... Maybe the reason why they support SELECT aliases simply is because they can, due to the fact that they don't support things that would make it impossible...who knows? :)
SELECTclause can be interchanged. So, there is no ambiguity in doing this and can simplify the looks of the code when there are monstrous expressions in
distincts) ... with the
Alias in the Havingdespite the same
Explainoutput. So some variation with the Optimizer is happening.