MySQL LEFT JOIN after 5.0.12 changes - How to rewrite query

Question

After 5.0.12 MySQL changed the syntax for left joins to match SQL2003 standard. So

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

needs to be rewritten as

... FROM (t1 , t2) LEFT JOIN t3 ON (expr

or else it will be parsed as ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

Now, I have an ancient app I'm porting from MySQL 3.23 (eek!) to 5.1, and the old code has this query:

select b.*, c.*, g.*, p.perfname, p.persname 
from bookings b, customer c 
left join grade g on b.chrggrade=g.grcode 
left join person p on b.person=p.percode 
where complete='Y' and invoiced='N' 
and datemade between '2009-03-25' and '2009-03-31' 
and c.custcode=b.cust 
order by cust, person, tsref, stdt

This fails with SQL error 1054, unknown column in b.chrggrade. This is because it's parsing as

select b., c., g.*, p.perfname, p.persname from bookings b, (customer c left join grade g on b.chrggrade=g.grcode ) left join person p on b.person=p.percode where complete='Y' and invoiced='N' and datemade between '2009-03-25' and '2009-03-31' and c.custcode=b.cust order by cust, person, tsref, stdt

I think.

I'm sure correctly placed brackets can fix this but I'm stumped. I found reference to this change at http://bugs.mysql.com/bug.php?id=13551, which shows how to fix a simple left join, but I still can't work it out for this query. David

Answer 1

Stop using the comma syntax altogether and be explicit in your JOIN statements. The comma syntax forces you to put the JOIN condition in the WHERE clause, which may not get executed until after LEFT/RIGHT joins, depending on how it's parsed. Using explicit JOINS makes the query more readable anyway.

...FROM t1, t2 LEFT JOIN t3 ON (expr) becomes ...FROM t1 INNER JOIN t2 ON (expr) LEFT JOIN t3 ON (expr)

That will also fix the error you are seeing. Unless there is no chrggrade in the bookings table, then nothing will fix the error.