275523 - Replace INNER JOIN in Flag searches with LEFT JOIN

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[agrieco]

Problem manifests itself as follows:

When doing a boolean search for flag and flag requestee, the following error is
seen:

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


Comments from bug 275422:

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


------- Additional Comment #6 From Joel Peshkin 2004-12-20 20:08 PST [reply] -------

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 1

[Vlad Dascalu]

Attachment: patch v1

Comment 2

[Vlad Dascalu]

This blocks a 2.18 blocker, so it's normal to be a blocker in itself. Requesting
blocker flags, and review on the patch.

agrieco: can you test this patch and see if it solves the symptoms? I did
perform basic testing on my localhost and it seems ok.

Joel: do you have time for this quick patch review, considering it's a 2.18
blocker? Thanks!

Comment 3

[Vlad Dascalu]

agrieco: this patch is for the 2.19 tree; it should work on 2.18 as well if you
apply first the patch in bug 245158.

Comment 4

[Joel Peshkin]

Comment on attachment 169854 [details] [diff] [review]
patch v1

r=joel if you've tested it.  My testing database isn't in good shape for this
right now.

This will probably be very slow on very large databases.  If people are going
to do this a lot, we should look up the userid first.  That can be an
enhancment later.

Comment 5

[agrieco]

I have validated that this patch, and the patch for bug 245158 together work to
fix this problem.  My database is extremely small, so no useful performance info
can be garnered.

thanks for the fix!
agrieco

Comment 6

[Dave Miller [:justdave]]

(In reply to comment #4)

> This will probably be very slow on very large databases.  If people are going
> to do this a lot, we should look up the userid first.  That can be an
> enhancment later.

Please file a bug requesting this before we forget.  Anything that speeds it up
is probably a good idea whether or not we think it's slow :)

In any case, I'll take accuracy over speed anyway. :)

Comment 7

[Vlad Dascalu]

I've opened bug 276566 about that one.

Comment 8

[Vlad Dascalu]

Checking in Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.71; previous revision: 1.70
Checking in Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.57.2.5; previous revision: 1.57.2.4

Comment 9

[Dave Miller [:justdave]]

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