63051 - Boolean chart changed by additions

Status: RESOLVED DUPLICATE of bug 38487

(Bugzilla :: Query/Bug List, enhancement)

Description

[Asa Dotzler [:asa]]

Summary of RFE
The boolean chart has a "changed by" field that requires an exact match with a
valid username.  It would be useful (to me at least) to be able to do "changed
by  (case-insensitive) substring" and " "not changed by (case-insensitive)
substring"

This option would allow changed by queries against partial usernames like we
have in the Email fields, making the feature much more user friendly as well as
making possible "organizational" queries like for example "show bugs which have
keyword "blah" added by someone @mozilla.org" or "show bugs which were confirmed
by someone not @netscape.com" or "show bugs where priority was changed by
someone at @eng.sun.com".

I'm pretty sure that this is not available with the current set of tools but I'd
love for someone to show me otherwise.  I have no idea how difficult this would
be to add but I think it would be extremely helpful for the Mozilla project in
particular since we have many different organizations contributing. For others
it would make the tool more user friendly.

Comment 1

[Jesse Ruderman]

One way to fix this would be to fix bug 38487.

Comment 2

[Andreas Franke (gone)]

-> Bugzilla product, Query component, reassigning.

Comment 3

[Asa Dotzler [:asa]]

I was probably asking for too much here. Making my request a little bit simpler.

I can probably get most of what I need if the changed by value accepted a
substring by default, or even better, regexp. If it accepted regexps it would be
the most flexible since that allows for a fugly changed by "not" something . 
So, for example, I could do horribly hackish things to get my organizational
queries like everconfirmed 'changed by'
"([^n]...........$)|(.[^e]..........$)|(..[^t].........$)|(...[^s]........$)etc."
to get the "changed by not netscape.com accounts"  I've tested this kind of
regexp negative in the simple email match fields at the top of query.cgi and it
seems to work just fine, if a bit slow. 

If regexp is too difficult and substring isn't then I can live with that too.
For the organizational queries I think I could do a something like this:
everconfirmed changed by substring "netscape.com" and subtract those results
from all bugs that have everconfirmed changed by "." or something like that.

If anyone knows how I can do this in the current world please let me know. 

Comment 4

[Matthias Versen [:Matti]]

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

Comment 5

[Christian Reis]

Do this tomorrow, you procrastinator. Asa needs fixes and you just think of work.

Comment 6

[Christian Reis]

Asa, I'm looking into it. I now have a problem and request help from the ninjas
above. I'll place my questions here.

a) Doing a regexp is non-portable _unless_ we resort to manually going through
all users and doing a perl regexp. Yes, this is awful, but it will complicate us
if we plan on changing from MySQL ever. So I need feedback on this - if the
ninjas say regexps are okay, it's simple to do. Otherwise, substrings.

b) This is the real tricky one. Say we do a regexp. Right now, the part where we
handle changedby looks like:

         "^long_?desc,changedby" => sub {
             my $table = "longdescs_$chartid";
             push(@supptables, "longdescs $table");
             push(@wherepart, "$table.bug_id = bugs.bug_id");
             my $id = DBNameToIdAndCheck($v);
             $term = "$table.who = $id";
         },

Please note the $table.who = $id part. This means we can have one id for this
query. Now the standard way to do this would be to perform a subselect on a
function that returned the list of IDs. However, MySQL doesn't do subselects. So
is adding a list of "( id=foo OR id=bar )" statements okay?

Comment 7

[Christian Reis]

Attachment: kiko_v1: does what Asa wants, but not in a way Jake or justdave will like :P (lacks comments in function, will do on next roll)

Comment 8

[Christian Reis]

Hi

The attached patch does something. Actually, it does what Asa requested using
substrings (I use a LIKE '%foo%' clause). I'm adding a series of "OR id =
ids[0]" type of clauses to get them all. Since I'm not the schema expert it's
hard for me to say if I'm doing the right thing or not.

DBsubstr_to_id_list does what the name implies; I've added it to globals.pl and
I would like review on the design intention.

Well, that's it for now. Asa, if you would like to see this working, try

http://bugs.async.com.br/query.cgi

And enter changed by "async". It will find [e-mail] and
[e-mail] and add the bugs they have changed to the buglist.

Comment 9

[Jacob Steenhagen]

Attachment: jake_v1: Only works on activity log

Comment 10

[Jacob Steenhagen]

This patch needs to be expanded to work w/things such as long descriptions that
aren't stored in the bugs_activity table.

Comment 11

[Andreas Franke (gone)]

mysql supports "IN": where id in (1793,1937,2720,2724,3417) ;

Comment 12

[Christian Reis]

I seriously believe offering _another_ boolean option there is overkill.
Asa himself suggested it wasn't essential, and IMHO the fact that some
functionality of the bar has _never been tested_ (see bug 
103568 f.e.) shows me that we need to reduce them to stuff that is
usable before expanding.  Maybe I'm naive 8)

Is using non-standard SQL okay in Bugzilla? REGEXP sure is MySQL
proprietary...

Anyway, just let me know what your opinion is.

I see know the $ptable.name clause (nicer), but what is this
"activity log" comment?

Comment 13

[Jacob Steenhagen]

I know there are many cases where REGEXP is used currently in the SQL, esp. in
buglist.  I don't know if there's a cross DB way to do it, do you?

Having options for both exact and regexp could be quite helpful as exact runs a
lot faster.

The "activity log" comment is stating that this only works for changes that are
stored in the "bugs_activity" table. If click on the "View Bug Activity" link in
this (or any other bug) you can see what's recorded there).  Any change that
does not appear on that page, cannot be queried via the "Changed by (rexexp)"
option I added.  One example I can think of off the top of my head is additional
comments.

I don't think past history RE testing of the boolean charts should prevent
adding new options when that are/can be helpful.

Comment 14

[Christian Reis]

> I know there are many cases where REGEXP is used currently in the SQL, esp. in
> buglist.  I don't know if there's a cross DB way to do it, do you?

Well, the way I did it avoided x-SQL problems but you didn't like it :P I don't
think we can get regexp matching cross-db, no.

> Having options for both exact and regexp could be quite helpful as exact runs a
> lot faster.

Somehow I feel very uneasy about adding yet _another_ option to that list; I
don't think it's very used at all beyond advanced users like Asa (ping, Asa?).
But if you think it's the best, okay.

About activity table:
won't this give us a lot of false positives? I see in the report at least that
the bug creator is by default marked on all bugs - is that the point with changedby?

Did my patch not work as perfectly as I assumed? The approach might not be too
nice (the expansion of OR statements sucks) but it was x-sql and it did seem to
work well. Let me know. Want to go on working on this, split out another bug for
the non-activity features or leave me to ramble on alone? :)

Comment 15

[Matthew Tuck [:CodeMachine]]

If REGEXP is only used in charts, we can turn off that feature for DBs that
don't support it until we implement emulation.

But I think SQL regexps are right throughout the code.  Do we know for sure if
they're standard?

Comment 16

[Christian Reis]

MattyT: regexp syntax is not SQL standard: i.e. in postgreSQL it's =~,
in MySQL it's REGEXP and in Oracle you need a special package.

Comment 17

[Matthew Tuck [:CodeMachine]]

OK, well just use the MySQL stuff then.  I imagine dkl will come along and whack
a sub around all call sites at some stage and we might want to add a test then.

Comment 18

[Jacob Steenhagen]

Still in discussion (removing patch and review keywords).  Asa, any comment?

Comment 19

[Asa Dotzler [:asa]]

While I'd love to be able to do a few queries for changed by regexp (or
substring), I don't think that confusing the chart or adding options that won't
work is the right way to go. Sorry if I've caused people to waste time. This is
probably best addressed by splitting out the changed by options into their own
select (redesign) and there's another bug reported for that I think. 

I don't understand all the details here but if it's possible to just tell it to
do a substing match instead of an exact match on 'changed by' and if that worked
with all of the items in the first select then that might be a solution (it
would give me 95% of what I want from a full redesign that splits the action and
match type into to selects). 

Comment 20

[Max Kanat-Alexander]

This should be fixed as bug 38487 instead, which is the generic way to handle this.