MySQL Bugs: #2976: NATURAL JOIN produces duplicate columns

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (GNU/Linux)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

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

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.

ChangeSet
  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().

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
    http://www.mysql.com/doc/en/Installing_source_tree.html