253594 - keyword bug counts are not accurate

Status: NEW

(Bugzilla :: Bugzilla-General, defect, P5)

Description

[Jon]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 Galeon/1.3.8
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 Galeon/1.3.8

The bug counts listed on the describekeywords page for each keyword don't match
the total shown in the buglist if you follow the bugcount link.  It appears to
show the real total count of bugs using that keyword, but if you don't have
access to all of those bugs then the total won't match the buglist total.

Reproducible: Always
Steps to Reproduce:
1.add a keyword to a restricted bug
2.look at the describekeywords.cgi page from an account that doesn't have access
to all bugs with keywords
3.

Actual Results:  
bugcount column shows total bugs using a keyword including bugs you don't have
access to

Expected Results:  
only show the bug count total including the bugs the user has access to

Comment 1

[Joel Peshkin]

This needs the customary criteria added to its query

Comment 2

[Joel Peshkin]

Attachment: patch uses bug visibility logic

This has had a rudimentary test.  Please try it and confirm that it returns the
right number.

Comment 3

[Jon]

It fixes it for the test case that I had.

Comment 4

[Stephen Lee]

The old code did a single SQL query, getting the bug counts almost "for free".

The code in the patch appears to perform a separate query for each individual 
keyword, which seems a HUGE performance hit for a feature that the user may not 
be interested in. It looks like the template used by bugzilla.mozilla.org does 
not even show the bug counts, so these extra queries would be completely wasted.


Also, consider the equivalent operation on "editkeywords.cgi"... it should 
presumably have consistent bug counts to describekeywords.cgi, and it is at 
least in principle possible for someone to have editkeywords privilege without 
having access to all security and/or product groups... messages such as the 
following could be confusing or misleading:
There are 0 bugs which have this keyword set. Are you sure you want to delete...

Comment 5

[Dave Miller [:justdave]]

Comment on attachment 154712 [details] [diff] [review]
patch uses bug visibility logic

OK, I like the idea of still pulling it in the same query.  So I experimented
with the SQL, and ran some tests using b.m.o's data and a user who was not a
member of the security group.

Here's a query that will pull the whole thing in one shot, and it doesn't even
take very long to run. :)

SELECT keyworddefs.name,
       keyworddefs.description,
       COUNT(keywords.bug_id)
FROM keyworddefs
     LEFT JOIN keywords ON keyworddefs.id = keywords.keywordid
     INNER JOIN bugs ON keywords.bug_id = bugs.bug_id
     LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id
	     AND bug_group_map.group_id NOT IN ($grouplist)
     LEFT JOIN cc ON bugs.bug_id = cc.bug_id AND cc.who = $uid
WHERE (bug_group_map.group_id IS NULL)
      OR (bugs.assigned_to = $uid)
      OR ( (bugs.reporter = $uid)
	   AND (bugs.reporter_accessible = 1) )
      OR ( (cc.who IS NOT NULL)
	   AND (bugs.cclist_accessible = 1) )
      OR (bugs.qa_contact = $uidqa)
GROUP BY keyworddefs.id
ORDER BY keyworddefs.name


163 rows in set (5.09 sec)

Spot checks on a couple keywords that commonly show up on security bugs shows
that the results are accurate, too.

Comment 6

[Dave Miller [:justdave]]

Oops, that misses keywords that aren't on any bugs.  Try this one instead:

SELECT keyworddefs.name,
       keyworddefs.description,
       COUNT(keywords.bug_id)
FROM keyworddefs
     LEFT JOIN keywords ON keyworddefs.id = keywords.keywordid
     LEFT JOIN bugs ON keywords.bug_id = bugs.bug_id
     LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id
             AND bug_group_map.group_id NOT IN ($grouplist)
     LEFT JOIN cc ON bugs.bug_id = cc.bug_id AND cc.who = $uid
WHERE (bug_group_map.group_id IS NULL)
      OR (bugs.assigned_to = $uid)
      OR ( (bugs.reporter = $uid)
           AND (bugs.reporter_accessible = 1) )
      OR ( (cc.who IS NOT NULL)
           AND (bugs.cclist_accessible = 1) )
      OR (bugs.qa_contact = $uidqa)
GROUP BY keyworddefs.id
ORDER BY keyworddefs.name
;


177 rows in set (5.64 sec)

Comment 7

[Dave Miller [:justdave]]

I'll take this for 2.18 if someone finishes it up, but I won't block the release
on it.

Comment 8

[Dave Miller [:justdave]]

Reassigning bugs that I'm not actively working on to the default component owner
in order to try to make some sanity out of my personal buglist.  This doesn't
mean the bug isn't being dealt with, just that I'm not the one doing it.  If you
are dealing with this bug, please assign it to yourself.

Comment 9

[Max Kanat-Alexander]

OK, I don't think this bug is significant enough for a branch checkin, and I
don't think that we'll get to it before we release 2.20.