Quirk in MySQL Join Conditions

I found a quirk in a join condition today that caused too many records to display. Look at the following query and notice the two AND clauses after the LEFT JOIN to activitytype.


SELECT *
FROM organization grouptable
INNER JOIN (
SELECT c.CommunityID, o.OrganizationID, d.DivisionID
FROM
community c
LEFT JOIN organization o ON c.communityID = o.communityID
LEFT JOIN division d ON o.organizationID = d.organizationID
WHERE c.communityID = 1
) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID
LEFT JOIN activity a ON a.memberID = m.memberID
LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
AND hasDistance = 1
AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
ORDER BY activitydate


This query runs and returns 2918 rows. However, when I audit this data, I get rows back that are outside of the time bounds specified in the BETWEEN clause: ( AND activityDate BETWEEN ‘2011-08-01 00:00:00’ AND ‘2011-10-24 13:38:14’ ). There is no activityDate column on the table activitytype. There is an activityDate column on the activity table however. This means the query is parsed and executed without MySQL throwing an error, but the expression is not used to limit the number of joined rows. The correct recordset (428 rows) is easily obtained by moving the join condition to the correct join statement.

SELECT *
FROM organization grouptable
INNER JOIN (
SELECT c.CommunityID, o.OrganizationID, d.DivisionID
FROM
community c
LEFT JOIN organization o ON c.communityID = o.communityID
LEFT JOIN division d ON o.organizationID = d.organizationID
WHERE c.communityID = 1
) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID
LEFT JOIN activity a ON a.memberID = m.memberID AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
AND hasDistance = 1
ORDER BY activitydate


I hope this helps someone else with their MySQL queries.