| Bug #20673 | LEFT JOIN unknown column in ON clause | ||
|---|---|---|---|
| Submitted: | 23 Jun 2006 22:57 | Modified: | 24 Jun 2006 2:27 |
| Reporter: | Radu Raduica | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 5.x | OS: | Linux (Linux/Windows) |
| Assigned to: | CPU Architecture: | Any | |
[24 Jun 2006 2:27]
MySQL Verification Team
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 Pleas read: http://dev.mysql.com/doc/refman/5.0/en/join.html mysql> SELECT a.iIdRight, c.iIdUser, f.iIdForm, a.iRight, f.cFormTitle -> FROM tforms AS f, tusers AS c -> LEFT JOIN trights AS a ON a.iIdUser = c.iIdUser AND f.iIdForm = -> a.iIdForm -> WHERE f.iFormInRights = 1 AND c.iIdUser = 1; ERROR 1054 (42S22): Unknown column 'f.iIdForm' in 'on clause' mysql> mysql> SELECT a.iIdRight, c.iIdUser, f.iIdForm, a.iRight, f.cFormTitle -> FROM (tforms AS f, tusers AS c) -> LEFT JOIN trights AS a ON a.iIdUser = c.iIdUser AND f.iIdForm = -> a.iIdForm -> WHERE f.iFormInRights = 1 AND c.iIdUser = 1; Empty set (0.00 sec)
Description: I have 3 tables tforms, tusers, trights and i do the following simple query mysql> SELECT a.iIdRight, c.iIdUser, f.iIdForm, a.iRight, f.cFormTitle -> FROM tforms AS f, tusers AS c -> LEFT JOIN trights AS a ON a.iIdUser = c.iIdUser AND f.iIdForm = a.iIdForm -> WHERE f.iFormInRights = 1 AND c.iIdUser = 1; I get the following error: ERROR 1054 (42S22): Unknown column 'f.iIdForm' in 'on clause' Is ok if i rewrite the query in the following way: mysql> SELECT a.iIdRight, c.iIdUser, f.iIdForm, a.iRight, f.cFormTitle -> FROM tusers AS c, tforms AS f -> LEFT JOIN trights AS a ON f.iIdForm = a.iIdForm -> WHERE f.iFormInRights = 1 AND a.iIdUser = c.iIdUser AND c.iIdUser = 1 How to repeat: Just create the tables with the following structure: mysql> desc tusers; +---------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | iIdUser | int(10) unsigned | NO | PRI | NULL | auto_increment | | cUserLogin | varchar(20) | NO | | | | | cUserPassword | varchar(20) | NO | | | | | cUserFullName | varchar(100) | NO | | | | +---------------+------------------+------+-----+---------+----------------+ mysql> desc tforms; +---------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+----------------+ | iIdForm | int(10) unsigned | NO | PRI | NULL | auto_increment | | cFormName | varchar(30) | NO | | | | | cFormTitle | varchar(100) | NO | | | | | iFormInRights | tinyint(1) unsigned | NO | | 1 | | | cFormReport | varchar(100) | NO | | | | +---------------+---------------------+------+-----+---------+----------------+ mysql> desc trights; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | iIdRight | int(10) unsigned | NO | PRI | NULL | auto_increment | | iIdUser | int(10) unsigned | NO | PRI | 0 | | | iIdForm | int(10) unsigned | NO | PRI | 0 | | | iRight | tinyint(1) unsigned | NO | | 0 | | +----------+---------------------+------+-----+---------+----------------+ Try to execute the query.