Date: Tue, 28 Nov 2006 11:06:22 +0100> From: hart...@mysql.com> To:
mrib...@hotmail.com> CC: bu...@lists.mysql.com> Subject: Re: JOIN problem> >
Michael Ribbons wrote:> > Hi, not sure if this is a bug or a feature added with
the changes for nested JOINs, > > this code works fine under 4.0.24, but 2nd
last query fails under 5.0.26.> > JOIN behavior was changed starting with MySQL
5.0.12 to become> more ANSI compliant, check the "JOIN Syntax" manual page for>
"Join Processing Changes in MySQL 5.0.12"> >
vhttp://dev.mysql.com/doc/refman/5.0/en/join.html> > > > > Please observe the
following sql:> [...]> > SELECT ta.* FROM ta, tc LEFT JOIN tb ON tb.tb_id =
ta.ta_id;> > ERROR 1054 (42S22): Unknown column 'ta.ta_id' in 'on clause'> > *
Previously, the comma operator (,) and JOIN both had the same> precedence, so
the join expression t1, t2 JOIN t3 was interpreted> as ((t1, t2) JOIN t3). Now
JOIN has higher precedence, so the> expression is interpreted as (t1, (t2 JOIN
t3)). This change> affects statements that use an ON clause, because that
clause> can refer only to columns in the operands of the join, and the> change
in precedence changes interpretation of what those> operands are.> > so
rewriting the query like this should solve your problem:> > SELECT ta.* FROM
(ta, tc) LEFT JOIN tb ON tb.tb_id = ta.ta_id;> ^ ^> > > -- > Hartmut Holzgraefe,
Senior Support Engineer .> MySQL AB, www.mysql.com>