updating old query?

I am migrating bugzilla from mysql 3 to mysql 5. I cannot upgrade
bugzilla itself yet, I must migrate it first. One (probably several)
of the queries is of the form 'select ... from ... left join ... on'.
The problem seems to be table aliasing between the left join ... and
the on (LEFT JOIN profiles map_qa_contact ON). Is there an updated
syntax I can use to massage the query and get this part working?

Mike

actual query:

SELECT bugs.bug_id, bugs.groupset, substring(bugs.bug_severity, 1, 3),
substring(bugs.priority, 1, 3), substring(bugs.rep_platform, 1, 3),
map_assigned_to.login_name, substring(bugs.bug_status,1,4),
substring(bugs.resolution,1,4), substring(bugs.short_desc, 1, 60)
FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT JOIN
profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid
WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.reporter
= map_reporter.userid AND bugs.groupset & 0 = bugs.groupset GROUP
BY bugs.bug_id;

Re: updating old query?

On 2006-08-16, Mike wrote:
> I am migrating bugzilla from mysql 3 to mysql 5. I cannot upgrade
> bugzilla itself yet, I must migrate it first. One (probably several)
> of the queries is of the form 'select ... from ... left join ... on'.
> The problem seems to be table aliasing between the left join ... and
> the on (LEFT JOIN profiles map_qa_contact ON). Is there an updated
> syntax I can use to massage the query and get this part working?
>
> Mike
>
> actual query:
>
> SELECT bugs.bug_id, bugs.groupset, substring(bugs.bug_severity, 1, 3),
> substring(bugs.priority, 1, 3), substring(bugs.rep_platform, 1, 3),
> map_assigned_to.login_name, substring(bugs.bug_status,1,4),
> substring(bugs.resolution,1,4), substring(bugs.short_desc, 1, 60)
> FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT JOIN
> profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid
> WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.reporter
>= map_reporter.userid AND bugs.groupset & 0 = bugs.groupset GROUP
> BY bugs.bug_id;

Reading something else I modified the perl script to put everything
between FROM and LEFT JOIN in parenthesis. The code (at work) looks
something like this:

if($query =~ /^(.*\s+FROM)\s+(.*)\s+(LEFT JOIN\s+.*)\s*$/oi) {
$query = $1 . ' ( ' . $2 . ' ) ' $3;
}

This seems to work for now. I'll try to upgrade bugzilla soon.

Mike

Re: updating old query?

Mike wrote:
> FROM bugs, profiles map_assigned_to, profiles map_reporter LEFT JOIN
> profiles map_qa_contact ON

The problem is actually the way they mix SQL89 "comma-style" joins and
SQL92 "JOIN" syntax.

See http://dev.mysql.com/doc/refman/5.0/en/join.html, under the heading
"Join Processing Changes in MySQL 5.0.12".

The solution is to rewrite comma-style joins into SQL92 syntax:

FROM a, b LEFT JOIN c ON
WHERE a.x = b.y

Can be rewritten as:

FROM a
JOIN b ON a.x = b.y
LEFT JOIN c ON

As you might expect, the most recent release of Bugzilla has already
fixed all such cases. I read it in the bug logs of the Bugzilla
product, some time back when someone had the same issue that you are
having now.

Regards,
Bill K.