99716 - cannot query for Attachment is obsolete AND Attachment Status

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Asa Dotzler [:asa]]

reproduced on b.m.o (2.15) on 2001-09-14 at 18:45 PDT

Description: 
A query in the boolean tool (advanced search) for Attachment is obsolete any
value AND Attachment Status any value results in a Software error. 

example:
http://bugzilla.mozilla.org/buglist.cgi?field0-0-0=attachments.isobsolete&type0-0-0=equals&value0-0-0=1&field0-1-0=attachstatusdefs.name&type0-1-0=equals&value0-1-0=has-review

results:
Content-type: text/html
Software error:
SELECT bugs.bug_id, bugs.groupset, substring(bugs.bug_severity, 1, 3),
map_assigned_to.login_name, substring(bugs.bug_status,1,4),
substring(bugs.resolution,1,4), substring(bugs.component, 1, 8),
substring(bugs.op_sys, 1, 4), 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 attachments attachments_0 ON
bugs.bug_id = attachments_0.bug_id, attachments attachments_0, attachstatuses
attachstatuses_0, attachstatusdefs attachstatusdefs_0 WHERE bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND bugs.groupset
& 3583 = bugs.groupset AND bugs.bug_id = attachments_0.bug_id AND
attachments_0.attach_id = attachstatuses_0.attach_id AND
attachstatuses_0.statusid = attachstatusdefs_0.id AND (attachments_0.isobsolete
= '1') AND (attachstatusdefs_0.name = 'has-review') GROUP BY bugs.bug_id ORDER
BY bugs.resolution, bugs.bug_id: Not unique table/alias: 'attachments_0' at
globals.pl line 214.

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

Comment 1

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v1: fixes this bug along with bug 97947

Comment 2

[Myk Melez [:myk] [@mykmelez]]

The patch I just attached fixes this problem and also fixes the problem in bug
97947 where you cannot search for bugs with attachments whose statuses match
multiple conditions.

The only problem is that this fix makes searches on the "obsolete" flag (and
presumably other attachment fields) take twice as long.  Apparently this happens
when I change the outer join between the bugs and attachments table to an inner
join.  Theoretically the inner join should be faster (in fact it is a lot faster
when searching on attachment status), and the results of EXPLAIN seem to support
the inner join being faster:

-----------------------------------------------------

mysql> EXPLAIN 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.component, 1, 8),
substring(bugs.product, 1, 8), 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, attachments
attachments_0 WHERE bugs.bug_id = attachments_0.bug_id AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND bugs.groupset
& 3327 = bugs.groupset AND (bugs.bug_status = 'NEW' OR bugs.bug_status =
'ASSIGNED' OR bugs.bug_status = 'REOPENED') AND (attachments_0.isobsolete = '1')
GROUP BY bugs.bug_id ORDER BY bugs.bug_id;
+-----------------+--------+-----------------------------------------+---------+---------+----------------------+-------+-------------+
| table           | type   | possible_keys                           | key     |
key_len | ref                  | rows  | Extra       |
+-----------------+--------+-----------------------------------------+---------+---------+----------------------+-------+-------------+
| attachments_0   | ALL    | bug_id                                  | NULL    |
   NULL | NULL                 | 49417 | where used  |
| bugs            | eq_ref | PRIMARY,assigned_to,bug_status,reporter | PRIMARY |
      3 | attachments_0.bug_id |     1 | where used  |
| map_reporter    | eq_ref | PRIMARY                                 | PRIMARY |
      3 | bugs.reporter        |     1 | Using index |
| map_qa_contact  | eq_ref | PRIMARY                                 | PRIMARY |
      3 | bugs.qa_contact      |     1 | Using index |
| map_assigned_to | eq_ref | PRIMARY                                 | PRIMARY |
      3 | bugs.assigned_to     |     1 |             |
+-----------------+--------+-----------------------------------------+---------+---------+----------------------+-------+-------------+
5 rows in set (0.10 sec)

mysql> EXPLAIN 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.component, 1, 8),
substring(bugs.product, 1, 8), 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 LEFT JOIN attachments
attachments_0 ON bugs.bug_id = attachments_0.bug_id WHERE bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND bugs.groupset
& 3327 = bugs.groupset AND (bugs.bug_status = 'NEW' OR bugs.bug_status =
'ASSIGNED' OR bugs.bug_status = 'REOPENED') AND (attachments_0.isobsolete = '1')
GROUP BY bugs.bug_id ORDER BY bugs.bug_id;
+-----------------+--------+---------------------------------+---------+---------+------------------+-------+-------------+
| table           | type   | possible_keys                   | key     | key_len
| ref              | rows  | Extra       |
+-----------------+--------+---------------------------------+---------+---------+------------------+-------+-------------+
| bugs            | ALL    | assigned_to,bug_status,reporter | NULL    |    NULL
| NULL             | 99554 | where used  |
| map_assigned_to | eq_ref | PRIMARY                         | PRIMARY |       3
| bugs.assigned_to |     1 |             |
| map_reporter    | eq_ref | PRIMARY                         | PRIMARY |       3
| bugs.reporter    |     1 | Using index |
| map_qa_contact  | eq_ref | PRIMARY                         | PRIMARY |       3
| bugs.qa_contact  |     1 | Using index |
| attachments_0   | ref    | bug_id                          | bug_id  |       3
| bugs.bug_id      |    13 | where used  |
+-----------------+--------+---------------------------------+---------+---------+------------------+-------+-------------+
5 rows in set (0.00 sec)
-----------------------------------------------------

But then I run the queries and get these sample results:

inner join: 84 rows in set (41.10 sec)
outer join: 84 rows in set (27.50 sec)
On multiple tests the inner join is consistently in the 40-50 second range,
while the outer join is consistently in the 20-30 second range.  Is it possible
that this is because the attachments table is so much bigger than the bugs table?

Comment 3

[Myk Melez [:myk] [@mykmelez]]

In any case, my patch fixes this software error and enables something to work
that didn't work before, plus the speed is not unbearable even though it is
slower than we want, so I recommend checking it in and then filing another bug
on improving the performance of attachment queries.

Comment 4

[Jacob Steenhagen]

Comment on attachment 49422 [details] [diff] [review]
patch v1: fixes this bug along with bug 97947

It does prevent the sotware error (this bug :)  I didn't check to see if it solves bug 97947.
r=jake
Recommend a second review (specifically testing that bug 97497 is also solved)

Comment 5

[Jacob Steenhagen]

*** Bug 103344 has been marked as a duplicate of this bug. ***

Comment 6

[Asa Dotzler [:asa]]

*** Bug 97947 has been marked as a duplicate of this bug. ***

Comment 7

[Gervase Markham [:gerv]]

I say we check it in. If it doesn't fix the other bug, we'll reopen it and think
again :-)

Gerv

Comment 8

[Dave Miller [:justdave]]

Comment on attachment 49422 [details] [diff] [review]
patch v1: fixes this bug along with bug 97947

r= justdave

Comment 9

[Gervase Markham [:gerv]]

/cvsroot/mozilla/webtools/bugzilla/buglist.cgi,v  <--  buglist.cgi
new revision: 1.150; previous revision: 1.149

Gerv

Comment 10

[Dave Miller [:justdave]]

Fixed for 2.16

Comment 11

[Dave Miller [:justdave]]

no, really this time :)