MySQL alias, variables and the WHERE clause

What a nice morning i had remembering that you cannot reference an ALIAS or a variable in a WHERE clause. So for anyone else that has a memory like mine (i'm jealous of fish put it that way) standard SQL doesn't allow you to refer to a column alias in a where cause. Which make total sense (now) as the column value is most likely not determined at time of execution. So....
SELECT
\n @total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
\nFROM
\n mytable
\n#WHERE
\n# @total > now() /*Invalid*/
\n#WHERE
\n# newDate > now() /*Invalid*/

comments powered by Disqus