JOIN problem

--_3e7fd670-c78e-45a9-9c1f-431065554429_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi, not sure if this is a bug or a feature added with the changes for neste=
d JOINs,=20
this code works fine under 4.0.24, but 2nd last query fails under 5.0.26.
=20
Please observe the following sql:
=20
CREATE DATABASE DB_JOIN_TEST;USE DB_JOIN_TEST;
CREATE TABLE ta ( ta_id int default 0 );CREATE TABLE tb ( tb_id int defa=
ult 0 );CREATE TABLE tc ( tc_id int default 0 );
=20
SELECT ta.* FROM ta LEFT JOIN tb ON tb.tb_id =3D ta.ta_id;
SELECT ta.* FROM ta, tc LEFT JOIN tb ON tb.tb_id =3D 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 =3D ta.ta_id;
=20
DROP DATABASE DB_JOIN TEST;
=20
My problem is that a more complicated version of this bug exists in product=
ion software where it's not possible to change the FROM clause to put ta ne=
xt to JOIN (if that's what is actually solving the error....)
=20
=20
Regards
____________________________________________________________ _____
Be one of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=3D5d21c51a- b161-4314-9b0e-=
4911fb2b2e6d=

--_3e7fd670-c78e-45a9-9c1f-431065554429_--

Re: JOIN problem

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


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: JOIN problem

--_f137cb25-209a-4eb4-a6f1-98c020fb1f39_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thanks, and thanks to Valeriy Kravchuk too.

> Date: Tue, 28 Nov 2006 11:06:22 +0100> From: hartmut@mysql.com> To: mribb=
ons@hotmail.com> CC: bugs@lists.mysql.com> Subject: Re: JOIN problem> > Mic=
hael Ribbons wrote:> > Hi, not sure if this is a bug or a feature added wit=
h 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 wi=
th MySQL 5.0.12 to become> more ANSI compliant, check the "JOIN Syntax" man=
ual page for> "Join Processing Changes in MySQL 5.0.12"> > vhttp://dev.mysq=
l.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 =3D ta.ta_id;>=
> ERROR 1054 (42S22): Unknown column 'ta.ta_id' in 'on clause'> > * Previo=
usly, 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 t=
hat 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 =3D ta.ta_id;> ^ ^> > > -- > Hartm=
ut Holzgraefe, Senior Support Engineer .> MySQL AB, www.mysql.com>=20
____________________________________________________________ _____
Be one of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=3D5d21c51a- b161-4314-9b0e-=
4911fb2b2e6d=

--_f137cb25-209a-4eb4-a6f1-98c020fb1f39_--