Bug #2976 NATURAL JOIN produces duplicate columns
Submitted: 26 Feb 2004 12:05 Modified: 30 Mar 2004 3:54
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Linux (GNU/Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[26 Feb 2004 12:05] Marko Mäkelä
I was told that according to the standard, NATURAL JOIN should report the joined columns only once.  However, it seems that MySQL reports the columns for each table separately, each column obviously containing the same data.

Thus, for instance, the following SQL doesn't work, although it works in another SQL database: SELECT field1 FROM table1 NATURAL JOIN table2;  MySQL thinks that field1 is ambiguous; you will need to write table1.field1 or table2.field1.

How to repeat:
create table t(a int);
create table u(a int);
insert into t values(1);
insert into u values(1);
select * from t natural join u;
| a    | a    |
|    1 |    1 |
1 row in set (0.00 sec)

Suggested fix:
Return each NATURAL JOINed column only once.  If there is such a thing as NATURAL OUTER JOIN, there you may want to retain the current behaviour, after checking the standard.
[27 Feb 2004 9:02] Sergei Golubchik
Whoever told it you, he was right.
This behaviour is specified by the standard.
7. Query expression
7.7 <joined tables>
Syntax Rules:
7) If NATURAL is specified or if a <join specification> immediately containing a <named columns join> is specified, then:
and below from here.

By the way for <named columns join> (that is USING (col1, col2, ...)) MySQL also reports each field twice.
[19 Mar 2004 0:38] Ramil Kalimullin
  1.1707 04/03/19 12:36:55 ram@gw.mysql.r18.ru +4 -0
  Fix for the bug #2976: NATURAL JOIN produces duplicate columns.
  Improvement natural join code in the setup_conds().
[30 Mar 2004 3:54] Ramil Kalimullin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at