77764 - Allowing to do bug counts from the query page

Status: RESOLVED WONTFIX

(Bugzilla :: Query/Bug List, enhancement, P3)

Description

[Ludovic Dubost]

I'm submitting a modification to bugzilla that allow to run bug counts from the
query page.. It might not be the nicest code but it works..

The feature is:

- add a nbbugs column that you can pick from the "Change Column" link
- with this columns Bugzilla will count bugs grouped by all the other columns
choosen

When this is active, the number of bugs in the page is not shown (because it
would be the number of group by's)..

To be able to do this I had to add the 'id' column in the list of columns
because if you want to count you need not to have the id column... otherwise the
result will always be one per line..

The interesting thing is that you can create your reports that count bugs by
products, components, priority etc etc...

An additional feature could be to allow the admin or the user to specify which
of there queries should go in the reports page for you and/or for all..

Patched file is attaches:

globals.pl (default_column_list change)
colchange.cgi (just adding id and nbbugs)
buglist.cgi (changing the way the query is generated and the report shown)

Comment 1

[Ludovic Dubost]

Attachment: Patch file for the three modifed files

Comment 2

[Ludovic Dubost]

Oops... I messed up the ordering

This change should work

DefCol("nbbugs", "count(distinct bugs.bug_id) nbbugs", "Nb of Bugs", "nbbugs
desc", 5);     

Comment 3

[Ludovic Dubost]

Ordering still causes problems.. The columns needs to be there if you used
ordering by number of bugs once.. If you loose the column and the ordering stays
in memory then it fails..

There is a need to filter the ordering for removing the nbbugs columns if it is
still there...

Comment 4

[Matthew Tuck [:CodeMachine]]

Consider and review for 2.16.  This may be the wrong way to go about things
given bug #12282 would be better.  Might confuse if we did this and then
implemented that.

Comment 5

[Christopher Aillon (sabbatical, not receiving bugmail)]

Mass moving to new product Bugzilla...

Comment 6

[Dave Miller [:justdave]]

Attachment: A simpler way to get a bug count

Comment 7

[Dave Miller [:justdave]]

I'm having trouble visualizing what the first attachment here is doing.  What I 
just did here in the second attachment may be totally off base from what you were 
looking for, but the subject of the bug seemed to fit.  If we can clarify here 
what the first attachment does maybe I'm better off moving the second one to 
another bug...

The second attachment adds a "Count of matching bugs" button to query.cgi, and if 
that's pressed, all you get is the total matches ("15 bugs found.") instead of 
the list of bugs.  Besides people who may be curious themselves, the irc bots can 
use "&countonly=1" on the end of their queries and it'll greatly speed up the 
parsing since they won't have to download the whole list if it's big.

Comment 8

[Ludovic Dubost]

It is quite different from what my attachment is doing... What my patch is doing
is allow to count the number of bugs py products, component or priorities or any
other fields used in the request...

The technique for this is to include a 'count(distinct bug_id)' and a 'group by'
for all the fields in the SQL request...

The interesting usage of this feature is that it allows managers to do bug
counts by user, by products, by priorities etc etc... and save these requests in
the footer for running them later...

I really think somebody from bugzilla should look at this patch and include it
in the standard distribution... it is worthwhile...

Comment 9

[Andreas Franke (gone)]

See also bug 92676 "Need summary table of bug counts". Maybe someone can
elaborate on the similarities and differences?

Comment 10

[Andreas Franke (gone)]

Dave, your patch should probably go to bug 9789. The difference between your
patch and my bugcount.cgi script there is that I've modified the query to just
count(...) the bugs, abandon the GROUP BY and ORDER clauses, so that mysql
returns just the bug count, not the actual bug data. Also it suppresses the
header and footer, and always returns just a decimal number, not "Zarro boogs
found" or "One bug found".

Comment 11

[Andreas Franke (gone)]

This is related to reporting, cc'ing Gerv.

Comment 12

[Gervase Markham [:gerv]]

This is not related to reporting! It's related to querying. Although I have
views on how this should be done, expressed over in bug
I-cant-remember-the-number-because-there-are-too-many-like-this , it's nothing
to do with me :-)

Gerv

Comment 13

[Andreas Franke (gone)]

Ok, if you say so... :-)

Comment 14

[Andreas Franke (gone)]

Ludovic, can you perhaps attach a sample buglist-generated html page that shows
this feature?

Comment 15

[Andreas Franke (gone)]

Attachment: patch for current cvs, part 1: globals.pl and colchange.cgi

Comment 16

[Andreas Franke (gone)]

Attachment: part 2: buglist.cgi (diff -u)

Comment 17

[Andreas Franke (gone)]

Comment on attachment 48822 [details] [diff] [review]
part 2: buglist.cgi (diff -u)

I could successfully apply this patch to my local roughly-2.14 installation.
However, it gave me a lot of software errors, complaining about 
	SELECT substring(...)
or
	SELECT count(distinct something)

Maybe I introduced some bugs when I updated the patch
by hand to fit the current CVS,
but it doesn't seem to work out
of the box.

Comment 18

[Ludovic Dubost]

Attachment: Sample Output of bug counts by priority

Comment 19

[Ludovic Dubost]

I've attached a sample output of bug counts by priority...
but you could count by any of the fields in the Database.. for example bug
counts by products or components...

My patch was on a version 2.12 I think... can you show the SQL that is generated
and the exact problem that it complains about ?

There must be something wrong in the SQL generation... The sql generated by my
installation for the bug count I've attached is:

SELECT substring(bugs.priority, 1, 3), count(distinct bugs.bug_id) nbbugs 
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 & 9223372036850448383 = bugs.groupset 
GROUP BY substring(bugs.priority, 1, 3) ORDER BY nbbugs desc, bugs.bug_id

Comment 20

[Andreas Franke (gone)]

A "normal" query for open bugs in a certain product fails:

http://bugzilla.mathweb.org:8000/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&field0-0-0=product&type0-0-0=substring&value0-0-0=mbase

SELECT 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, 15),
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 AND (bugs.bug_status = 'UNCONFIRMED' OR
bugs.bug_status = 'NEW' OR
bugs.bug_status = 'ASSIGNED' OR bugs.bug_status = 'REOPENED') AND
(INSTR(LOWER(bugs.product), 'mbase')) GROUP
BY 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, 15),
substring(bugs.short_desc, 1, 60) ORDER BY
bugs.product, bugs.component, bugs.bug_status, map_assigned_to.login_name,
bugs.priority, bugs.bug_severity:
You have an error in your SQL syntax near 'substring(bugs.bug_severity, 1, 3),
substring(bugs.priority, 1,
3), substring(bu' at line 1 at globals.pl line 214. 

If I manually add &columnlist=id at the end, it works (and shows just the ID
column; I have the patch from bug 12284 in my tree).

If I add &columnlist=priority+nbbugs , then I again get a software error:

SELECT substring(bugs.priority, 1, 3), count(distinct bugs.bug_id) 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 AND (bugs.bug_status = 'UNCONFIRMED' OR bugs.bug_status = 'NEW' OR
bugs.bug_status = 'ASSIGNED'
OR bugs.bug_status = 'REOPENED') AND (INSTR(LOWER(bugs.product), 'mbase')) GROUP
BY substring(bugs.priority,
1, 3) ORDER BY bugs.product, bugs.component, bugs.bug_status,
map_assigned_to.login_name, bugs.priority,
bugs.bug_severity: You have an error in your SQL syntax near 'distinct
bugs.bug_id) FROM bugs, profiles
map_assigned_to, profiles map_reporter' at line 1 at globals.pl line 214. 

Any ideas?

To me, the sample page looks ok. What happens if there is more than one column
besides nbbugs?

Comment 21

[Gervase Markham [:gerv]]

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

Comment 22

[Gervase Markham [:gerv]]

The plan in this area is to templatise query.cgi, then get generic reporting
working using most of the UI. The functions there will be a superset of what
this patch does.

Gerv

Comment 23

[Dave Miller [:justdave]]

We are currently trying to wrap up Bugzilla 2.16.  We are now close enough to
release time that anything that wasn't already ranked at P1 isn't going to make
the cut.  Thus this is being retargetted at 2.18.  If you strongly disagree with
this retargetting, please comment, however, be aware that we only have about 2
weeks left to review and test anything at this point, and we intend to devote
this time to the remaining bugs that were designated as release blockers.

Comment 24

[Jesse Ruderman]

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

Comment 25

[Joel Peshkin]

Assuming that nobody screams, I am marking this as a duplicate of bug 12282.

If bug 12282 does not cover what is needed here, please reopen and comment with
specifically what else is needed.



*** This bug has been marked as a duplicate of 12282 ***

Comment 26

[Dave Miller [:justdave]]

removing target milestone from WONTFIX/INVALID/WORKSFORME/DUPLICATE bugs so
they'll show up as untriaged if they get reopened.

Comment 27

[Ludovic Dubost]

yes i'de like to reopen the bug. While the features implemented in 12282
implement the bug counting the fact that it is separate does not allow to save
the query n the footer.. It looked to me like a nice feature to be able to do
this because the reports take often a lot of time to rightly design..

Especially with the notion of 'global queries' this allows to save reports to be
shared..

Let me know what you think..

Comment 28

[Gervase Markham [:gerv]]

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

Comment 29

[Gervase Markham [:gerv]]

You can now do bug counts using the Reporting mechanism, as long as there's some
field where you've picked a single value.

Example:

http://bugzilla.mozilla.org/report.cgi?x_axis_field=&ctype=html&y_axis_field=product&z_axis_field=&query_format=report-table&short_desc_type=allwordssubstr&short_desc=&product=Browser&long_desc_type=substring&long_desc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&status_whiteboard_type=allwordssubstr&status_whiteboard=&keywords_type=allwords&keywords=&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&emailassigned_to1=1&emailtype1=exact&email1=&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailtype2=exact&email2=&bugidtype=include&bug_id=&votes=&changedin=&chfieldfrom=&chfieldto=Now&chfieldvalue=&format=table&action=wrap&field0-0-0=noop&type0-0-0=noop&value0-0-0=

This tells me how many open bugs there are in the Browser product. Underneath,
it does the same single SQL query that any "count" enhancement to the search
page would do.

Gerv

Comment 30

[Andreas Kunz]

But report.cgi wants users to be logged in, while the default query can be made
by anybody.
I'm not sure why, and if this can be changed - the information available there
seems to be also available without registration...

Comment 31

[Gervase Markham [:gerv]]

I seem to remember that report.cgi wants people to be logged in because it's
much easier to put heavy load on the server using report.cgi than query.cgi,
because one report can be lots and lots of queries.

I'm not saying that report.cgi is an ideal replacement for a "count" function on
queries - I'm just saying that the function is there.

Gerv

Comment 32

[Andreas Kunz]

The CPU load might be more limited with query.cgi, bandwith not.
I'm not sure what is considered more unpleasant: potentially higher CPU cycles
or a 10-20 Megabyte download each time somebody wants to know how many open bugs
there are...

Comment 33

[alanjstr]

Does Reports still require a user to be logged in?

Comment 34

[Max Kanat-Alexander]

I think that report.cgi adequately provides this functionality.