Are there changes in left joins in mysql 4 -> 5?

[gudmunson]

Hello guys,

I have a question about mysql versions...I have a query that has worked great for a year or so:

SELECT DISTINCT Schools.name AS school_name, Schools.city, Countries.country, Schools.id AS school_id
FROM Programs_terms, Terms
LEFT JOIN Programs ON Programs_terms.program_id = Programs.id
LEFT JOIN Programs_Relationship ON Programs_Relationship.id = Programs.type
LEFT JOIN Schools ON Programs.School_id = Schools.id
LEFT JOIN Countries ON Schools.country = Countries.id
LEFT JOIN Programs_language ON Programs.id = Programs_language.program_id
LEFT JOIN Programs_Relationship_Type ON Programs_Relationship.type_ag = Programs_Relationship_Type.id
WHERE Terms.id = Programs_terms.term
AND Programs_terms.Display = 'Y'
AND Schools.Display = 'Y'
LIMIT 0 , 30

When I use phpMyAdmin 2.6.1 and MySQL 4.0.16 this query is successful. On another system, running phpMyAdmin 2.7.0-pl2 and MySQL 5.0.18, it doesn't work. Has something changed in the syntax of mysql which would make this query suddenly not run correctly? I'd like to think it's a configuration issue but at this point I'm not sure.

Thanks,
Nathan

[fenway]

Yes, there was a major change:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Before MySQL 5.0.12, 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.[/quote]

Basically, your first two tables were implicity grouped together, and now they are not, so the JOIN doesn't "use" the first table. You can fix this by simply changing the comma to JOIN.

Hope that helps.

[gudmunson]

[!--quoteo(post=340026:date=Jan 25 2006, 09:02 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 25 2006, 09:02 PM) [snapback]340026[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yes, there was a major change:
Basically, your first two tables were implicity grouped together, and now they are not, so the JOIN doesn't "use" the first table. You can fix this by simply changing the comma to JOIN.

Hope that helps.
[/quote]

Thanks, that does help. Unfortunately I have about 50,000 lines of code with many, many join statements, so I think I'm better off installing mysql 4 at this point. At least I know why now.

Thanks!
Nathan

[fenway]

True -- however, I think there is a way to get MySQL5 to run in version 4 emulation mode. Some sort of server mode, I think. Then again, go with what works. Good luck.