Bug #51178 MySQL query running fine on v4.1.22 (Linux) but not working on v5.1.43 (Windows)

• Submitted: 15 Feb 2010 4:01
• Modified: 15 Feb 2010 5:01
• Reporter: Fulvio Cusumano
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S1 (Critical)
• OS: Windows
• CPU Architecture: Any
• Tags: linux, query, windows

[15 Feb 2010 4:01] Fulvio Cusumano

Description:

I have an application that executes the following MySQL query:

SELECT 402 AS user_id, p.id AS perm_id, p.name AS perm_name, lc.business_division_id, bd.name AS bd_name, bd.current_cycle, bd.current_moon, lc.name AS cycle_name, lc.milestone_date, lc.scorecard_date, bdm.name AS meta_name, bdm.value AS meta_value FROM lc_vc_cg_353.business_division bd, lc_vc_cg_353.business_division_meta bdm, lc_vc_cg_353.lunar_cycle lc LEFT OUTER JOIN lc_vc_cg_353.permissions ps ON ps.user_id = 402 AND ps.business_division_id = bd.id inner join lc_vc_central.permission p ON ((ps.privilege_id IS NOT null AND p.id = ps.privilege_id) OR (ps.privilege_id IS NULL AND p.id = 1024)) WHERE bd.active = 1 AND bdm.business_division_id = bd.id AND lc.business_division_id = bd.id AND lc.id = bd.current_cycle ORDER by bd.name asc;

The production server works fine and is running MySQL v4.1.22 (Redhat), however when I execute the same query on a Windows machine running MySQL v5.1.43-community it comes up with the following error:

ERROR 1054 (42S22): Unknown column 'bd.id' in 'on clause'

Any ideas on what the issue could be? Could it be that particular syntax has been deprecated in newer versions of MySQL?

Any help would be greatly appreciated.

How to repeat:
Execute above query on v4.x Linux and v5.x Windows and see the difference.

[15 Feb 2010 5:01] Valeriy Kravchuk

This is not a bug but documented change in behavior since 5.0.12 (AFAIR). Read http://dev.mysql.com/doc/refman/5.0/en/join.html:

"However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section."