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


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]