Bug #38879 The Sql Syntax Error

• Submitted: 19 Aug 2008 2:34
• Modified: 19 Aug 2008 7:34
• Reporter: huang fs
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: MySQL Server 4.1
• OS: Any
• 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.