mysql 5.0.12,5.0.12更改后的MySQL LEFT JOIN-如何重写查询

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

解决方案

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.