MySQL Bugs: #1505: column aliases not usable in where clause

Bug #1505	column aliases not usable in where clause
Submitted:	8 Oct 2003 23:21	Modified:	9 Oct 2003 9:44
Reporter:	axiom dadak	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.15a	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
An annoying little bug.

This query, creates the alias column, but return as an unknown column in the where clause.  

I was trying to perform a case and alias it as a column, which worked well, but I couldn't access it in the where clause.

How to repeat:
SELECT login as l 
FROM users
WHERE login = 'test';

returns
#1054 - Unknown column 'l' in 'where clause'

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

mysql> SELECT login as l
    -> FROM users
    -> WHERE login = 'test';
+------+
| l    |
+------+
| test |
+------+

Sorry, my mistake, I was up a bit too late trying to sort this out.

I ment to use the alias to access the column:

SELECT login as l 
FROM users
WHERE l = 'test';

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is how aliases work.

Consider order of execution of query in steps.

Step 1. Server first have to scan through rows in table to see which rows match your WHERE clause.

Step 2. For all rows matched in Step 1. server prepares content as specified in SELECT part. Retrieves columns, calculates expressions if any and labels columns with aliases

In other words Alias simply does not exist at the time when WHERE clause is processed