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 = 'email@example.com')) 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 (firstname.lastname@example.org), giving this error message and the time and date of the error.
The search was a boolean search: comment contains text AND commenter was me
Not b.m.o specific, and happens with tip
Summary: b.m.o failure: DBD::mysql::st execute failed: Not unique table/alias: 'longdescs_0' [ → DBD::mysql::st execute failed: Not unique table/alias: 'longdescs_0' [
Version: 2.20 → 2.21
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?
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.
Flags: blocking3.0? → blocking3.0-
Summary: DBD::mysql::st execute failed: Not unique table/alias: 'longdescs_0' [ → commenter "contains" and "was me" fails with: Not unique table/alias: 'longdescs_0'
Target Milestone: --- → Bugzilla 2.22
Created attachment 244132 [details] [diff] [review] 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
Attachment #244132 - Flags: review?
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 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.
Attachment #244132 - Flags: review? → review-
Created attachment 251680 [details] [diff] [review] Use LEFT JOINS for all longdesc joins
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.
Too invasive to go into 2.22. Moreover, this branch is now restricted to security bugs and dataloss only.
Target Milestone: Bugzilla 2.22 → Bugzilla 3.0
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
Attachment #251680 - Flags: review?(bugreport) → review+
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.
Bugzilla 3.0.2 has been released. a=me for checkin on the 3.0.x branch now.
Flags: approval3.0? → approval3.0+
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: 22.214.171.124; previous revision: 1.145 done
Status: ASSIGNED → RESOLVED
Last Resolved: 11 years ago
Resolution: --- → FIXED
Committing to: bzr+ssh://email@example.com/bugzilla/qa/4.2/ modified t/test_search.t Committed revision 210. Committing to: bzr+ssh://firstname.lastname@example.org/bugzilla/qa/4.0/ modified t/test_search.t modified t/lib/QA/Util.pm Committed revision 199. Committing to: bzr+ssh://email@example.com/bugzilla/qa/3.6/ modified t/test_search.t modified t/lib/QA/Util.pm Committed revision 157.
Flags: testcase? → testcase+
You need to log in before you can comment on or make changes to this bug.