List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 12 2005 8:27pm
Subject:Re: possible bug in mysql 5.0.13
View as plain text  
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
>
>  
>

Attachment: [text/html]
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
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