Subtle Syntax Change in Outer Join

mySQL 5.02 - community

[txbakers]

So here it is 4:00 AM, I've been working on this since about midnight when a client noticed a mySQL error - field not found.

Field not found? How can that be? it's been there for years.

Turns out there is a subtle change in the LEFT JOIN syntax:

This worked in 4.X:
select * from tab1, tab2 LEFT JOIN tab3 on tab1.f = tab3.f WHERE tab1.g = tab2.g

But it doesn't work in 5.02. You need this:
select * from tab2, tab1 LEFT JOIN tab3 on tab1.f = tab3.f WHERE tab1.g = tab2.g

Notice the difference? I didn't either then it was nasty to fix all these.

In deference to themySQL folks, it is cleaner the new way, but what a pain.

If you have TWO LEFT JOINS it's even worse.

[mattglet]

txbakers-

I see the difference, but I'm not quite sure WHY it now has to be done this way?

[txbakers]

I'm not sure either, but it's a royal pain in the boot.

Errors are coming up all over the place because of this.

Date fields seem to have a differnt handling too, I'm getting errors on those now.....

[FalseDawn]

The queries are badly written anyway, combining ANSI and old-style join syntax - and it's not really clear what is joining to what.

select * from tab1, tab2 LEFT JOIN tab3 on tab1.f = tab3.f WHERE tab1.g = tab2.g

should probably have been written:

SELECT * FROM ((tab1 T1 JOIN tab2 T2 ON T1.g=T2.g) LEFT JOIN tab3 T3 ON T1.f=T3.f)