Debbie-Leigh wrote:Hi,
My hoster has recently updated their servers to the all new version 5s of PHP and MySQL, which has caused a bit of a problem with some of my queries. It seems to be an obscure one and I can't seem to find anything in the manuals about it.
The query that I'm using is:
SELECT t1.my_col1
, t2.my_col2
, t3.my_col3
, t4.my_col4
FROM offer t1
, price_type t2
LEFT JOIN processor t3
ON t3.processor_id = ''
LEFT JOIN currency t4
ON t4.currency_id = t1.currency_id
WHERE t1.offer_id = 1
AND t1.price_type_id = t2.price_type_id
The problem is with the ON of the second LEFT JOIN. If I run it as it is i.e. comparing the two columns, it says 'Unknown column t1.currency_id', but the currency_id column definitely exists on the offer table. However, when I change that line to t4.currency_id = 1 or t1.currency_id = 1, the query works fine. The query also works fine in v4.
Obviously, I can't run it using t1.currency_id = 1, so could anyone tell me whether there was a change in the way ON clauses work between v4 and v5 of MySQL, as I can't find any indiction in the manuals about this?
Debbie
Yes, things change from v4 to v5. They fixed the query planner. The SQL Spec says that explicit joins are always evaluated before implicit joins. I.e. this:
table 1 join table 2 on (t1.id=t2.t1id)
is an explicit join, this:
table1, table2 where t1.id=t2.t1id
is an implicit join. So, what the spec says is that in your query, the tables are joned like so:
FROM offer t1 -- Last join is t2/t3/t4 to t1
, price_type t2
LEFT JOIN processor t3
ON t3.processor_id = '' -- First join is t2 to t3
LEFT JOIN currency t4
ON t4.currency_id = t1.currency_id -- Second join is t2/t3 to t4
WHERE t1.offer_id = 1
AND t1.price_type_id = t2.price_type_id
So, how is the on t4.currency_id=t1.currency_id supposed to match rows when, as far as that join is concerned, t1 doesn't exist yet.
I would suggest picking one join style and sticking to it to make this easier for you. Your query would look like this:
SELECT t1.my_col1
, t2.my_col2
, t3.my_col3
, t4.my_col4
FROM offer t1
join price_type t2
on (t1.price_type_id = t2.price_type_id)
LEFT JOIN processor t3
ON (t3.processor_id = '')
LEFT JOIN currency t4
ON (t4.currency_id = t1.currency_id)
WHERE t1.offer_id = 1
Which should run faster anyway as t1 <-> t2 seems like it should be more selective, especially if t1 is part of the where clause.
