Bug #27849 version incompatibility for comma operator between 4.1.x and 5.0.x
Submitted: 16 Apr 2007 12:21 Modified: 18 Apr 2007 7:07
Reporter: Vadim Smelyansky Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.37 OS:Linux
Assigned to: CPU Architecture:Any
Tags: incompatibility
Triage: D5 (Feature request)

[16 Apr 2007 12:21] Vadim Smelyansky
Description:
found while migrating dotproject from 4.1.20 to 5.0.37
The same statement interpreted in different way. Following query work on version 4, but give 'Unknown column tasks.task_id in on clause' on version 5:

SELECT 
distinct tasks.task_id, task_parent, task_name, task_start_date, task_end_date, task_dynamic, task_pinned, pin.user_id as pin_user,
task_priority, task_percent_complete, task_duration, task_duration_type, task_project,
task_description, task_owner, task_status, usernames.user_username, usernames.user_id, task_milestone,
assignees.user_username as assignee_username, count(distinct assignees.user_id) as assignee_count, co.contact_first_name, co.contact_last_name,
count(distinct files.file_task) as file_count, tlog.task_log_problem, MAX(history_date) as last_update 
FROM tasks,user_tasks 
LEFT JOIN history ON history_item = tasks.task_id AND history_table='tasks' 
LEFT JOIN projects ON project_id = task_project 
LEFT JOIN users as usernames ON task_owner = usernames.user_id 
LEFT JOIN user_tasks as ut ON ut.task_id = tasks.task_id 
LEFT JOIN users as assignees ON assignees.user_id = ut.user_id 
LEFT JOIN contacts as co ON co.contact_id = usernames.user_contact 
LEFT JOIN task_log AS tlog ON tlog.task_log_task = tasks.task_id AND tlog.task_log_problem > '0' 
LEFT JOIN files on tasks.task_id = files.file_task 
LEFT JOIN user_task_pin as pin ON tasks.task_id = pin.task_id AND pin.user_id = 13 
WHERE project_active <> 0
                                        AND task_project             = projects.project_id
                                        AND user_tasks.user_id       = 13
                                        AND user_tasks.task_id       = tasks.task_id
                                        AND (task_percent_complete    < '100' OR task_end_date = '')
                                        AND projects.project_active  = '1'
                                        AND projects.project_status <> '4'
                                        AND projects.project_status <> '5'
        AND task_status = '0'
GROUP BY task_id
ORDER BY project_id, task_start_date

version 4 accept 'FROM tasks,user_tasks ' but for version 5 this part should be
replaced with explicit INNER JOIN like 'FROM tasks INNER JOIN user_tasks ' or parenthized like 'FROM (tasks,user_tasks) ' to work

How to repeat:
install dotproject
logon into it
click tasks

Suggested fix:
backward compatibility must be keeped!
[16 Apr 2007 12:40] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/join.html:

"Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
..."
[17 Apr 2007 20:07] Vadim Smelyansky
It is not a bug in code it is usability and backward compatibility bug
You can not just broke applications just because there is a new version of MySQL
There should be at least option in config file to keep it compatible (like old_passwords for example) may be old_join
[18 Apr 2007 7:07] Valeriy Kravchuk
I agree that some backward compatibility mode for JOINs will be very useful. So, this is a reasonable feature request, but, formally, not a bug.