List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 13 2005 1:12pm
Subject:Re: possible bug in mysql 5.0.13
View as plain text  
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
Thread
re: possible bug in mysql 5.0.13James Black12 Oct
  • re: possible bug in mysql 5.0.13SGreen12 Oct
    • Re: possible bug in mysql 5.0.13Peter Brawley12 Oct
      • Re: possible bug in mysql 5.0.13James Black12 Oct
      • Re: possible bug in mysql 5.0.13James Black12 Oct
        • Re: possible bug in mysql 5.0.13SGreen12 Oct
          • Re: possible bug in mysql 5.0.13James Black12 Oct
          • Re: possible bug in mysql 5.0.13Peter Brawley12 Oct
            • Re: possible bug in mysql 5.0.13SGreen13 Oct