MySQL Bugs: #6495: Illogical requirement for column qualification in NATURAL join

Bug #6495	Illogical requirement for column qualification in NATURAL join
Submitted:	8 Nov 2004 10:18	Modified:	30 Aug 2005 4:14
Reporter:	Troels Arvin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
MySQL offers NATURAL JOINs as a way to make some SQL queries less verbose. One of the characteristics of a NATURAL join is that equally named columns are automatically used for joining. Another characteristic is that the equally named columns from each joined table are included only once in the result table. MySQL conforms to both of these characteristics.

However, MySQL requires that you explicitly qualify column identifiers in WHERE clauses, even for columns being joined on in a NATURAL join. Also, unless you use a "SELECT *" mass-projection, you need to qualify the name of a joined-on column in your SELECT list.

To me, that's confusing and illogical, and it makes NATURAL joins less practical for making (some) queries more terse (which is the whole point of NATURAL joins).

I'm not sure how to interpret the SQL standard on this. But MySQL's behaviour on these issues is different from MimerSQL's, PostgreSQL's and Oracle's (which all behave the same way here).

How to repeat:
Example: Two tables both have a column called "somecol":

mysql> SELECT * FROM tab1;
+---------+
| somecol |
+---------+
| a       |
| b       |
| c       |
| d       |
+---------+
mysql> SELECT * FROM tab2;
+---------+
| somecol |
+---------+
| b       |
| c       |
| d       |
| e       |
+---------+

This works fine:
===============
mysql> SELECT * FROM tab1 NATURAL JOIN tab2;
+---------+
| somecol |
+---------+
| b       |
| c       |
| d       |
+---------+

Note that only one instance of the "somecol" column is returned.

Failing queries:
===============
mysql> SELECT somecol FROM tab1 NATURAL JOIN tab2;
ERROR 1052 (23000): Column 'somecol' in field list is ambiguous
mysql> SELECT * FROM tab1 NATURAL JOIN tab2 WHERE somecol='b';
ERROR 1052 (23000): Column 'somecol' in where clause is ambiguous

This is rather a feature request. This fix will require serious modifications in name resolution procedure.
Most probably we'll introduce the suggested behaviour in 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28709

Fixed in 5.0.12.

The bug itself is fixed by WL#2486.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28717

Added the bug number for this bug to existing changelog entry for this task.