469,160 Members | 1,832 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

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;
Aug 16 '06 #1
2 2723
On 2006-08-16, Mike <mi***@mikee.ath.cxwrote:
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
Aug 16 '06 #2
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 <expr>
WHERE a.x = b.y

Can be rewritten as:

FROM a
JOIN b ON a.x = b.y
LEFT JOIN c ON <expr>

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.
Aug 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Jason | last post: by
reply views Thread by Bob Kaku | last post: by
1 post views Thread by Derek Davlut | last post: by
1 post views Thread by Chris Jackson | last post: by
2 posts views Thread by Ray Holtz | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.