In August Productions

Our friend Dathan recently suggested that The Quality of mySQL lately sucks.

I've just ran into yet another obvious bug that has made it's way into production.

While I'm not going to argue that more unit tests would be a good thing, I'd like to point out that what he's referring to is not so much a bug as it is a change that happened (and was documented) in 5.0.12:

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to JOIN, LEFT JOIN, and so forth.
These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 12.2.7.1, “JOIN Syntax”.

What this means is that the query in the bug report:

SELECT p.id, gt.object_id FROM Photos p, PhotosExtra px LEFT JOIN GeoTagged gt ON gt.object_id=p.id WHERE px.photo_id=p.id AND p.id = 2173;

should be rewritten to either:

SELECT p.id, gt.object_id FROM PhotosExtra px, Photos p LEFT JOIN GeoTagged gt ON gt.object_id=p.id WHERE px.photo_id=p.id AND p.id = 2173;

or even:

SELECT p.id, gt.object_id FROM , Photos p LEFT JOIN GeoTagged gt ON gt.object_id=p.id JOIN PhotosExtra px ON px.photo_id=p.id WHERE p.id = 2173;

This is a common thing people have to deal with when upgrading to 5.0.