385523 - Search with multiple changed to / from booleans always returns 0 results

Status: NEW

(Bugzilla :: Query/Bug List, defect)

Description

[Seth]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4
Build Identifier: 3.0

I was trying to build a search that returned all bugs that had changed from NEW, UNCONFIRMED or ASSIGNED in the last week.  As soon as I added the second changed from field, I began getting 0 results back.  The url supplied is an example of this on the Bugzilla site

Reproducible: Always

Steps to Reproduce:
1. Goto Advanced query
2. Add two or more changed to / from criteria on a field or'd together

Actual Results:  
No results are ever returned

Expected Results:  
The bugs that match any of the matching criteria should be returned

Comment 1

[Dave Miller [:justdave]]

From the debug output of the above query:

LEFT JOIN bugs_activity AS act_0 ON (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 29 AND act_0.removed = 'NEW') AND (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 29 AND act_0.removed = 'UNCONFIRMED')

That looks a lot like an AND instead of an OR to me.  "Edit Search" quite clearly shows OR selected in the search UI though.

Comment 2

[Dave Miller [:justdave]]

This appears to be the ON clause optimization code causing this.  Since the selection criteria for this table is getting pushed into @supptables, and the contents of @supptables is all ANDed together at the end, outside of the chart processing.

The only workable solutions I can think of here is to get the conditions put back into the WHERE clause, or seriously rethink the way we're processing @supptables for ON conditions.

Comment 3

[Dave Miller [:justdave]]

The current code for the activity checks was introduced by Joel in bug 252889.  The previous code had both the join conditions and the search terms in the WHERE part, and that patch moved both into the ON clause on the join.  I suspect the safest and least invasive fix here will be to move the search terms back into the WHERE clause, but leave the join conditions where they are in the ON clause.

Comment 4

[Dave Miller [:justdave]]

Attachment: Patch v1

This patch implements the "move the search condition back into the WHERE part" option.  The patch is live on bugzilla-test.mozilla.org currently (so you can click the URL on this bug, and change the domain name and run it again).  It does fix this particular use case, but I'm not convinced that it doesn't revert the problem that was intended to be fixed in bug 252889 (though it probably doesn't, since most of the stuff that was moved to the ON clause in that bug stayed put).  Removing the "NOT NULL" from the WHERE part is what's making me nervous, although I think that was just a placeholder.  I'd appreciate an opinion from Joel since it's his code.

Comment 5

[Joel Peshkin]

The reason behind both the move to the join clause and the NOT NULL is to make negation work properly.  

If we make the change proposed, then it becomes impossible to search for something that did not happen (you can only search for something else happening).

I think the right fix for this is to fix the code so that the on-clause optimization  understands that these joins are distinct.  That probably means that  we need to bump the activity table index so that we have a distinct act_0 and act_1 before these are even pushed onto supptables.

Comment 6

[Dave Miller [:justdave]]

OK, that's what I was afraid of, re the not null...

Bumping the index would break the semantics of the boolean chart though.  The idea of putting something in the same row in the chart is that it's comparing the same specific item all the way across.  Bumping the index makes it a new independent comparison.

Perhaps we need a scoped version of @supptables inside the OR processing section of the chart, and merge any matching table joins' ON clauses with OR instead of AND at that point inside the chart before pushing onto the real @supptables.

Comment 7

[Dave Miller [:justdave]]

OK, after hashing this out on IRC a little bit, it looks like the @supptables processing is logically in the wrong place...  right now it's being done once at the end.  It shouldn't be getting done at the end at all, but should be getting done after each chart.  The only reason this doesn't break now for the AND conditions is because the table indexes get bumped between charts, so the table joins no longer match to get their conditions merged.  Similarly, it needs to be done at the end of each row as well, using OR instead of AND (which is the part it's missing currently).

Comment 8

[Prasad Menon]

is there a work around to this?
i.e how can i use multiple elementd with changed to/from without returning 0 results.
or is it totally unusable?

Comment 9

[Prasad Menon]

is there a work around to this?
i.e how can i use multiple elementd with changed to/from without returning 0 results.
or is it totally unusable?

Comment 10

[Frédéric Buclin]

The Bugzilla 3.0 branch is now locked to security bugs and dataloss fixes only. This bug doesn't fit into one of these two categories and is retargetted to 3.2 as part of a mass-change. To catch bugmails related to this mass-change, use lts081207 in your email client filter.

Comment 11

[Frédéric Buclin]

Bugzilla 3.2 is restricted to security bugs only. Moreover, this bug is either assigned to nobody or got no traction for several months now. Rather than retargetting it at each new release, I'm clearing the target milestone and the bug will be retargetted to some sensible release when someone starts fixing this bug for real (Bugzilla 3.8 more likely).

Comment 12

[Frédéric Buclin]

mkanat, how hard is it to fix this problem in 4.2? I can still reproduce the problem in 4.1.

Comment 13

[Max Kanat-Alexander]

It will probably be a lot easier to fix, soon. There's a lot of basic fixing to do, and then I'll start testing AND/OR combinations to see what needs to be fixed there.