Peter Brawley <[EMAIL PROTECTED]> wrote on 10/12/2005 04:27:18 PM: > James, > > Both ... > > SELECT ... > FROM a, b LEFT JOIN c ON a.x=c.y > > and > > SELECT ... > FROM a > LEFT JOIN B USING (x ) > LEFT JOIN c ON a.x=c.y > > work up to and including version 5.0.10, not in 5.0.11, 12 or 13. > > http://bugs.mysql.com/bug.php?id=13832 reports... > > "The two statements below are quite different from one another: > 1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c > 2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c > > "Statement (1) above will likely continue to give an "Unknown column't1.a' in > 'on clause'" error, while statement (2) will likely function correctly at some > point in the future. > > "This bug report is in reference to statements like statement (2), and no bug > reports that use a statement like statement (1) are duplicates of this bug." > > The behaviour of (1) above is also verified (http://bugs.mysql. > com/bug.php?id=13551), and that page explains... > > "This is a change that was made in 5.0.15 [sic] to make MySQL more > compliant with the standard. According to the SQL:2003 > > <from clause> ::= FROM <table reference list> > <table reference list> ::= > <table reference> [ { <comma> <table reference> }... ] > <table reference> ::= > <table factor> > | <joined table> > <joined table> ::= > <cross join> > | <qualified join> > | <natural join> > ... > > "Thus when you write > > ... FROM t1 , t2 LEFT JOIN t3 ON (expr) > > it is parsed as > > (1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr)) > > and not as > > (2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr) > > so, from expr you can only refer to columns of t2 and t3 - operands > of the join. Workaround - to put parentheses explicitly as in (2). > Then you can refer to t1 columns from expr. > > Unfortunately, this change is not properly documented in the manual, > it will be fixed." > > PB > http://www.artfulsoftware.com > > ----- > > [EMAIL PROTECTED] wrote: > James Black <[EMAIL PROTECTED]> wrote on 10/12/2005 02:06:26 PM: > > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Peter Brawley wrote: > > James, > > You can reproduce that error by writing ... > > SELECT ... > FROM a, b INNER JOIN c ON a.x=c.y > > The error goes away if you instead write ... > > SELECT ... > FROM b, a INNER JOIN c ON A.x=c.y > > so you might try swapping > > FROM items i > , nams.netids n > > Tried that,now I get: > Unknown column 'n.badge'in 'on clause' > > So, whichever order I put them in, I get one of two errors. > > It appears that this bug will continue to break for me until it is > fixed in the next version, hopefully. > > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black [EMAIL PROTECTED] > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.1 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9 > cd7w92nB8uhOH2Y1+jAe4MU= > =FOcN > -----END PGP SIGNATURE----- > > > > What if, instead of using a comma, you use an explicit INNER JOIN? It's > perfectly valid to leave out the ON clause of an INNER JOIN (creating a > Cartesian product). I mention this because you don't seem to have a term > to use in an ON clause, unless you want to move the term n.netid='jblack' > from the WHERE clause. > > SELECT... > FROM items i > INNER JOIN nams.netids n > INNER JOIN ... > ... > > Does the problem remain? If it goes away, this would be useful information > to include in your bug report. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005 > > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005 >
Fantastic response!! Someone give this man a coffe mug or something! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
