319466 - commenter "contains" and "was me" fails with: Not unique table/alias: 'longdescs_0'

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[GavinS]

https://bugzilla.mozilla.org/buglist.cgi?query_format=advanced&short_desc_type=allwordssubstr&short_desc=+&product=Bugzilla&long_desc_type=allwordssubstr&long_desc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&status_whiteboard_type=allwordssubstr&status_whiteboard=&keywords_type=allwords&keywords=&resolution=DUPLICATE&resolution=---&emaillongdesc1=1&emailtype1=exact&email1=bugzilla%40chimpychompy.org&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailtype2=exact&email2=&bugidtype=include&bug_id=&votes=&chfieldfrom=&chfieldto=Now&chfieldvalue=&cmdtype=doit&order=Reuse+same+sort+as+last+time&field0-0-0=longdesc&type0-0-0=substring&value0-0-0=title&field0-1-0=commenter&type0-1-0=equals&value0-1-0=bugzilla%40chimpychompy.org

Software error:

DBD::mysql::st execute failed: Not unique table/alias: 'longdescs_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  INNER JOIN profiles AS map_assigned_to ON (bugs.assigned_to = map_assigned_to.userid) LEFT JOIN longdescs AS longdescs_LD0 ON (longdescs_LD0.bug_id = bugs.bug_id  AND longdescs_LD0.who IN (15150)) INNER JOIN longdescs AS longdescs_0 ON (longdescs_0.bug_id = bugs.bug_id ) LEFT JOIN longdescs AS longdescs_0 ON (longdescs_0.bug_id = bugs.bug_id ) LEFT JOIN profiles AS map_longdescs_0 ON (longdescs_0.who = map_longdescs_0.userid) LEFT JOIN bug_group_map  ON bug_group_map.bug_id = bugs.bug_id  WHERE ((bugs.product_id IN (19)) AND (bugs.resolution IN ('DUPLICATE','')) AND (longdescs_LD0.who IS NOT NULL)) AND ((INSTR(CAST(LOWER(longdescs_0.thetext) AS BINARY), CAST('title' AS BINARY)) > 0) AND (map_longdescs_0.login_name = '[e-mail]')) AND bugs.creation_ts IS NOT NULL AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id"] at /opt/webtools/bugzilla/buglist.cgi line 849
For help, please send mail to the webmaster ([e-mail]), giving this error message and the time and date of the error.

Comment 1

[GavinS]

The search was a boolean search:

comment contains text

AND

commenter was me

Comment 2

[GavinS]

Not b.m.o specific, and happens with tip

Comment 3

[Frédéric Buclin]

I did the same query today and of course got the same error. This query seems so obvious/useful to me that I would really like to see it fixed for 3.0. joel, max, could you have a look at this bug?

Comment 4

[Max Kanat-Alexander]

No, I don't consider errors in combinations of boolean charts to be blockers for a  major feature release, unless this is a regression from 2.22 (which I don't think it is).

However, I'd still like to see it in 3.0 and 2.22, and I've set the target milestone appropriately.

Comment 5

[GavinS]

Attachment: work in progress investigation to fix boolean charts issue

One of the searches is a 'LEFT JOIN longdescs as longdescs_0', the other is a 'INNER JOIN longdescs as longdescs_0'. This means that the check for duplicate tables fails, hence the error.

Changing one of them (see patch) removes the error (, and successfully found the comment I was searching for in my local install.)

Is there a plan for which searches are LEFT JOIN and which are INNER JOIN? (I'm trying to understand why we use each variant.)

There is another issue, a minor redundant check: when we store the list of tables making up the query to check for duplicates (%suppseen), we store the left hand part of the query, but when we check it, we use the whole clause as a hash key, I think

Comment 6

[Joel Peshkin]

They should almost always be left joins.  Left joins still work when the entire table is subjected to a boolean condition or negation.

So, if I want something where A or B is present, I can say....

... FROM bugs 
LEFT JOIN tableA ON bugs.id = tableA.bug_id AND tableA.something = whatever
LEFT JOIN tableB ON bugs.id = tableB.bug_id AND tableB.something2 = whatever2
WHERE (tableA.bug_id IS NOT NULL) OR (tableB.bug_id IS NOT NULL)

and it will work, as will NOT((tableA.bug_id IS NOT NULL) OR (tableB.bug_id IS NOT NULL))

If I make either of those an inner join, this will not work.  Mysql (newer versions) know that they can internally transform the simple cases like 
LEFT JOIN tableA ON bugs.id = tableA.bug_id AND tableA.something = whatever WHERE (tableA.bug_id IS NOT NULL) into the INNER JOIN case and they will do that where they can and, more importanly, will not do that where they should not.

Comment 7

[Joel Peshkin]

Comment on attachment 244132 [details] [diff] [review]
work in progress investigation to fix boolean charts issue

Make it a LEFT JOIN and I think you'll be in business.  Naturally, make sure you test the heck out of it using charts that include negation as well.

Comment 8

[GavinS]

Attachment: Use LEFT JOINS for all longdesc joins

Comment 9

[Joel Peshkin]

Comment on attachment 251680 [details] [diff] [review]
Use LEFT JOINS for all longdesc joins

This looks correct.  I have not yet had a chance to test it out.

Comment 10

[Frédéric Buclin]

Too invasive to go into 2.22. Moreover, this branch is now restricted to security bugs and dataloss only.

Comment 11

[Joel Peshkin]

Comment on attachment 251680 [details] [diff] [review]
Use LEFT JOINS for all longdesc joins

Fix the indent level (whitespace-only change ok) and r=joel

Comment 12

[Frédéric Buclin]

OK to land this patch (with the indentation fixed: 4 spaces, not 2) on the trunk, but it has to wait till 3.0.2 is released before landing it on the 3.0.x branch. It will be taken for 3.0.3.

Comment 13

[Frédéric Buclin]

Bugzilla 3.0.2 has been released. a=me for checkin on the 3.0.x branch now.

Comment 14

[Frédéric Buclin]

tip:

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

3.0:

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

Comment 15

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/qa/4.2/
modified t/test_search.t
Committed revision 210.

Committing to: bzr+ssh://[e-mail]/bugzilla/qa/4.0/
modified t/test_search.t
modified t/lib/QA/Util.pm
Committed revision 199.

Committing to: bzr+ssh://[e-mail]/bugzilla/qa/3.6/
modified t/test_search.t
modified t/lib/QA/Util.pm
Committed revision 157.