30823 - Advanced query conditions on dependencies and CCs are ignored.

Status: RESOLVED FIXED

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

Description

[Matthew Tuck [:CodeMachine]]

Bah.

Just my luck.

The first time I use Bugzilla advanced querying and I broke it.


SELECT bugs.bug_id, bugs.groupset, date_format(bugs.delta_ts,'Y-m-d'),
map_reporter.login_name,
substring(bugs.bug_status,1,4), substring(bugs.resolution,1,4),
substring(bugs.component, 1, 8),
substring(bugs.product, 1, 8), bugs.votes, bugs.target_milestone,
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 & 192 = bugs.groupset AND (INSTR(blocked,
'18054')) GROUP BY
bugs.bug_id ORDER BY bugs.delta_ts, bugs.priority, bugs.bug_severity: Unknown
column 'blocked' in 'where
clause' at globals.pl line 135.

Comment 1

[Terry Weissman]

Yep, sure 'nuff.  I think all advanced queries related to dependencies are
busted.  Whoops!

Comment 2

[Terry Weissman]

[e-mail] is the new owner of Bugzilla and Bonsai.  (For details,
see my posting in netscape.public.mozilla.webtools,
news://news.mozilla.org/38F5D90D.F40E8C1A%40geocast.com .)

Comment 3

[Tara Hernandez]

Go Don.

Comment 4

[Don McCasland]

fix made to buglist.cgi that does the right thing for dependancies.dependson and
dependancies.blocked.  Man that's hairy stuff.

Comment 5

[Matthew Tuck [:CodeMachine]]

The URL above now seems to return every bug in Bugzilla.

Comment 6

[Matthew Tuck [:CodeMachine]]

Reopening because as far as I can see, this just isn't working.  Rather than the
software error, the condition is now ignored, so if it's the only one you get
every bug.  The above URL still does not work.

Comment 7

[Matthew Tuck [:CodeMachine]]

I've just been looking at this.  Replacing the functions with:

"^blocked," => sub {
   my $table = "dependencies_$chartid";
   push(@supptables, "LEFT JOIN dependencies $table ON bugs.bug_id =
$table.dependson");
   $f = "$table.blocked";
},

"^dependson," => sub {
   my $table = "dependencies_$chartid";
   push(@supptables, "LEFT JOIN dependencies $table ON bugs.bug_id =
$table.blocked");
   $f = "$table.dependson";
},

seems to do the trick for normal access.  I'm not sure whether the $chart_id is
needed for this.

However, it will fail when there are several bugs linked to the bug in question,
because of the way the SQL works and the fact that dependencies are in a
different table.  It will check whether it is equal to any of the bugs listed,
or not equal to any of the bugs listed, rather than them all as a whole.  This
is also a problem with queries on CCs.

In fact, I don't see any way to make that work simply in one query without SQL
Subselects, which aren't supported in MySQL according the docs I've read.

Other possibilities are temporary tables (urgh), not generating a condition in
the initial query, but later going through and doing checks on each bug returned
(slow?), or putting redundant cc, dependson and blocked information on the bugs
table.  It looks like this has already been done with keywords!

Comment 8

[Matthew Tuck [:CodeMachine]]

I'm assuming here there's no function to concatenate every value in a column of
a group in a SELECT statement, that would be the ideal solution.

Comment 9

[Tara Hernandez]

Gerv is going to see if he can create a workaround for this that won't require
changing the query CGI to hell and gone. If he can great, if not, this will be
part of the 3.0 spec.

Comment 10

[Dawn Endico]

I think the following query is an example of the kind of query we would
like to do. (Is that right?)  This query searches for verified bugs which
block bug 7229.

select bugs.bug_id, dependencies.blocked,dependencies.dependson from
dependencies, bugs where dependencies.blocked="7229" and
bugs.bug_status="VERIFIED" and dependencies.dependson=bugs.bug_id;

I think what bugzilla is missing is the "dependencies.dependson=bugs.bug_id"
part of the query.

Comment 11

[Matthew Tuck [:CodeMachine]]

Dawn, IIRC the change I posted would fix queries with just one bug.  It was just
the ones where you wish to specify more than one that are difficult.

Comment 12

[Dawn Endico]

what do you mean by 'more than one bug'? Do you mean queries like:

'search for all bugs which block either bug 7229 or bug 40935'

This query returns the correct result.

select bugs.bug_id, dependencies.blocked,dependencies.dependson from
dependencies, bugs where (dependencies.blocked="7229" or
dependencies.blocked="40935")  and dependencies.dependson=bugs.bug_id;

Comment 13

[Dawn Endico]

Attachment: initial patch

Comment 14

[Matthew Tuck [:CodeMachine]]

Dawn, I think your query needs to be "contains" not "equal to".  7229 might be
present along with other bugs.

Comment 15

[Matthew Tuck [:CodeMachine]]

An ideal solution here would:

- support equal to, not equal to, contains, not contains on the advanced query
list box.
- realise that CC and Deps are sets and properly respect dupes and the lack of
order (ie it shouldn't matter which order you enter the bugs into the advanced
query text box)

It sounds like you're on the right track, but I didn't really know enough about
the code or Perl to work out how to do it within the existing model.

Comment 16

[Dawn Endico]

with this patch applied to my test installation (on the nscp intranet), things
seem to work. Using boolean charts, I searched for all bugs which block either
bug 7229 or bug 40935. The resulting query had two problems. First, it was
missing the dependencies.dependson=bugs.bug_id clause. Second, it had an extra
"dependencies.blocked = '7229' AND dependencies.blocked = '40935'" in it.
The query had both that and "(dependencies.blocked = '7229' OR
dependencies.blocked = '40935')" clauses. The first pair of clauses don't belong
in the query.

The problem with my patch is that it adds one
"dependencies.dependson=bugs.bug_id" clause per dependency. That should be
harmless but I'll try to get rid of it.

Comment 17

[Dawn Endico]

another problem with my patch (as matty indicates) is that it doesn't
work if you have multiple ANDs in the bug chart referring to dependencies.
However, multiple ORs do work. You can search for the union of N tracking
bugs but you can't search for their intersection.

Oh well. that's better than nothing. I'll continue trying to figure out
how to do the query properly but in the mean time I think this solution
is good enough. I think set unions is 90% of what we need anyway (for our
new mozilla.org milestone tracking plan)

Comment 18

[Matthew Tuck [:CodeMachine]]

What I said earlier about not needing contains is wrong IIRC as each dep record
contains 2 bug numbers only.  I was thinking about a space separated list, which
is what bugs.keywords is.

What I said earlier about my fix working for queries on one bug is inconsistent
with my earlier comment and I think wrong.

I'll shut up now.

Comment 19

[Matthew Tuck [:CodeMachine]]

Argh.  "Needing contains" not "Not needing contains".

Comment 20

[Dawn Endico]

hooray! i figured out how to craft a query to search for set intersections.
Getting bugzilla to do this may be a bit trickier.

this query searches for bugs that block both bug 7229 and 40935

SELECT bugs.bug_id FROM bugs, dependencies d1, dependencies d2 WHERE d1.blocked
= '40935' AND d2.blocked = '7229' AND d1.dependson=bugs.bug_id AND
d2.dependson=bugs.bug_id GROUP BY bugs.bug_id;

Comment 21

[Dawn Endico]

Attachment: (better) patch allowing set unions of dependencies but not intersection

Comment 22

[Dawn Endico]

fwiw, i found an example of the above query syntax in Sam's 'Teach Yourself
SQL in 10 minutes' at the top of page 93 under the section 'creating self
joins'. That example was used as an alterative to doing subselects which
mysql doesn't support.

Comment 23

[Dawn Endico]

Attachment: a still better patch allowing set unions and intersections for queries on dependencies

Comment 24

[Dawn Endico]

I think this latest patch should fix all queries on dependencies
although I haven't done extensive testing yet. Those of you on
the inside of the netscape firewall can play with my db at
http://amok/bugzilla/

Comment 25

[Dawn Endico]

Attachment: this patch fixes cc too, and comments on boolean charts

Comment 26

[Tara Hernandez]

endico's patch is applied to landfill (http://landfill.tequilarista.org/).  A 
preliminary look seems like there are some problems.  Without too much effort I 
can get this to happen:

Can't use string ("") as a subroutine ref while "strict refs" in use at 
/home/httpd/html/bugzilla/buglist.cgi line 344. 

In particular this happens if I try to use the "substring" selections for the CC 
query.  Other string based queries, for example "Assigned To" don't seem to have 
this problem.

If I use the "OR" button and extend the string, it seems like only the second 
query is actually run, and incorrectly.  The query I tried was "Depends on bug 
equals 5" or "CC contains [e-mail]" returned only bug 36, and not 
bug 10, which is another cyeh CC bug, or bug 4, which was a depends on 5 bug.

Will continue to try and isolate this.

Comment 27

[Dave Miller [:justdave]]

removing the patch keyword, since the patches that are here don't 
completely work yet (see Tara's comments above).  Put it back when we 
have a working patch.

Comment 28

[Matthew Tuck [:CodeMachine]]

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

Comment 29

[Matthew Tuck [:CodeMachine]]

Is it worthwhile putting in what we have now and leaving the fix for the
remaining bugs for 2.14?

Comment 30

[Chris Yeh]

If a new patch isn't forthcoming, I propose this gets dropped off the 2.12 list.

Comment 31

[Dave Miller [:justdave]]

moving to real milestones...

Comment 32

[Dawn Endico]

Attachment: removed row number from table aliases

Comment 33

[Dawn Endico]

haven't tested this new patch with cc's at all.

Comment 34

[Dave Miller [:justdave]]

why are we removing the code to let you put a stored query in the footer when you 

create it?  (or maybe we aren't and I'm just reading it wrong)

Comment 35

[Dawn Endico]

oops. didn't mean to do that. my file is apparently out of sync.

Comment 36

[Dawn Endico]

Attachment: this one is properly in sync

Comment 37

[Dave Miller [:justdave]]

A visual glance over this patch looks good.  I don't have a real easy way to test 
it though.  Don't have enough bugs with weird conditions in them in any of the 
installs I have access to.

Comment 38

[Dawn Endico]

Ok, i'm reasonably certain this is right.

On my test db i did the following queries and got correct looking results.

bugs blocking 7229, bugs blocking 40935, bugs that block either 7229 and 40935
(set union), bugs that block both 7229 and 40935 (set intersection).

Note that the last query requires two boolean charts. If you do "blocks 40935
AND blocks 7229" in a single chart then you're looking for a single entry in
the dependencies table where the 'blocks' field equals both 40935 and 7229,
which is impossible and the query comes up empty. 

The only relations that work with searching the CC field are
"contains (case-sensitive) substring", "contains regexp" and "equal to"
but i think that problem is beyond the scope of this bug.

I did various searches on CC and they seemed to work as well. On the
other hand, the queries i'm trying on b.m.o seem to work too. What's an 
example of a broken cc query?

Comment 39

[Jacob Steenhagen]

I once tried to run a query of all bugs I had commented on but wasn't CC'd on
([e-mail] - Added Comment in e-mail section -- CC - Does not contain -
[e-mail] in boolean chart)... but you just said that still doesn't work :)

Comment 40

[Dawn Endico]

checked in code. marking fixed. tara will update to tip on landfill,
call it 2.12 and let people test.

Comment 41

[Dawn Endico]

bah, resolution wasn't set because of the midair collision. reopening

Comment 42

[Dawn Endico]

marking fixed again

Comment 43

[Matthew Tuck [:CodeMachine]]

Apparently Dawn backed out the CC changes yet did not reopen this bug.  Dawn do
you want to reopen this and close bug #72977 or should we move over to that and
leave this alone?

Comment 44

[Dave Miller [:justdave]]

Moving closed bugs to Bugzilla product