| Bug #10646 | Columns included in the join between two tables are ambigious in the select | ||
|---|---|---|---|
| Submitted: | 15 May 2005 19:22 | Modified: | 29 Aug 2005 22:06 |
| Reporter: | Are you mortal Then prepare to die. | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.9 | OS: | Linux (linux dec alpha) |
| Assigned to: | Timour Katchaounov | CPU Architecture: | Any |
[15 May 2005 19:24]
Are you mortal Then prepare to die.
Here is the discussion thread on mysql-general http://lists.mysql.com/mysql/183724
[15 May 2005 23:28]
Sergei Golubchik
Timour is working on this right now (and there're close to 10 bugreports about this issue already :)
[16 May 2005 8:30]
Are you mortal Then prepare to die.
Sorry about that - I searched the bug database for the word ambigious, but didn't find anything.
[16 Jul 2005 5:40]
Timour Katchaounov
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.
[22 Aug 2005 14:34]
Bugs System
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
[22 Aug 2005 14:36]
Timour Katchaounov
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.
[29 Aug 2005 22:06]
Mike Hillyer
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>
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.