joining activerecord with mysql 5

dhh committed a patch for activerecord to make it work with mysql 5 that was subsequently reverted because it broke things on postgres and sqlite.

obviously we’d like ruby on rails to work with mysql 5, but because there was no test case committed along with either of these changes, i don’t really know the root cause of the problem. dhh claims it is the changes that made mysql conform to the standard sql join syntax, but i can’t evaluate that because i can’t reproduce the problem.

any activerecord gurus want to point me in the right direction?

» ruby on rails, activerecord, mysql, code, work
« better out-of-the-box mysql support for ruby on railsripped-up roof »


The solution is not to use parentheses. The solution is to use SQL92 syntax for joins consistently.

In other words, instead of using SQL89 syntax like this:

SELECT ... FROM a, b, c WHERE ...conditions...

One should use SQL92 syntax like this:

SELECT ... FROM a JOIN b ON ...join condition...
JOIN c ON ...join condition...
WHERE ...non-join conditions...

All RDBMS implementations should support SQL92 joins, and it will eliminate the precedence problems with the new join semantics in MySQL 5.0.

The new semantics only cause problems when someone mixes SQL89 syntax with SQL92 syntax like this:

SELECT ... FROM a, b
LEFT JOIN c ON ...join condition...
WHERE ...conditions...

SQL92 syntax is better and has been standard for 14 years. But nearly everyone I see on newsgroups persists in using SQL89 syntax at every opportunity.

» Bill Karwin (link) » september 13, 2006 2:02pm

this entry is closed to new comments.