245158 - Search.pm fails if multiple LEFT JOINs are on the same table in the same chart

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect, P2)

Description

[Joel Peshkin]

If, in a single chart, I try to search for 
"CC" "contains" "justdave" AND
"CC" "contains" "@bugzilla.org"
the resulting query will fail because it tries to do 2 distinct LEFT JOIN
operations on the same table with the same alias.

In order for this to work properly (see bug 237107), Search.pm must do a single
LEFT JOIN for each such case and merge the ON criteria.

Comment 1

[Joel Peshkin]

Attachment: Patch - combine LEFT JOIN statements when the alias is identical

Comment 2

[Joel Peshkin]

Attachment: Fix risk of right side with "ON" in it

Comment 3

[Joel Peshkin]

Attachment: v3 - makes sure that ON clauses are in parentheses before ANDing them

Comment 4

[Joel Peshkin]

Comment on attachment 149685 [details] [diff] [review]
v3 - makes sure that ON clauses are in parentheses before ANDing them

grr...	justdave is right

Comment 5

[Joel Peshkin]

Attachment: revised - append conditons to correct JOIN

Comment 6

[Dave Miller [:justdave]]

Yeah, this looks pretty straightforward, but something about it just makes me
want to wait until we branch to check it in....  (blocking2.18-)

Comment 7

[Zach Lipton [:zach]]

Comment on attachment 151257 [details] [diff] [review]
revised - append conditons to correct JOIN 

r=zach

Comment 8

[Joel Peshkin]

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

Comment 9

[Bradley Baetz (:bbaetz)]

IS this valid? You can left join to yourself, perfectly validly.

Comment 10

[Joel Peshkin]

Yes, this is valid.  

The reason it fails is the same table alias is used.  When we are in the same
chart, we DO want to combine all the conditions on the same left join rather
than creating a new alias.

Comment 11

[Joel Peshkin]

Actually, I didn't answer that very completely...

The fix combines the JOINs only if they use the identical table alias.  So,
there are lots of cases where we want to join the same table several times and
those are untouched.  This only combines the joins when we used the same alias
(previously an error condition) because we meant to do that.

Comment 12

[Dave Miller [:justdave]]

Let's go ahead and put this on the 2.18 branch.  It's been tested enough now
that I trust it on the branch, and it's a useful fix.

Comment 13

[Vlad Dascalu]

Requesting approval for 2.18 checkin (patch applies cleanly)

Comment 14

[Dave Miller [:justdave]]

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

Comment 15

[Vlad Dascalu]

Joel: can you check this in for 2.18? It's pretty critical for that release :-)

Comment 16

[Joel Peshkin]

Checked in on 2.18 branch

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