Re: possible bug in mysql 5.0.13

[Peter Brawley]

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



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]