MySQL Bugs: #17562: Left join issue

Bug #17562	Left join issue - Part 2
Submitted:	19 Feb 2006 22:16	Modified:	21 Feb 2006 10:23
Reporter:	Jim Risc	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:

SELECT m.nachname,t.name FROM mitarbeiter m
LEFT JOIN teams ON m.teamcode=t.code

-> DATABASE ERROR - Unknown column 'm.teamcode' in 'on clause' 

table mitarbeiter
--------------------
nachname|teamcode
--------------------
Maier|1

table teams
-----------
code|name
-----------
1|worker

with mysql 4.1 everything is/was ok. I have got problems from 5.0 on :-(

Like MySQL Bugs: #16883: Left join issue    only easier way...

How to repeat:
as simple as above...

Suggested fix:
Setting brakets does not help (Like MySQL Bugs: #16883)

SELECT nachname,t.name FROM (mitarbeiter m)
LEFT JOIN teams t ON m.teamcode=t.code

only: LEFT JOIN teams  t ON mitarbeiter.teamcode=t.code
works correctly...                  ^ full length

just tried this on linux using latest 5.0 source and couldn't reproduce it ...

I was unable to reproduce this under FC4 nor XP/SP2

Can you please give me your exact CREATE TABLE statements ?

Thanks

I have found the difference: it only occurs, if a second table is used in the FROM statement.

e.g.:
create database testjoin
use testjoin
create table mitarbeiter (nachname char(40),teamcode int)
create table teams (code int,name char(40))
create table city (code int,name char(40))

SELECT m.nachname,t.name FROM mitarbeiter LEFT JOIN teams t ON t.code=mitarbeiter.teamcode

Ok. Everything works so far but now try this: (second table added after FROM)

SELECT nachname,t.name FROM mitarbeiter,city LEFT JOIN teams t ON t.code=mitarbeiter.teamcode
-> Error: Unknown column 'mitarbeiter.teamcode' in 'on clause'

you can append "WHERE city.code=t.code=mitarbeiter.teamcode" but there is no different in the result. The tables don't need any content.

sorry: the first selection have to be:

SELECT nachname,t.name FROM mitarbeiter LEFT JOIN teams t ON t.code=mitarbeiter.teamcode

Then (with second table before LEFT JOIN) it is not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"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.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator."