MySQLTalk.org

Michael Ribbons · Guest

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.

Please observe the following sql:

CREATE DATABASE DB_JOIN_TEST;USE DB_JOIN_TEST;
CREATE TABLE ta ( ta_id int default 0 );CREATE TABLE tb ( tb_id int default 0 );CREATE TABLE tc ( tc_id int default 0 );

SELECT ta.* FROM ta LEFT JOIN tb ON tb.tb_id = ta.ta_id;
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'
SELECT ta.* FROM tc, ta LEFT JOIN tb ON tb.tb_id = ta.ta_id;

DROP DATABASE DB_JOIN TEST;

My problem is that a more complicated version of this bug exists in production software where it's not possible to change the FROM clause to put ta next to JOIN (if that's what is actually solving the error....)

Regards
_________________________________________________________________
Be one of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d

Hartmut Holzgraefe · Guest

Michael Ribbons wrote:

Valeriy Kravchuk · Guest

Hi Michael,

This:

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'

is not a bug, but a result of changing JOIN behaviour in 5.0.12 and
following versions. Please, read the manual,
http://dev.mysql.com/doc/refman/5.0/en/join.html:

" However, the precedence of the comma operator is less than than of INNER
JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the
other join types when there is a join condition, an error of the form
Unknown column 'col_name' in 'on clause' may occur."

Best regards,
--
Valeriy Kravchuk, Support Engineer
MySQL AB, Kiev, Ukraine, www.mysql.com

Are you MySQL certified? www.mysql.com/certification

Michael Ribbons · Guest

Thanks, and thanks to Valeriy Kravchuk too.