276232 - "Bug Changes": selecting "bug creation" OR "status" (changed in date range) only returns "status" results

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Mike Cowperthwaite]

b.m.o  (browsing with Opera 7.54)

I set up a query to return a subset of bugs with New status where either the bug 
creation or the status changed in the past five days.

The results were identical to querying only on status, but there are results 
where "bug creation" was in the past five days which do not trigger the "status 
changed" condition.  (Which, itself, seems wrong -- if a bug is newly created 
with the New status, I might expect the "status" field to be marked as "changed" 
at the same time as the creation.)

Comment 1

[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 2

[Justin C. De Vries]

Here is some more information on reproducing this bug from one of our Bugzilla
users:

<quote>
> Not yet.  The additional information I can add at this point is that
> the bugs in question (a bug which has been created, but does not yet
> have any post-creation activity) in fact DO show up if I change the
> "Bug Changes" criteria in my search to be ONLY "[Bug creation]".
>  
> i.e. In a query where "[Bug creation]" within a certain amount of days
> is the only criteria (or "Bug Changes" criteria isn't being used at
> all) the bugs in question do show up.  These same bugs do NOT show up
> if additional items are selected in addition to "[Bug creation]" (e.g.
> "Status" and "Assignee", as shown in the example search provided
> earlier).
>  
> It's not that the multiple-select in "Bug Changes" is acting like
> boolean "AND"; we can change "anything" about the bug (i.e. something
> other than the "Status" and/or "Assignee", which are the other "Bug
> Changes" criteria in our search) and the bugs will start to show up.
> For example, we changed "Priority" on a newly-created bug which was
> absent from the search results, and even though "Priority" is not part
> of our "Bug Changes" criteria the bug then started showing up.
>  
> So it's more like there is just some bug in the "[Bug creation]" match
> criteria for "Bug Changes" when there are also other "Bug Changes"
> criteria selected.  And somehow "touching" the bug with post-creation
> activity works around it, even though the bug still doesn't match the
> search criteria in any way except based on the "[Bug creation]"
> criteria.
>  
> This was reproducing at will last week (but we "fixed" all the bugs in
> question); if you're not immediately able to see what I'm describing,
> let me know and I'll set one up and confirm I'm still seeing the same
> thing & point you to a specific bug record.

I hope this helps with pin pointing the problem. Thanks.

Comment 3

[Aaron Larson]

Attachment: Patch to fix whitespace in choose-product template.

Comment 4

[Aaron Larson]

Comment on attachment 187367 [details]
Patch to fix whitespace in choose-product template.

Sorry, submitted patch to wrong bug.

Comment 5

[Todd Stansell]

I got to looking at this from an email exchange on [e-mail] and I came to the conclusion that the code used in the "Bug Changes" section differs slightly than the boolean charts and such.

Aaron Larson wrote:
=====================================================================

Thanks Todd.  I appreciate your help.  I won't even go into the more embarrassing details of this (e.g., why I could find that bug 4 months ago, but not 4 days ago).  Anyway, I was looking at the release notes for 2.21 and I saw that it now supports creation time as one of the fields in the boolean charts.  I composed a similar query in 2.21 using the boolean charts and used the "&debug=1" trick to see the resulting SQL.  The boolean chart query in 2.21 appears to work properly, but the "Bug Changes" box query is still broken (and is effectively unchanged from 2.18, or 2.20).

I marked the interesting differences below with an asterisk.  I'm afraid the subtlety here is beyond my SQL knowledge (which is a good approximation to zero).

2.21.1+ boolean chart query:

 SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity, bugs.priority
, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc 
 FROM bugs 
 INNER JOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) 
 INNER JOIN products AS map_products ON (bugs.product_id = map_products.id) 
*LEFT JOIN bugs_activity AS act_0 ON (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 8 AND act_0.bug_when > '2005-10-22 00:00:00') 
 LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id 
*WHERE (((bugs.creation_ts > '2005-10-22') OR ((act_0.bug_when IS NOT NULL)))) 
   AND bugs.creation_ts IS NOT NULL
   AND ((bug_group_map.group_id IS NULL)) 
 GROUP BY bugs.bug_id ORDER BY bugs.bug_id


2.21.1+ "Bug Changes" box query:

 SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity, bugs.priority
, bugs.op_sys, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc 
 FROM bugs
 INNER JOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid)
 INNER JOIN products AS map_products ON (bugs.product_id = map_products.id)
*INNER JOIN bugs_activity AS actcheck ON (actcheck.bug_id = bugs.bug_id AND actcheck.bug_when >= '2005-10-22 00:00:00')
 LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id
*WHERE ( actcheck.fieldid = 8 OR (bugs.creation_ts >= '2005-10-22 00:00:00'))
 AND 1 = 1
 AND bugs.creation_ts IS NOT NULL
 AND ((bug_group_map.group_id IS NULL))
 GROUP BY bugs.bug_id ORDER BY bugs.bug_id

=====================================================================

I looked through all of the Search.pm code and this was the only place where the bugs_activity table used INNER JOIN.  Everywhere else it used LEFT JOIN.  So, I changed the logic in the "Bug Changes" section of Search.pm to coincide with how the boolean charts generated it's SQL.  It fixed the bug.

I'll attach my patch to Search.pm next.

Todd

Comment 6

[Todd Stansell]

Attachment: patch against 2.20 branch

Here's a patch against 2.20.

Comment 7

[Todd Stansell]

Attachment: patch against tip

Here's the patch against the tip.  i'm obsoleting the 2.20 branch patch, but wanted it there in case folks wanted to apply it themselves.

Todd

Comment 8

[Todd Stansell]

Oh, I guess I should take this, since I'm supplying patches :)

Todd

Comment 9

[Aaron Larson]

I upgraded from 2.18 to 2.20 last night and verified that the poposed patch (Attachment #201018 [details] [diff]) fixes the problem I was having.  Thanks Todd!

Comment 10

[Mike Cowperthwaite]

Was this patched applied for b.m.o (currently running "2.20+")?  Because it seems to be working correctly for me now.

Comment 11

[Mike Cowperthwaite]

(In reply to comment #10)
> Was this patched applied for b.m.o (currently running "2.20+")?  Because it
> seems to be working correctly for me now.

Altho b.m.o still reads "2.20+" this has stopped working sometime in the past several weeks; I don't know the date that I last got a correct set of results.

Comment 12

[Joel Peshkin]

Comment on attachment 201018 [details] [diff] [review]
patch against tip

This looks correct... I'll have to test it out at a later date.

Comment 13

[Frédéric Buclin]

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.135; previous revision: 1.134
done

Comment 14

[Mike Cowperthwaite]

The currently-running b.m.o version ("2.20+") exhibits this fix.  Thanks.

Comment 15

[Todd Stansell]

What amazing timing.  Just after this finally gets checked in, I discover that it actually introduces a bug in the query code.  The following change also needs to get applied to fix it:

<        "ON (" . join(" OR ", @actlist) . "$extra )");
>        "ON (" . join(" OR ", @actlist) . ") $extra");

Otherwise, any queries where 2 or more items are selected in the "bug changes" drop-down list will result in full table scans of the bugs_activity table for *each* bug that might be a match.

We can open a new bug instead, if you'd rather do that.

Todd

Comment 16

[Dave Miller [:justdave]]

(In reply to comment #15)
> <        "ON (" . join(" OR ", @actlist) . "$extra )");
> >        "ON (" . join(" OR ", @actlist) . ") $extra");
> 
> Otherwise, any queries where 2 or more items are selected in the "bug changes"
> drop-down list will result in full table scans of the bugs_activity table for
> *each* bug that might be a match.
> 
> We can open a new bug instead, if you'd rather do that.

Yikes.  Too bad half of us don't see comments on closed bugs.  We just ran into this bigtime on b.m.o, and just fixed it on bug 365179.

Comment 17

[Ivan Khanna]

Very good work. You can visit this https://bit.ly/3xkjQtY