Description:

Columns included in the join between two tables are ambigious they should *not* be ambigious!

The following is an extract (highlight) of the conversation on mysql-general mailing list...

It is not ambiguous according to the SQL standard. If this behaviour
of MySQL is documented it is an omission in the MySQL implementation
that you get the error. If this behaviour is not documented, it is a
bug.

> Now, it's not likely this is a valid query for your table structure

It is very likely it is. It is even an example in the MySQL manual.

Re: Example below...

> but, in this instance, a.pk and b.pk are not necessarily the same. b.pk
> could potentially be NULL while a.pk was not

There is nothing ambiguous about this example. The SQL standard is
very clear about the way field names should be resolved in in joins.
In this case the relevant quote is:

<quote>

7.7 <joined table> (..)
Syntax Rules (..)
7) If NATURAL is secified or if a <join specification> immediately containing a <named columns join> is specified, then: (..) d) If there is at least one corresponding join column, then let SLCC be a <select list> of <derived columns>s of the form COALESCE ( TA.C, TB.C ) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in RT1.

</quote> ISO/IEC 9075-2:2003
                                                                                
In a <named columns join> (i.e. a join with the USING keyword) every
column named in the join is only present once in the resultset. And
since the selection mechanism for the value uses COALESCE there is
absolutely no ambiguity in which value gets choses: never the NULL.

How to repeat:

select pk from a inner join b using (pk);
                                                                                
ERROR 1052 (23000): Column 'pk' in field list is ambiguous

Addendum:

> select pk from a inner join b on a.pk = b.pk+1;
>
> Would that be 'correctly' ambigious according to the sql specification?
                                                                                
That would indeed be ambiguous according to the SQL standard.
                                                                                

Suggested fix:

Always get it into the system. Even if it is considered not a bug but
a feature I think it warrants a documentation update.
But the problem with fixing this is that it is not backward compatible
and will break for everybody who qualifies his field names.

Here is the discussion thread on mysql-general

http://lists.mysql.com/mysql/183724

Timour is working on this right now
(and there're close to 10 bugreports about this issue already :)

Sorry about that - I searched the bug database for the word ambigious, but didn't find anything.

We are ready with a patch for 5.0 that corrects this and many other problems
related to JOIN ... USING and NATURAL JOIN. The patch will be pushed in
version 5.0 before it becomes GA. The problem will not be corrected in 4.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/28638

The bug is fixed by WL#2486 which is pushed into 5.0.12.

The changeset above adds only a test case.

NOTICE: This bug will not be fixed in 4.1.

Documented in 5.0.12 changelog:

<listitem><para><literal>SELECT <replaceable>PK</replaceable> FROM <replaceable>TableA</replaceable> INNER JOIN <replaceable>TableB</replaceable> USING(<replaceable>PK</replaceable>)</literal> resulted in a inappropriate <literal>Column ... is ambiguous</literal> error. (Bug #10646)</para></listitem>