Peter Brawley <peter.brawley@stripped> 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
>
> -----
>
> SGreen@stripped wrote:
> James Black <jblack@stripped> 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 james@stripped
> -----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
