Description:
In accordance with the manual, as of MySQL 5.0.12 the precedence of the JOIN and the comma operator have been changed.
This is a major showstopper when trying to upgrade to MySQL5, because you will have to scan all applications to see if they execute queries that are affected by this change and you will have to add parentheses to correct the problem.
If you don't want to run different versions of MySQL in parallel, you're forced to touch "legacy" code that works with MySQL 4.1 or even earlier and that would not make use of the new features -- just to be able to use MySQL5 for "new" code.
See http://dev.mysql.com/doc/refman/5.0/en/join.html (the last two bullet points); the migration notes (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html) also note this one as an "incompatible change", though I find the remark too subtle compared to the impact :).
How to repeat:
SELECT ... FROM a, b LEFT JOIN c ON c.c1 = a.a1 WHERE ...
This no longer works as because "b LEFT JOIN c" now has a higher precedence and thus in the ON clause you can only access tables b and c. Before the change, "," and "LEFT JOIN" had the same precedence so it would first join a and b and the result of that would be the operand for the LEFT JOIN.
The problem is that this way of writing a query is very "natural"... assume you have tables user, country and email, where each user comes from a country and have 0 ... n email addresses. Then you would "naturally" write the query to get all users, resolve the country and see if they additionally have an email address:
SELECT ... FROM user, country LEFT JOIN email ON email.userid = user.id WHERE user.countryid = country.id
Suggested fix:
Maybe add a switch or configuration option? Not nice, but I have no other good idea.
