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:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.x OS:Linux (Linux/Windows)
Assigned to: CPU Architecture:Any

[23 Jun 2006 22:57] Radu Raduica
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.
[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)