Bug #43038 The aliases on the fields does not work on where clause
Submitted: 19 Feb 2009 22:02 Modified: 19 Feb 2009 23:24
Reporter: Amir Aram Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.31 and 5.1.x and 5,0,67 and later OS:Any
Assigned to: CPU Architecture:Any
Tags: Aliases definitions on fields and using it in where clause

[19 Feb 2009 22:02] Amir Aram
Dear sir/madam,
The aliases does not work on the fields area when you use it in the where caluse and I got unknown column error ,
it works fine in the mysql5.0.51 and earlier version but it does not work on 5.0.67,5.0.77 and 5.1.31 

As you know it should work and it is a normal definition

Dr. Amir Aram

How to repeat:
The script is in attached

Suggested fix:
It should solve because it is a serious incompatibility
[19 Feb 2009 23:24] MySQL Verification Team
Thank you for the bug report. That behavior is according the SQL Standard and commented clearly in our Manual. Please see:


" Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;"