Bug #10428 join ... using syntax cannot find column
Submitted: 7 May 2005 0:36 Modified: 10 May 2005 10:18
Reporter: Andrew Pimlott Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11-2 OS:Linux (Debian unstable)
Assigned to: Assigned Account CPU Architecture:Any

[7 May 2005 0:36] Andrew Pimlott
The "join ... using (col, ...)" syntax sometimes can't find the column ("col") when it should.  I hypothesize that this happens wheneven the column is not in the table immediately to the left of the join, but in a table joined further to the left.  I do not believe this limitation is in standard SQL; also, Postgresql does not have this limitation.  So I believe that this is a bug in Mysql.  (I am aware that I can work around it using "join ... on".)

How to repeat:
create table t1 (a boolean, b boolean);
create table t2 (a boolean);
create table t3 (b boolean);

select * from t1 join t2 using (a) join t3 using (b);

ERROR 1054 (42S22): Unknown column 'demo.t2.b' in 'on clause'

Postgresql processes this query as expected.
[9 May 2005 21:34] Andrew Pimlott
I suspect that this example demonstrates the same problem.  Starting with the same tables:

insert into t1 values (true, true);
insert into t2 values (true);
insert into t3 values (true);
insert into t3 values (false);
select * from t1 natural join t2 natural join t3;
| a    | b    | b    |
|    1 |    1 |    1 |
|    1 |    1 |    0 |

The natural join with t3 is not "seeing" the column b from t1.  Postgresql gives the expected answer:

 b | a 
 t | t
[10 May 2005 0:47] Andrew Pimlott
Probably the same as bug 470.
[10 May 2005 10:18] Timour Katchaounov
This bug is duplicate of BUG#9067 and is related to few other bugs mentioned in the
explanation for BUG#9067. It is a known problem that I am currently working on.
All known problems related to NATURAL/USING joins will be fixed in version 5.0.