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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.31 and 5.1.x and 5,0,67 and later
• OS: Any
• CPU Architecture: Any
• Tags: Aliases definitions on fields and using it in where clause

[19 Feb 2009 22:02] Amir Aram

Description:
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

Thanks
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:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

" 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;"