Bug 479428

Summary:	Fails: Find 'Status changed from zzz AND Status Changed to xxx OR Status changed to yyy' FAILS_QA and ASSIGNED
Product:	[Community] Bugzilla	Reporter:	Chris Ward <cward>
Component:	Query/Bug List	Assignee:	Kevin Baker <kbaker>
Status:	CLOSED RAWHIDE	QA Contact:
Severity:	medium	Docs Contact:
Priority:	low
Version:	devel	CC:	benl, ineilsen
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2012-02-29 12:18:29 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Chris Ward 2009-01-09 15:07:23 UTC

Description of problem:
I'm trying to find bugs which at some point:

Status Changed From ON_QA [AND]
Status Changed To FAILS_QA [OR]
Status Changed To ASSIGNED

But i get no results. Even though if i do From ON_QA to FAILS_QA/ASSIGNED individually i get results for both. I tried serveral different variations of the same search but couldn't get it to work. It's either broken or i'm doing something wrong...

How reproducible:
Always. 

Steps to Reproduce:
1. Launch this search. 
https://bugzilla.redhat.com/query.cgi?field-1-0-0=product&field0-0-0=flagtypes.name&field0-1-0=bug_status&field0-2-0=bug_status&field0-2-1=bug_status&product=Red%20Hat%20Enterprise%20Linux%205&query_format=advanced&remaction=&type-1-0-0=anyexact&type0-0-0=substring&type0-1-0=changedfrom&type0-2-0=changedto&type0-2-1=changedto&value-1-0-0=Red%20Hat%20Enterprise%20Linux%205&value0-0-0=rhel-5.3.0%2B&value0-1-0=ON_QA&value0-2-0=ASSIGNED&value0-2-1=FAILS_QA

  
Actual results:
No results.

Expected results:
Correct number of results which meets those criteria.

Comment 1 David Lawrence 2009-01-09 17:21:54 UTC

This is indeed broken it seems, that in the SQL produced it is not properly performing an OR condition between the two changedto statuses. It is doing an AND instead and which cannot happen so the results are zero. Here is the SQL generated viewable by adding &debug=1 to the URI in buglist.cgi.

0-0 = product | anyexact | Red Hat Enterprise Linux 5 *
^product,(?!changed) (product / anyexact / Red Hat Enterprise Linux 5) =>
products.name / anyexact / Red Hat Enterprise Linux 5 / bugs.product_id IN (141) *
^flagtypes.name, (flagtypes.name / substring / rhel-5.3.0+) =>
flagtypes.name / substring / rhel-5.3.0+ / INSTR(CONCAT(flagtypes_0.name, flags_0.status), 'rhel-5.3.0+') > 0 *
,(changedfrom|changedto) (bug_status / changedfrom / ON_QA) =>
bug_status / changedfrom / ON_QA / (act_0.bug_when IS NOT NULL) *
,(changedfrom|changedto) (bug_status / changedto / ASSIGNED) =>
bug_status / changedto / ASSIGNED / (act_0.bug_when IS NOT NULL) *
,(changedfrom|changedto) (bug_status / changedto / FAILS_QA) =>
bug_status / changedto / FAILS_QA / (act_0.bug_when IS NOT NULL) *

SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, map_products.name, bugs.bug_severity, bugs.priority, map_assigned_to.login_name, bugs.bug_status, bugs.version, bugs.op_sys, FlagList(bugs.bug_id,'96,134,191,113,111,190,128,129,127,58,159,122,133,13,126,93,17,99,71,202,197,162,8,110,31,153,23,108,63,24,139,124,150,135,102,85,57,136,125,98,38,147,86,106,70,152,68,148,132,67,6,65,192,161,171,20,151,50,27,10,109,187,100,196,101,105,121,3,92,114,21,1,116,83,186,74,103,81,34,157,149,198,138,201,2,12,140,144,79,76,97,195,182,35,7,120,146,189,84,131,107,32,4,78,14,200,69,123,44,19,183,82,87,143,15,137,80,194,155,117,95,199,75,118,130,29,181,41,9,112,141,185,11,119,104,33,22,18,56,48,145,184,16,188,91,25,154,156,193,30,142,5,94',1) AS flags, IssueTrackerList(bugs.bug_id) AS issuetrackers, bugs.short_desc, bugs.cf_devel_whiteboard 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 flags AS flags_0 ON (bugs.bug_id = flags_0.bug_id ) LEFT JOIN flagtypes AS flagtypes_0 ON (flags_0.type_id = flagtypes_0.id) LEFT JOIN bugs_activity AS act_0 ON (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.removed = 'ON_QA') AND (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'ASSIGNED') AND (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'FAILS_QA') LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 6 WHERE (( bugs.product_id IN (141) )) AND ((INSTR(CONCAT(flagtypes_0.name, flags_0.status), 'rhel-5.3.0+') > 0) AND ((act_0.bug_when IS NOT NULL)) AND (((act_0.bug_when IS NOT NULL)) OR ((act_0.bug_when IS NOT NULL)))) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL) OR (bug_group_map.group_id IN (96,134,191,113,111,190,128,129,127,58,159,122,133,13,126,93,17,99,71,202,197,162,8,110,31,153,23,108,63,24,139,124,150,135,102,85,57,136,125,98,38,147,86,106,70,152,68,148,132,67,6,65,192,161,171,20,151,50,27,10,109,187,100,196,101,105,121,3,92,114,21,1,116,83,186,74,103,81,34,157,149,198,138,201,2,12,140,144,79,76,97,195,182,35,7,120,146,189,84,131,107,32,4,78,14,200,69,123,44,19,183,82,87,143,15,137,80,194,155,117,95,199,75,118,130,29,181,41,9,112,141,185,11,119,104,33,22,18,56,48,145,184,16,188,91,25,154,156,193,30,142,5,94)) OR (bugs.reporter_accessible = 1 AND bugs.reporter = 6) OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL) OR (bugs.assigned_to = 6) OR (bugs.qa_contact = 6) ) GROUP BY bugs.bug_id ORDER BY bugs.bug_id;

The problem is located in:

LEFT JOIN bugs_activity AS act_0 ON (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.removed = 'ON_QA') AND (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'ASSIGNED') AND (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'FAILS_QA')

which should be:

LEFT JOIN bugs_activity AS act_0 ON (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.removed = 'ON_QA') AND ((act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'ASSIGNED') OR (act_0.bug_id = bugs.bug_id AND act_0.fieldid = 2 AND act_0.added = 'FAILS_QA'))

Will look into this.
Dave

Comment 2 David Lawrence 2009-01-09 17:27:38 UTC

Also the following looks suspect as well.

((act_0.bug_when IS NOT NULL)) AND (((act_0.bug_when IS NOT NULL)) OR ((act_0.bug_when IS NOT NULL))))

which makes no sense.

I did find an upstream bug similar to this.

https://bugzilla.mozilla.org/show_bug.cgi?id=422480

Comment 3 Chris Ward 2009-08-04 10:00:38 UTC

Ping, any update here?

Comment 4 Chris Ward 2010-10-07 10:18:59 UTC

Still failing, even after update to 3.6

Comment 6 Chris Ward 2012-02-29 12:18:29 UTC

This should be doable in 4.2; and what's not possible, i have other solutions available anyway.