| Bug #28831 | ALIAS is where-condition | ||
|---|---|---|---|
| Submitted: | 1 Jun 2007 11:04 | Modified: | 1 Jun 2007 13:12 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | any | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Jun 2007 12:27]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Thank you for the report. But aliases is not allowed in WHERE clause. See also http://dev.mysql.com/doc/refman/5.0/en/select.html: # It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, “Problems with Column Aliases”.
[1 Jun 2007 13:12]
Peter Laursen
OK - it seems that I overlooked that B.1.5.4. Problems with Column Aliases .. paragraph!
Description: A query like: SELECT a as b where b = 'something'; returns 'unknown column' error. I do not know what is SQL standard in this respect. Microsoft Access accepts the above. Also I find it inconsistent that an ALIAS cannot be used in the where condition of a WHERE clause (and JOIN, actually) but it can be in the where condition of a HAVING clause. I did not find anything in the documentation clearly telling where ALIAS may be used and not. You can categorize this as a bug report or documentation request as you like! How to repeat: CREATE TABLE `aliastest` ( `id` bigint(20) unsigned NOT NULL auto_increment, `somedata` bigint(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /* this works */ SELECT id AS thefirst , count(somedata) AS thesecond FROM aliastest WHERE (id > 0) GROUP BY somedata HAVING (thesecond > 0); /* this does not work */ SELECT id AS thefirst , count(somedata) AS thesecond FROM aliastest WHERE (thefirst > 0) GROUP BY somedata HAVING (thesecond > 0); /* Error Code : 1054 Unknown column 'thefirst' in 'where clause' (0 ms taken) */ Suggested fix: In my opinion best would be to allow for use of alias!