MySQL Bugs: #15497: sql -statement with join on failed

Bug #15497	sql -statement with join on failed
Submitted:	5 Dec 2005 19:14	Modified:	19 Sep 2006 11:36
Reporter:	Peter Lenser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.17	OS:	Windows (WIN XP pro)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Source Editors

Description:
the following sql statement is'n successfull with the Query Browser.

select cd.titel, f.name from cd join  (verleihtabelle t join freunde f on t.freundenr=f.nummer) on cd.nummer=t.cdnummer;

Column 'Nummer' in field list is ambiguous

with mysqlmanager or mysql.exe (DOS-Box) it works perfectly.

Greetings

peter lenser 

How to repeat:
I could send to you my database. It's a simple database for our students. 
cd (nummer,title);
freunde (nummer,name,..)
verleihtabelle(nummer,freundenr,cdnummer,verleihdatum,rückgabedatum);

this is the database

Attachment: cdverleih.zip (application/zip, text), 2.97 KiB.

Thank you for a bug report. Verified just as described, on your database. Query works in mysql client, but goves error message in QB 1.1.17 on XP.

Another example of possible the same issue: this only fails in the query browser (1.1.18) but works from the command line to the server (5.0.18):

SELECT $N1.lex AS x, $N2.lex AS y, $N3.lex AS z FROM
( ( Triples AS $T1
  INNER JOIN Nodes AS $N1 )
 INNER JOIN Nodes AS $N2 )
 INNER JOIN Nodes AS $N3
WHERE $T1.s = $N1.id AND $T1.p = $N2.id AND $T1.o = $N3.id

but full bracketting, just one level, or removing all the brackets works OK 
(the query is machine-produced)

Browser: 1.1.18

Works at command line (5.0.18)

I can confirm this behaviour, and also offer a workaround (and possibly an insight into the problem).
If you include one of the columns you join on that is NOT in the field list (in this case the field 'nummer') in the field list, the query works.

So the following should work:
select cd.titel, f.name, cd.nummer from cd join  (verleihtabelle t join freunde f on
t.freundenr=f.nummer) on cd.nummer=t.cdnummer;

I can confirm this behaviour (with 1.1.20), and also offer a workaround (and possibly an insight into the problem).
If you include one of the columns you join on that is NOT in the field list (in this case the field 'nummer') in the field list, the query works.

So the following should work:
select cd.titel, f.name, cd.nummer from cd join  (verleihtabelle t join freunde f on
t.freundenr=f.nummer) on cd.nummer=t.cdnummer;

I bumped into this defect today while doing some testing around the QB. The scenario described is much more complex than it needs to be to experience the failure.  Whenever you try to join two or more tables using standard syntax like:
ON (t1.ID=t2.ID) OR...
USING (ID)

The query will fail, unless you also include one of the ID columns in the SELECT.  When we have the opportunity to do so, it would be good to resolve this.

This bug has been quite annoying for wuite a long while - I deal with the issue on nearly all of my queries and have to add the primary key/other join columns to the query (normally I just copy/paste queries I use in my software, so adding columns everytime is a nuisance).  Seems like this has been open an awfully long time - any progress?  I noticed it is still not fixed in 1.1.20 beta :(

Bob

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html