MySQL Bugs: #74719: misleading error message in case of late NATURAL join

Bug #74719	misleading error message in case of late NATURAL join
Submitted:	6 Nov 2014 21:26	Modified:	7 Nov 2014 18:52
Reporter:	Programmer Old	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6.14-log	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any

Description:
When "NATURAL JOIN" follows otherwise stated JOINs, ambiguity is detected, but, since no field name is used with NATURAL JOIN, the message is mystifying.

(It looks as if one cannot use NATURAL JOIN after ON ....)

How to repeat:
5.6.14-log CREATE TEMPORARY TABLE TOBLE (ID INTEGER);

5.6.14-log CREATE TEMPORARY TABLE TUBLE (ID INTEGER);

5.6.14-log CREATE TEMPORARY TABLE TIBLE (ID INTEGER);

5.6.14-log select * from toble join tuble on toble.id = tuble.id;

5.6.14-log select * from toble join tuble on toble.id = tuble.id natural join tible;
ERROR 1052 (23000): Column 'ID' in from clause is ambiguous ****

5.6.14-log select * from toble natural join tuble natural join tible;

Suggested fix:
A distinct error message for this case, since the ambiguous field is not mentioned.

(And maybe it is better to capitalize "FROM" in the existing message.)

This is not a bug, but a consequence of the changes introduced in MySQL version 5.0.

Since that version, MySQL server follows strictly SQL standard 2003, which says (approximately):

"
       In the first SELECT statement, column j appears in both tables
       and thus becomes a join column, so, according to standard SQL,
       it should appear only once in the output, not twice.
"

So, simply you can not have one table that is present in two NATURAL JOINs or two JOINs with USING clause, both involving same table and same column from that table.

This entire matter is fully described in our manual, chapter on JOINs.

My complaint is not lack of NATURAL JOIN after different joins, but the misleading error message. The immediate problem is indeed ambiguity, but the ambiguous field --in my example "ID"-- is not evident at the point of error. Furthermore, because of the means of parsing up to that point, NATURAL JOIN, as you say, is out of order--nor is that part of the error message.

For internal use, as when a view is saved, MySQL normalizes & changes the constructs, but it is very bad form to base an error report on the normalized code, not on that which the user actually entered.

Actually, the error message is quite correct. Column 'ID' (in this case) is truly ambiguous. 

As the examples in JOIN chapter in our manual shows, common columns are presented and printed only once. So, you have two common columns and both with the same name. And, as SQL standards and theory tells us, a single column in a single table can not be part of two common columns. 

Hence, a server has found an ambiguous column, as it is present in two common columns.

The purpose of the error message is to be correct and not educational. And this error message is correct.