320477 - (mysql_join_bug) MySQL 5.0.16 requires joins to be specific

Status: RESOLVED DUPLICATE of bug 251960

(Bugzilla :: Database, defect)

Description

[Kevin Benton]

The code:

SELECT  products.name
FROM products, classifications
LEFT JOIN group_control_map
ON group_control_map.membercontrol = 3
   AND group_id NOT IN(6,3,10,4,1,7,14,13,2,5,12,8)
   AND group_control_map.product_id = products.id
WHERE group_id IS NULL
      AND classifications.id = products.classification_id
      AND classifications.name = 'Unclassified'
ORDER BY name

Causes syntax errors.  MySQL requires queries to be specific regaring what table the join is being joined to.  The following query works as a replacement for the above...

SELECT  products.name
FROM products
LEFT JOIN classifications
ON classifications.id = products.classification_id
   AND classifications.name = 'Unclassified'
LEFT JOIN group_control_map
ON group_control_map.membercontrol = 3
   AND group_id NOT IN(6,3,10,4,1,7,14,13,2,5,12,8)
   AND group_control_map.product_id = products.id
WHERE group_id IS NULL
ORDER BY name

I think we need to do a full scan of our query code to make sure we don't do the above.  Maybe someone with more in-depth knowlege of MySQL 5 can help me figure out why this didn't work in query.cgi from Bugzilla 2.19.1 against 5.0.16.  Also filing a bug on mysql.com for their part trying to find the answer...

Comment 1

[Frédéric Buclin]

This is not a critical bug as we haven't find anything wrong with Bugzilla running on MySQL 5.x so far. I added the URL to our QA installation running on MySQL 5.0.15 on landfill so that you can play with it.

Comment 2

[Kevin Benton]

http://bugs.mysql.com/bug.php?id=15800 is the bug I referred to above.

Comment 3

[Kevin Benton]

FYI - from the MySQL documentation...

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

Incompatible change: 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.

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 13.2.7.1, “JOIN Syntax”.

This begs the question - oes Landfill have classifications turned on?  If not, you won't see the issue.

Comment 4

[Kevin Benton]

This has already been addressed (looking at the tip) by refusing to allow implicit joins.  This was fixed in bug 251960.  Please be aware that this will cause upgrade issue for any user attempting to use MySQL 5.0.12 or higher with a Bugzilla version that does not have the patches for bug 251960.

*** This bug has been marked as a duplicate of 251960 ***

Comment 5

[Max Kanat-Alexander]

Yes--all the implicit joins were fixed for 2.20. PostgreSQL doesn't support mixing implicit and explicit joins, really.