275408 - search problem when using flag and flag requestee

Status: RESOLVED DUPLICATE of bug 245158

(Bugzilla :: Query/Bug List, defect)

Description

[agrieco]

I get the following error when using a 'flag requestee" "contains" "<something>"
AND "flag" "contains" "<something>"

I have tried this on the production, bugzilla 2.19 release for bugzilla bugs
(this system), but I dont get the same error.  This error is seen using 2.18rc3.
and is reproducable.  If I only use one of the conditions, the query returns
normally, and when I make both conditions the same, the query also returns normally.


DBD::mysql::st execute failed: Not unique table/alias: 'flags_0' [for Statement
"SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, flags flags_0, profiles requestees_0
LEFT JOIN flags flags_0 ON bugs.bug_id = flags_0.bug_id AND flags_0.is_active =
1 LEFT JOIN flagtypes flagtypes_0 ON flags_0.type_id = flagtypes_0.id LEFT JOIN
bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND bug_group_map.group_id
NOT IN (7,12,8)  LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = 4 WHERE
bugs.assigned_to = map_assigned_to.userid AND bugs.bug_id = flags_0.bug_id AND
flags_0.requestee_id = requestees_0.userid AND (bugs.bug_status IN
('NEW','ASSIGNED','REOPENED')) AND (INSTR(LOWER(requestees_0.login_name),
'agrieco')) AND (INSTR(LOWER(CONCAT(flagtypes_0.name, flags_0.status)), 'info'))
AND ((bug_group_map.group_id IS NULL)    OR (bugs.reporter_accessible = 1 AND
bugs.reporter = 4)     OR (bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)  
  OR (bugs.assigned_to = 4) OR (bugs.qa_contact = 4) ) GROUP BY bugs.bug_id
ORDER BY bugs.bug_status,bugs.priority,map_assigned_to.login_name,bugs.bug_id "]
at Bugzilla/DB.pm line 62
	Bugzilla::DB::SendSQL('SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bu...') called at <dirname> buglist.cgi line 770

Comment 1

[Shane H. W. Travis]

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

Comment 2

[Dave Miller [:justdave]]

This appears to have been fixed by bug 245158, which indeed, only got checked in
on the trunk after we branched.

The code in question has been tested enough that I'd feel safe backporting it at
this point...  Since that bug is marked resolved, I'll leave this one open for
now so it doesn't get lost.  That bug can be retargeted to 2.18 and mark this
one as a dupe of it once it gets checked in.

Comment 3

[agrieco]

Thanks for the prompt response.  I will patch in the diffs and go from there.

Thanks!

Comment 4

[agrieco]

I am afraid this patch did not fix my problem:

DBD::mysql::st execute failed: Not unique table/alias: 'flags_0' [for Statement
"SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status,
bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform,
map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc
FROM bugs, profiles AS map_assigned_to, flags flags_0, profiles requestees_0 
LEFT JOIN flags flags_0 ON (bugs.bug_id = flags_0.bug_id AND flags_0.is_active =
1) LEFT JOIN flagtypes flagtypes_0 ON (flags_0.type_id = flagtypes_0.id) LEFT
JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  AND
bug_group_map.group_id NOT IN (6,3,10,12,4,1,7,9,11,2,5,8)  LEFT JOIN cc ON
cc.bug_id = bugs.bug_id AND cc.who = 1 WHERE bugs.assigned_to =
map_assigned_to.userid AND bugs.bug_id = flags_0.bug_id AND flags_0.requestee_id
= requestees_0.userid AND (bugs.resolution IN ('')) AND
(CONCAT(flagtypes_0.name, flags_0.status) = 'inforequired?') AND
(requestees_0.login_name = '[e-mail]') AND ((bug_group_map.group_id IS
NULL)    OR (bugs.reporter_accessible = 1 AND bugs.reporter = 1)     OR
(bugs.cclist_accessible = 1 AND cc.who IS NOT NULL)     OR (bugs.assigned_to =
1) OR (bugs.qa_contact = 1) ) GROUP BY bugs.bug_id ORDER BY
bugs.bug_status,bugs.priority,map_assigned_to.login_name,bugs.bug_id "] at
Bugzilla/DB.pm line 62
	Bugzilla::DB::SendSQL('SELECT bugs.bug_id, bugs.bug_severity, bugs.priority,
bugs.bu...') called at /local/apache2/htdocs/dev_bugz/buglist.cgi line 770

thanks

Comment 5

[Dave Miller [:justdave]]

Hmm, both an inner join and a left join on the same table alias...   oof.

Comment 6

[Joel Peshkin]

I did add code a while ago to consolidate multiple identical joins into the same
join using the same alias.  However, I doubt that it would help when the joins
are not identical and they use the same alias.  This is another case where we
should be using a LEFT JOIN regardless (mysql4 will optimize them out when they
aren't otherwise necessary).  Please write a bug on that and have it block the
LEFT JOIN bug.

Comment 7

[agrieco]

Thanks for the analysis.  I have filed 275523 to document this problem, although
Im not quite sure why I needed a new bug.

Thanks for the help.  Let me know if you have some diff's you would like me to try.

thanks
anthony  

Comment 8

[Vlad Dascalu]

So, this is failing on CVS tip as well.

Comment 9

[Vlad Dascalu]

I posted a patch in bug 275523. Not sure what to do with this one. If it will be
solved by the patch in that bug, we can mark this one as a duplicate or
something like that.

Bug 245158 has been reopened, probably someone (justdave?) should change its
blocking2.18 state from blocking2.18- to blocking2.18+.

Comment 10

[Dave Miller [:justdave]]

(In reply to comment #9)
> I posted a patch in bug 275523. Not sure what to do with this one. If it will be
> solved by the patch in that bug, we can mark this one as a duplicate or
> something like that.
> 
> Bug 245158 has been reopened, probably someone (justdave?) should change its
> blocking2.18 state from blocking2.18- to blocking2.18+.

The purpose of leaving this bug open was so we wouldn't have to reopen bug
245158, but it looks like someone did it anyway, which means we no longer have a
use for this bug.

*** This bug has been marked as a duplicate of 245158 ***