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

RESOLVED FIXED in Bugzilla 3.0

Status

()

RESOLVED FIXED
13 years ago
6 years ago

People

(Reporter: bugzilla, Assigned: bugzilla)

Tracking

({selenium})

2.21
Bugzilla 3.0
selenium
Bug Flags:
approval +
approval3.0 +
blocking3.0 -
testcase +

Details

Attachments

(1 attachment, 1 obsolete attachment)

(Assignee)

Description

13 years ago
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 = 'bugzilla@chimpychompy.org')) 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 (sysadmins@mozilla.org), giving this error message and the time and date of the error.
(Assignee)

Comment 1

13 years ago
The search was a boolean search:

comment contains text

AND

commenter was me

Updated

13 years ago
OS: MacOS X → All
Hardware: Macintosh → All
(Assignee)

Comment 2

13 years ago
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

Comment 3

12 years ago
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?
Flags: blocking3.0?

Comment 4

12 years ago
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
(Assignee)

Comment 5

12 years ago
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?

Comment 6

12 years ago
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

12 years ago
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-
(Assignee)

Comment 8

12 years ago
Created attachment 251680 [details] [diff] [review]
Use LEFT JOINS for all longdesc joins
Assignee: query-and-buglist → bugzilla
Attachment #244132 - Attachment is obsolete: true
Status: NEW → ASSIGNED
Attachment #251680 - Flags: review?

Updated

12 years ago
Attachment #251680 - Flags: review? → review?(bugreport)

Comment 9

12 years ago
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

12 years ago
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

Updated

11 years ago
Attachment #251680 - Flags: review?(mkanat)

Updated

11 years ago
Attachment #251680 - Flags: review?(justdave)

Comment 11

11 years ago
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+

Updated

11 years ago
Attachment #251680 - Flags: review?(mkanat)
Attachment #251680 - Flags: review?(justdave)

Comment 12

11 years ago
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.
Flags: approval3.0?
Flags: approval+

Comment 13

11 years ago
Bugzilla 3.0.2 has been released. a=me for checkin on the 3.0.x branch now.
Flags: approval3.0? → approval3.0+

Comment 14

11 years ago
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
Status: ASSIGNED → RESOLVED
Last Resolved: 11 years ago
Flags: testcase?
Resolution: --- → FIXED

Comment 15

7 years ago
Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/qa/4.2/
modified t/test_search.t
Committed revision 210.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/qa/4.0/
modified t/test_search.t
modified t/lib/QA/Util.pm
Committed revision 199.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/qa/3.6/
modified t/test_search.t
modified t/lib/QA/Util.pm
Committed revision 157.
Flags: testcase? → testcase+

Updated

6 years ago
Keywords: selenium
You need to log in before you can comment on or make changes to this bug.