177004 - pull the fielddefs constraint out of (bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED')) AND (bugs.resolution = '---' OR (fielddefs_0.name = 'resolution' AND act_0.bug_when > '10/13/2002'))

Status: RESOLVED WORKSFORME

(Bugzilla :: Query/Bug List, defect)

Description

[timeless]

Please stand by ...
Content-type: text/html
Software error:
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,
bugs_activity act_0, fielddefs fielddefs_0 LEFT JOIN cc selectVisible_cc ON
bugs.bug_id = selectVisible_cc.bug_id AND selectVisible_cc.who = 15223 WHERE
((bugs.groupset & 730) = bugs.groupset OR (bugs.reporter_accessible = 1 AND
bugs.reporter = 15223) OR (bugs.cclist_accessible = 1 AND selectVisible_cc.who =
15223 AND not isnull(selectVisible_cc.who)) OR (bugs.assigned_to = 15223) OR
(bugs.qa_contact = 15223)) AND bugs.assigned_to = map_assigned_to.userid AND
bugs.reporter = map_reporter.userid AND act_0.bug_id = bugs.bug_id AND
act_0.fieldid = fielddefs_0.fieldid AND (bugs.bug_status = 'UNCONFIRMED' OR
bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR bugs.bug_status =
'REOPENED' OR bugs.bug_status = 'RESOLVED') AND (bugs.resolution = '---' OR
(fielddefs_0.name = 'resolution' AND act_0.bug_when > '10/13/2002')) ORDER BY
bugs.bug_id : Server shutdown in progress at globals.pl line 276.

For help, please send mail to the webmaster ([e-mail]), giving this
error message and the time and date of the error.

Comment 1

[Dave Miller [:justdave]]

This works on bugzilla-tip.  It doesn't on b.m.o.  So it's apparently already
been fixed.  I'm betting the changes to SelectVisible having to do with the new
group restrictions probably have a lot to do with it.

Comment 2

[timeless]

http://bugzilla.mozilla.org/buglist.cgi?&bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=RESOLVED&field0-0-0=resolution&type0-0-0=equals&value0-0-0=---&field0-0-1=resolution&type0-0-1=changedafter&value0-0-1=10%2F13%2F2002


Please stand by ...
Content-type: text/html
Software error:

SELECT bugs.bug_id, bugs.bug_severity, map_assigned_to.login_name,
bugs.bug_status, bugs.resolution, map_products.name, map_components.name,
map_qa_contact.login_name, bugs.short_desc FROM bugs, profiles AS
map_assigned_to LEFT JOIN profiles map_qa_contact ON bugs.qa_contact =
map_qa_contact.userid, products AS map_products, components AS map_components,
bugs_activity act_0, fielddefs fielddefs_0 LEFT JOIN bug_group_map  ON
bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id NOT IN
(2,4,7,9,10,12)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 15223 
WHERE bugs.assigned_to = map_assigned_to.userid AND bugs.product_id =
map_products.id AND bugs.component_id = map_components.id AND act_0.bug_id =
bugs.bug_id AND act_0.fieldid = fielddefs_0.fieldid AND (bugs.bug_status IN
('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED')) AND (bugs.resolution =
'---' OR (fielddefs_0.name = 'resolution' AND act_0.bug_when > '10/13/2002'))
AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND
bugs.reporter = 15223)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT
NULL)     OR (bugs.assigned_to = 15223) OR (bugs.qa_contact = 15223) ) GROUP BY
bugs.bug_id ORDER BY bugs.bug_id : Server shutdown in progress at globals.pl
line 242.

For help, please send mail to the webmaster ([e-mail]), giving this
error message and the time and date of the error. 

Comment 3

[Bradley Baetz (:bbaetz)]

(bugs.bug_status IN
('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED')) AND (bugs.resolution =
'---' OR (fielddefs_0.name = 'resolution' AND act_0.bug_when > '10/13/2002'))

That looks ugly, since you'll be pulling a row out for every change ever made on
any UNCO, NEW, ASSI, REOP, or RESO bug, since fielddefs_0.name isn't restricted
in teh first part of the constraint

We need to pull the fielddefs constraint out, which should still be cheap, but
we need to make sure that we don't break |'changed to' AND 'changed by'| style
queries. I think that those will still work if we keep the bug activity table
per-chart, but then we have issues with |'resolution' changed* OR 'status'
changed*|. Hmmmm. Ideas?

Comment 4

[timeless]

ok, the query worked (it hurt mozilla's little mind but that's because 14days
became something much bigger)

Comment 5

[Teemu Mannermaa (:wicked)]

... LEFT JOIN bugs_activity AS act_11_0 ON bugs.bug_id = act_11_0.bug_id AND act_11_0.fieldid = 11 AND act_11_0.bug_when >= '2002-10-13 00:00:00' WHERE ... AND bugs.bug_status IN ('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED') AND (bugs.resolution = '---' OR act_11_0.bug_when IS NOT NULL) ...

This is how this works on tip now so seems to me this is no longer a problem.

... LIMIT 500

And we also have this in the query to make sure gigantic queries will at least finish. :)