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: | |
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
[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.