Bug #38879 The Sql Syntax Error
Submitted: 19 Aug 2008 2:34 Modified: 19 Aug 2008 7:34
Reporter: huang fs Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:MySQL Server 4.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: Unknown column 'column' in 'where clause'

[19 Aug 2008 2:34] huang fs
Description:
My Sql:
"set @mycnt = 0;
select (@mycnt := @mycnt + 1) as ROWNUM,Company,LoginName,ID, Type, ConplaintUserID, ComplaintDate, Title, Content, Category, TargetID, AdmissibleDate, AdmissibleSituation, Status 
from 
( 
select a.Company,a.logid LoginName,ID, Type, ConplaintUserID, ComplaintDate, Title, Content, Category, TargetID, AdmissibleDate, AdmissibleSituation, Status FROM mvb2k400_complaint t left join mvb2k400_accountmain a on t.ConplaintUserID=a.Acctid where Type='User' 
union
select a.Company,a.AgentName LoginName,ID, Type, ConplaintUserID, ComplaintDate, Title, Content, Category, TargetID, AdmissibleDate, AdmissibleSituation, Status FROM mvb2k400_complaint t left join mvb2k400_agent a on t.ConplaintUserID=a.agentid where Type='Agent' 
) 
mvb2k400_complain " the result is ok,the "ROWNUM" in the columns.Bug when I add "where  ROWNUM>=0 and ROWNUM<=10"  string ,the system error is "Unknown column 'ROWNUM' in 'where clause' ".

How to repeat:
Select error...
[19 Aug 2008 5:46] Valeriy Kravchuk
This is not a bug. Please, read the manual, "http://dev.mysql.com/doc/refman/4.1/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;"

So, use "WHERE @mycnt > 0 and ...".
[19 Aug 2008 7:34] huang fs
But why this syntax is Ok???And that just the same,for example:
"
set @mycnt = 0;select * from ( SELECT  (@mycnt := @mycnt + 1) as ROWNUM, ID, Type, ConplaintUserID, ComplaintDate, Title, Content, Category, TargetID, AdmissibleDate, AdmissibleSituation, Status FROM mvb2k400_complaint ) mvb2k400_complaint where ROWNUM>=0 and ROWNUM<=10 ; 
"

Thank for answer.
[19 Aug 2008 7:50] huang fs
the sql result.

Attachment: sql result.JPG (image/pjpeg, text), 45.17 KiB.