307091 - [MySQL] MySQL 5.0.12 changed certain JOIN rules

Status: RESOLVED WORKSFORME

(Bugzilla :: Database, defect)

Description

[london3]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.7.10) Gecko/20050717 Firefox/1.0.6

Have a look at this mysql bug : http://bugs.mysql.com/bug.php?id=12964
the bugzilla is not compatible with latest version of Mysql as "Natural joins
and joins with USING, including outer join variants, now are
processed according to the SQL:2003 standard" It is easy to update. Just read
the bug. I think it is only one sql query that have problem. I will past it
below. It is also pasted in the mysql bug. If you need server space with mysql
5.12 just email me I give you a test account ASAP.

Reproducible: Always

Steps to Reproduce:
Just run under Mysql 5.12:
SELECT DISTINCT 
bugs.bug_id, bugs.groupset, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, 
bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles
map_assigned_to, profiles map_reporter 
LEFT JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid
LEFT JOIN cc selectVisible_cc ON 
                     bugs.bug_id = selectVisible_cc.bug_id AND 
                     selectVisible_cc.who = 3 WHERE ((bugs.groupset & 127) =
bugs.groupset OR (bugs.reporter_accessible = 1 AND 
                     bugs.reporter = 3) OR (bugs.cclist_accessible = 1 AND
selectVisible_cc.who = 3 
                     AND not isnull(selectVisible_cc.who)) OR (bugs.assigned_to
= 3)) AND bugs.assigned_to = map_assigned_to.userid 
                     AND bugs.reporter = map_reporter.userid AND
(bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR 
                     bugs.bug_status = 'REOPENED') AND (bugs.product =
'AstBill') ORDER BY bugs.bug_status, bugs.priority, 
                     map_assigned_to.login_name, bugs.bug_id;
Actual Results:  
This error comes when you run any query in mysel
ERROR 1054 (42S22): Unknown column 'bugs.qa_contact' in 'on clause'


If you need server space with mysql 5.12 just email me I give you a test account
ASAP.

Comment 1

[Max Kanat-Alexander]

Actually, we don't officially support MySQL 5, yet. But if the fix is easy, I
suppose we could also add this to the 2.20 branch.

Although, actually, looking at this, I'm fairly sure we already fixed all these
sorts of things for 2.20 (PostgreSQL requires it) -- what version of Bugzilla
are you using?

Comment 2

[Dave Miller [:justdave]]

I think we did change this for Pg already....

This is one of those cases where it's best to do explicit inner joins instead of
the implicit comma joins, which is, in fact, what we did to fix it...

2.18 has this:

>        push @supptables, "profiles AS map_assigned_to";
>        push @wherepart, "bugs.assigned_to = map_assigned_to.userid";

where 2.20 has this:

>        push @supptables, "INNER JOIN profiles AS map_assigned_to " .
>                          "ON bugs.assigned_to = map_assigned_to.userid";

I suspect that 2.18 will no longer be supported by the time MySQL 5 is
considered production-ready, so we probably don't need to rush on backporting
this.  The type of change needed to Search.pm is fairly high-risk, and I'd be
hesitant to backport it anyway, even if it was considered production-ready,
since 2.20 and up work.  Anyone who can run a cutting-edge MySQL can run a
cutting-edge Bugzilla. :)  (or so I would hope)

So for 2.20 and trunk, this is WORKSFORME.
For 2.18 this is WONTFIX.