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