Launchpad itself

Merge lp:~pcjc2/launchpad/fix-tag-search-bug-501945 into lp:launchpad

  1. fix-tag-search-bug-501945
  2. Merge into devel
Proposed by Peter Clifton
Status: Merged
Approved by: Robert Collins
Approved revision: no longer in the source branch.
Merged at revision: 12200
Proposed branch: lp:~pcjc2/launchpad/fix-tag-search-bug-501945
Merge into: lp:launchpad
Diff against target: 611 lines (+259/-193)
2 files modified
lib/lp/bugs/model/bugtask.py (+25/-13)
lib/lp/bugs/model/tests/test_bugtask.py (+234/-180)
To merge this branch: bzr merge lp:~pcjc2/launchpad/fix-tag-search-bug-501945
Reviewer Review Type Date Requested Status
j.c.sackett (community) code* Approve
Robert Collins (community) Approve
Review via email: mp+46075@code.launchpad.net

Commit message

[r=lifeless][ui=none][bug=501945] Improve SQL efficiency when searching for bugs by tag (Pre-filter by Bug.id)

Description of the change

  Improve SQL efficiency when searching for bugs by tag (Pre-filter by Bug.id)

  Addresses LP Bug #501945:
  Product:+bugs timeout with search for bugs without tags (tag:-*)

  The sub-query for tags was not pre-filtered by bug ID, so was causing
  a large (and unnecessary) load on the database for some queries, such
  as wildcard matches.

  A significant speedup is obtained on large data-sets by rephrasing the
  SQL sub-query to consider only those BugTag rows which match the current
  bug being considered in the outer query.

Also:

  Fix argument order when calling assertEqualIgnoringWhitespace()

  This test is passed an expected and observed parameter, but all
  callers had the parameters swapped around. This didn't break
  anything, but would cause misleading output if a test failed.

To post a comment you must log in.
Revision history for this message
j.c.sackett (jcsackett) wrote :

Peter--

This looks pretty good. There are just two simple things I need fixed below:

> === modified file 'lib/lp/bugs/model/bugtask.py'
> --- lib/lp/bugs/model/bugtask.py 2011-01-05 08:10:10 +0000
> +++ lib/lp/bugs/model/bugtask.py 2011-01-13 01:38:23 +0000
> @@ -1366,7 +1366,8 @@
> """
> joiner = " %s " % joiner
> return joiner.join(
> - "SELECT bug FROM BugTag WHERE tag = %s" % quote(tag)
> + "SELECT TRUE FROM BugTag WHERE " +
> + "BugTag.bug = Bug.id AND BugTag.tag = %s" % quote(tag)
> for tag in sorted(tags))
> @@ -1413,22 +1414,24 @@
> if '*' in wildcards:
> # Only clobber the clause if not searching for all tags.
> if len(include_clause) == 0 or not find_all:
> - include_clause = "SELECT bug FROM BugTag"
> + include_clause = (
> + "SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id")
>
> # Search for the *absence* of any tag.
> if '-*' in wildcards:
> # Only clobber the clause if searching for all tags.
> if len(exclude_clause) == 0 or find_all:
> - exclude_clause = "SELECT bug FROM BugTag"
> + exclude_clause = (
> + "SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id")
>
> # Combine the include and exclude sets.
> if len(include_clause) > 0 and len(exclude_clause) > 0:
> - return "(BugTask.bug IN (%s) %s BugTask.bug NOT IN (%s))" % (
> + return "(EXISTS (%s) %s NOT EXISTS (%s))" % (
> include_clause, combine_with, exclude_clause)
> elif len(include_clause) > 0:
> - return "BugTask.bug IN (%s)" % include_clause
> + return "EXISTS (%s)" % include_clause
> elif len(exclude_clause) > 0:
> - return "BugTask.bug NOT IN (%s)" % exclude_clause
> + return "NOT EXISTS (%s)" % exclude_clause
> else:
> # This means that there were no tags (wildcard or specific) to
> # search for (which is allowed, even if it's a bit weird).

This would be much easier to understand if there was some comment at the start explaining that use of EXISTS and NOT EXISTS in the final query using the include/exclude part means you only need to get a result set, not the actual bugtags, which is why you're selecting TRUE.

> === modified file 'lib/lp/bugs/model/tests/test_bugtask.py'
> --- lib/lp/bugs/model/tests/test_bugtask.py 2010-11-12 03:58:03 +0000
> +++ lib/lp/bugs/model/tests/test_bugtask.py 2011-01-13 01:38:23 +0000
> @@ -201,10 +201,10 @@
> def searchClause(self, tag_spec):
> return build_tag_search_clause(tag_spec)
>
> - def assertEqualIgnoringWhitespace(self, expected, observed):
> + def assertEqualIgnoringWhitespace(self, observed, expected):
> return self.assertEqual(
> - normalize_whitespace(expected),
> - normalize_whitespace(observed))
> + normalize_whitespace(observed),
> + normalize_whitespace(expected))

You should flip those back; by convention we put the expected value first in assertEqual statements.

review: Needs Fixing (code*)
Revision history for this message
Peter Clifton (pcjc2) wrote :

Thanks for the review, I'll update the branch and get back to you.

Revision history for this message
Peter Clifton (pcjc2) wrote :

The above changes should have addressed the issues raised in review, please check again.

Revision history for this message
Robert Collins (lifeless) wrote :

Looks good now, thanks.

review: Approve
Revision history for this message
Robert Collins (lifeless) wrote :

tossing at ec2

Revision history for this message
Peter Clifton (pcjc2) wrote :

Sorry - it is broken, please hold

Revision history for this message
Peter Clifton (pcjc2) wrote :

I was obviously too keen.. more haste, less speed etc..

This time I've _definately_ run "bin/test -t bugtask". I clearly hadn't let it finish when I pushed last time.

Revision history for this message
j.c.sackett (jcsackett) :
review: Approve (code*)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'lib/lp/bugs/model/bugtask.py'
2--- lib/lp/bugs/model/bugtask.py 2011-01-05 08:10:10 +0000
3+++ lib/lp/bugs/model/bugtask.py 2011-01-14 00:41:54 +0000
4@@ -1354,19 +1354,29 @@
5
6
7 def build_tag_set_query(joiner, tags):
8- """Return an SQL snippet to find bugs matching the given tags.
9+ """Return an SQL snippet to find whether a bug matches the given tags.
10
11 The tags are sorted so that testing the generated queries is
12 easier and more reliable.
13
14+ This SQL is designed to be a sub-query where the parent SQL defines
15+ Bug.id. It evaluates to TRUE or FALSE, indicating whether the bug
16+ with Bug.id matches against the tags passed.
17+
18+ Returns None if no tags are passed.
19+
20 :param joiner: The SQL set term used to join the individual tag
21 clauses, typically "INTERSECT" or "UNION".
22 :param tags: An iterable of valid tag names (not prefixed minus
23 signs, not wildcards).
24 """
25+ if tags == []:
26+ return None
27+
28 joiner = " %s " % joiner
29- return joiner.join(
30- "SELECT bug FROM BugTag WHERE tag = %s" % quote(tag)
31+ return "EXISTS (%s)" % joiner.join(
32+ "SELECT TRUE FROM BugTag WHERE " +
33+ "BugTag.bug = Bug.id AND BugTag.tag = %s" % quote(tag)
34 for tag in sorted(tags))
35
36
37@@ -1412,23 +1422,25 @@
38 # Search for the *presence* of any tag.
39 if '*' in wildcards:
40 # Only clobber the clause if not searching for all tags.
41- if len(include_clause) == 0 or not find_all:
42- include_clause = "SELECT bug FROM BugTag"
43+ if include_clause == None or not find_all:
44+ include_clause = (
45+ "EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id)")
46
47 # Search for the *absence* of any tag.
48 if '-*' in wildcards:
49 # Only clobber the clause if searching for all tags.
50- if len(exclude_clause) == 0 or find_all:
51- exclude_clause = "SELECT bug FROM BugTag"
52+ if exclude_clause == None or find_all:
53+ exclude_clause = (
54+ "EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id)")
55
56 # Combine the include and exclude sets.
57- if len(include_clause) > 0 and len(exclude_clause) > 0:
58- return "(BugTask.bug IN (%s) %s BugTask.bug NOT IN (%s))" % (
59+ if include_clause != None and exclude_clause != None:
60+ return "(%s %s NOT %s)" % (
61 include_clause, combine_with, exclude_clause)
62- elif len(include_clause) > 0:
63- return "BugTask.bug IN (%s)" % include_clause
64- elif len(exclude_clause) > 0:
65- return "BugTask.bug NOT IN (%s)" % exclude_clause
66+ elif include_clause != None:
67+ return "%s" % include_clause
68+ elif exclude_clause != None:
69+ return "NOT %s" % exclude_clause
70 else:
71 # This means that there were no tags (wildcard or specific) to
72 # search for (which is allowed, even if it's a bit weird).
73
74=== modified file 'lib/lp/bugs/model/tests/test_bugtask.py'
75--- lib/lp/bugs/model/tests/test_bugtask.py 2010-11-12 03:58:03 +0000
76+++ lib/lp/bugs/model/tests/test_bugtask.py 2011-01-14 00:41:54 +0000
77@@ -216,300 +216,354 @@
78 # tag. Should be the same for an `any` query or an `all`
79 # query.
80 expected_query = (
81- """BugTask.bug IN
82- (SELECT bug FROM BugTag
83- WHERE tag = 'fred')""")
84- self.assertEqualIgnoringWhitespace(
85- self.searchClause(any(u'fred')),
86- expected_query)
87- self.assertEqualIgnoringWhitespace(
88- self.searchClause(all(u'fred')),
89- expected_query)
90+ """EXISTS
91+ (SELECT TRUE FROM BugTag
92+ WHERE BugTag.bug = Bug.id
93+ AND BugTag.tag = 'fred')""")
94+ self.assertEqualIgnoringWhitespace(
95+ expected_query,
96+ self.searchClause(any(u'fred')))
97+ self.assertEqualIgnoringWhitespace(
98+ expected_query,
99+ self.searchClause(all(u'fred')))
100
101 def test_single_tag_absence(self):
102 # The WHERE clause to test for the absence of a single
103 # tag. Should be the same for an `any` query or an `all`
104 # query.
105 expected_query = (
106- """BugTask.bug NOT IN
107- (SELECT bug FROM BugTag
108- WHERE tag = 'fred')""")
109- self.assertEqualIgnoringWhitespace(
110- self.searchClause(any(u'-fred')),
111- expected_query)
112- self.assertEqualIgnoringWhitespace(
113- self.searchClause(all(u'-fred')),
114- expected_query)
115+ """NOT EXISTS
116+ (SELECT TRUE FROM BugTag
117+ WHERE BugTag.bug = Bug.id
118+ AND BugTag.tag = 'fred')""")
119+ self.assertEqualIgnoringWhitespace(
120+ expected_query,
121+ self.searchClause(any(u'-fred')))
122+ self.assertEqualIgnoringWhitespace(
123+ expected_query,
124+ self.searchClause(all(u'-fred')))
125
126 def test_tag_presence(self):
127 # The WHERE clause to test for the presence of tags. Should be
128 # the same for an `any` query or an `all` query.
129 expected_query = (
130- """BugTask.bug IN
131- (SELECT bug FROM BugTag)""")
132- self.assertEqualIgnoringWhitespace(
133- self.searchClause(any(u'*')),
134- expected_query)
135- self.assertEqualIgnoringWhitespace(
136- self.searchClause(all(u'*')),
137- expected_query)
138+ """EXISTS
139+ (SELECT TRUE FROM BugTag
140+ WHERE BugTag.bug = Bug.id)""")
141+ self.assertEqualIgnoringWhitespace(
142+ expected_query,
143+ self.searchClause(any(u'*')))
144+ self.assertEqualIgnoringWhitespace(
145+ expected_query,
146+ self.searchClause(all(u'*')))
147
148 def test_tag_absence(self):
149 # The WHERE clause to test for the absence of tags. Should be
150 # the same for an `any` query or an `all` query.
151 expected_query = (
152- """BugTask.bug NOT IN
153- (SELECT bug FROM BugTag)""")
154- self.assertEqualIgnoringWhitespace(
155- self.searchClause(any(u'-*')),
156- expected_query)
157- self.assertEqualIgnoringWhitespace(
158- self.searchClause(all(u'-*')),
159- expected_query)
160+ """NOT EXISTS
161+ (SELECT TRUE FROM BugTag
162+ WHERE BugTag.bug = Bug.id)""")
163+ self.assertEqualIgnoringWhitespace(
164+ expected_query,
165+ self.searchClause(any(u'-*')))
166+ self.assertEqualIgnoringWhitespace(
167+ expected_query,
168+ self.searchClause(all(u'-*')))
169
170 def test_multiple_tag_presence_any(self):
171 # The WHERE clause to test for the presence of *any* of
172 # several tags.
173 self.assertEqualIgnoringWhitespace(
174- self.searchClause(any(u'fred', u'bob')),
175- """BugTask.bug IN
176- (SELECT bug FROM BugTag
177- WHERE tag = 'bob'
178+ """EXISTS
179+ (SELECT TRUE FROM BugTag
180+ WHERE BugTag.bug = Bug.id
181+ AND BugTag.tag = 'bob'
182 UNION
183- SELECT bug FROM BugTag
184- WHERE tag = 'fred')""")
185+ SELECT TRUE FROM BugTag
186+ WHERE BugTag.bug = Bug.id
187+ AND BugTag.tag = 'fred')""",
188+ self.searchClause(any(u'fred', u'bob')))
189 # In an `any` query, a positive wildcard is dominant over
190 # other positive tags because "bugs with one or more tags" is
191 # a superset of "bugs with a specific tag".
192 self.assertEqualIgnoringWhitespace(
193- self.searchClause(any(u'fred', u'*')),
194- """BugTask.bug IN
195- (SELECT bug FROM BugTag)""")
196+ """EXISTS
197+ (SELECT TRUE FROM BugTag
198+ WHERE BugTag.bug = Bug.id)""",
199+ self.searchClause(any(u'fred', u'*')))
200
201 def test_multiple_tag_absence_any(self):
202 # The WHERE clause to test for the absence of *any* of several
203 # tags.
204 self.assertEqualIgnoringWhitespace(
205- self.searchClause(any(u'-fred', u'-bob')),
206- """BugTask.bug NOT IN
207- (SELECT bug FROM BugTag
208- WHERE tag = 'bob'
209+ """NOT EXISTS
210+ (SELECT TRUE FROM BugTag
211+ WHERE BugTag.bug = Bug.id
212+ AND BugTag.tag = 'bob'
213 INTERSECT
214- SELECT bug FROM BugTag
215- WHERE tag = 'fred')""")
216+ SELECT TRUE FROM BugTag
217+ WHERE BugTag.bug = Bug.id
218+ AND BugTag.tag = 'fred')""",
219+ self.searchClause(any(u'-fred', u'-bob')))
220 # In an `any` query, a negative wildcard is superfluous in the
221 # presence of other negative tags because "bugs without a
222 # specific tag" is a superset of "bugs without any tags".
223 self.assertEqualIgnoringWhitespace(
224- self.searchClause(any(u'-fred', u'-*')),
225- """BugTask.bug NOT IN
226- (SELECT bug FROM BugTag
227- WHERE tag = 'fred')""")
228+ """NOT EXISTS
229+ (SELECT TRUE FROM BugTag
230+ WHERE BugTag.bug = Bug.id
231+ AND BugTag.tag = 'fred')""",
232+ self.searchClause(any(u'-fred', u'-*')))
233
234 def test_multiple_tag_presence_all(self):
235 # The WHERE clause to test for the presence of *all* specified
236 # tags.
237 self.assertEqualIgnoringWhitespace(
238- self.searchClause(all(u'fred', u'bob')),
239- """BugTask.bug IN
240- (SELECT bug FROM BugTag
241- WHERE tag = 'bob'
242+ """EXISTS
243+ (SELECT TRUE FROM BugTag
244+ WHERE BugTag.bug = Bug.id
245+ AND BugTag.tag = 'bob'
246 INTERSECT
247- SELECT bug FROM BugTag
248- WHERE tag = 'fred')""")
249+ SELECT TRUE FROM BugTag
250+ WHERE BugTag.bug = Bug.id
251+ AND BugTag.tag = 'fred')""",
252+ self.searchClause(all(u'fred', u'bob')))
253 # In an `all` query, a positive wildcard is superfluous in the
254 # presence of other positive tags because "bugs with a
255 # specific tag" is a subset of (i.e. more specific than) "bugs
256 # with one or more tags".
257 self.assertEqualIgnoringWhitespace(
258- self.searchClause(all(u'fred', u'*')),
259- """BugTask.bug IN
260- (SELECT bug FROM BugTag
261- WHERE tag = 'fred')""")
262+ """EXISTS
263+ (SELECT TRUE FROM BugTag
264+ WHERE BugTag.bug = Bug.id
265+ AND BugTag.tag = 'fred')""",
266+ self.searchClause(all(u'fred', u'*')))
267
268 def test_multiple_tag_absence_all(self):
269 # The WHERE clause to test for the absence of all specified
270 # tags.
271 self.assertEqualIgnoringWhitespace(
272- self.searchClause(all(u'-fred', u'-bob')),
273- """BugTask.bug NOT IN
274- (SELECT bug FROM BugTag
275- WHERE tag = 'bob'
276+ """NOT EXISTS
277+ (SELECT TRUE FROM BugTag
278+ WHERE BugTag.bug = Bug.id
279+ AND BugTag.tag = 'bob'
280 UNION
281- SELECT bug FROM BugTag
282- WHERE tag = 'fred')""")
283+ SELECT TRUE FROM BugTag
284+ WHERE BugTag.bug = Bug.id
285+ AND BugTag.tag = 'fred')""",
286+ self.searchClause(all(u'-fred', u'-bob')))
287 # In an `all` query, a negative wildcard is dominant over
288 # other negative tags because "bugs without any tags" is a
289 # subset of (i.e. more specific than) "bugs without a specific
290 # tag".
291 self.assertEqualIgnoringWhitespace(
292- self.searchClause(all(u'-fred', u'-*')),
293- """BugTask.bug NOT IN
294- (SELECT bug FROM BugTag)""")
295+ """NOT EXISTS
296+ (SELECT TRUE FROM BugTag
297+ WHERE BugTag.bug = Bug.id)""",
298+ self.searchClause(all(u'-fred', u'-*')))
299
300 def test_mixed_tags_any(self):
301 # The WHERE clause to test for the presence of one or more
302 # specific tags or the absence of one or more other specific
303 # tags.
304 self.assertEqualIgnoringWhitespace(
305- self.searchClause(any(u'fred', u'-bob')),
306- """(BugTask.bug IN
307- (SELECT bug FROM BugTag
308- WHERE tag = 'fred')
309- OR BugTask.bug NOT IN
310- (SELECT bug FROM BugTag
311- WHERE tag = 'bob'))""")
312+ """(EXISTS
313+ (SELECT TRUE FROM BugTag
314+ WHERE BugTag.bug = Bug.id
315+ AND BugTag.tag = 'fred')
316+ OR NOT EXISTS
317+ (SELECT TRUE FROM BugTag
318+ WHERE BugTag.bug = Bug.id
319+ AND BugTag.tag = 'bob'))""",
320+ self.searchClause(any(u'fred', u'-bob')))
321 self.assertEqualIgnoringWhitespace(
322- self.searchClause(any(u'fred', u'-bob', u'eric', u'-harry')),
323- """(BugTask.bug IN
324- (SELECT bug FROM BugTag
325- WHERE tag = 'eric'
326+ """(EXISTS
327+ (SELECT TRUE FROM BugTag
328+ WHERE BugTag.bug = Bug.id
329+ AND BugTag.tag = 'eric'
330 UNION
331- SELECT bug FROM BugTag
332- WHERE tag = 'fred')
333- OR BugTask.bug NOT IN
334- (SELECT bug FROM BugTag
335- WHERE tag = 'bob'
336+ SELECT TRUE FROM BugTag
337+ WHERE BugTag.bug = Bug.id
338+ AND BugTag.tag = 'fred')
339+ OR NOT EXISTS
340+ (SELECT TRUE FROM BugTag
341+ WHERE BugTag.bug = Bug.id
342+ AND BugTag.tag = 'bob'
343 INTERSECT
344- SELECT bug FROM BugTag
345- WHERE tag = 'harry'))""")
346+ SELECT TRUE FROM BugTag
347+ WHERE BugTag.bug = Bug.id
348+ AND BugTag.tag = 'harry'))""",
349+ self.searchClause(any(u'fred', u'-bob', u'eric', u'-harry')))
350 # The positive wildcard is dominant over other positive tags.
351 self.assertEqualIgnoringWhitespace(
352- self.searchClause(any(u'fred', u'-bob', u'*', u'-harry')),
353- """(BugTask.bug IN
354- (SELECT bug FROM BugTag)
355- OR BugTask.bug NOT IN
356- (SELECT bug FROM BugTag
357- WHERE tag = 'bob'
358+ """(EXISTS
359+ (SELECT TRUE FROM BugTag
360+ WHERE BugTag.bug = Bug.id)
361+ OR NOT EXISTS
362+ (SELECT TRUE FROM BugTag
363+ WHERE BugTag.bug = Bug.id
364+ AND BugTag.tag = 'bob'
365 INTERSECT
366- SELECT bug FROM BugTag
367- WHERE tag = 'harry'))""")
368+ SELECT TRUE FROM BugTag
369+ WHERE BugTag.bug = Bug.id
370+ AND BugTag.tag = 'harry'))""",
371+ self.searchClause(any(u'fred', u'-bob', u'*', u'-harry')))
372 # The negative wildcard is superfluous in the presence of
373 # other negative tags.
374 self.assertEqualIgnoringWhitespace(
375- self.searchClause(any(u'fred', u'-bob', u'eric', u'-*')),
376- """(BugTask.bug IN
377- (SELECT bug FROM BugTag
378- WHERE tag = 'eric'
379+ """(EXISTS
380+ (SELECT TRUE FROM BugTag
381+ WHERE BugTag.bug = Bug.id
382+ AND BugTag.tag = 'eric'
383 UNION
384- SELECT bug FROM BugTag
385- WHERE tag = 'fred')
386- OR BugTask.bug NOT IN
387- (SELECT bug FROM BugTag
388- WHERE tag = 'bob'))""")
389+ SELECT TRUE FROM BugTag
390+ WHERE BugTag.bug = Bug.id
391+ AND BugTag.tag = 'fred')
392+ OR NOT EXISTS
393+ (SELECT TRUE FROM BugTag
394+ WHERE BugTag.bug = Bug.id
395+ AND BugTag.tag = 'bob'))""",
396+ self.searchClause(any(u'fred', u'-bob', u'eric', u'-*')))
397 # The negative wildcard is not superfluous in the absence of
398 # other negative tags.
399 self.assertEqualIgnoringWhitespace(
400- self.searchClause(any(u'fred', u'-*', u'eric')),
401- """(BugTask.bug IN
402- (SELECT bug FROM BugTag
403- WHERE tag = 'eric'
404+ """(EXISTS
405+ (SELECT TRUE FROM BugTag
406+ WHERE BugTag.bug = Bug.id
407+ AND BugTag.tag = 'eric'
408 UNION
409- SELECT bug FROM BugTag
410- WHERE tag = 'fred')
411- OR BugTask.bug NOT IN
412- (SELECT bug FROM BugTag))""")
413+ SELECT TRUE FROM BugTag
414+ WHERE BugTag.bug = Bug.id
415+ AND BugTag.tag = 'fred')
416+ OR NOT EXISTS
417+ (SELECT TRUE FROM BugTag
418+ WHERE BugTag.bug = Bug.id))""",
419+ self.searchClause(any(u'fred', u'-*', u'eric')))
420 # The positive wildcard is dominant over other positive tags,
421 # and the negative wildcard is superfluous in the presence of
422 # other negative tags.
423 self.assertEqualIgnoringWhitespace(
424- self.searchClause(any(u'fred', u'-*', u'*', u'-harry')),
425- """(BugTask.bug IN
426- (SELECT bug FROM BugTag)
427- OR BugTask.bug NOT IN
428- (SELECT bug FROM BugTag
429- WHERE tag = 'harry'))""")
430+ """(EXISTS
431+ (SELECT TRUE FROM BugTag
432+ WHERE BugTag.bug = Bug.id)
433+ OR NOT EXISTS
434+ (SELECT TRUE FROM BugTag
435+ WHERE BugTag.bug = Bug.id
436+ AND BugTag.tag = 'harry'))""",
437+ self.searchClause(any(u'fred', u'-*', u'*', u'-harry')))
438
439 def test_mixed_tags_all(self):
440 # The WHERE clause to test for the presence of one or more
441 # specific tags and the absence of one or more other specific
442 # tags.
443 self.assertEqualIgnoringWhitespace(
444- self.searchClause(all(u'fred', u'-bob')),
445- """(BugTask.bug IN
446- (SELECT bug FROM BugTag
447- WHERE tag = 'fred')
448- AND BugTask.bug NOT IN
449- (SELECT bug FROM BugTag
450- WHERE tag = 'bob'))""")
451+ """(EXISTS
452+ (SELECT TRUE FROM BugTag
453+ WHERE BugTag.bug = Bug.id
454+ AND BugTag.tag = 'fred')
455+ AND NOT EXISTS
456+ (SELECT TRUE FROM BugTag
457+ WHERE BugTag.bug = Bug.id
458+ AND BugTag.tag = 'bob'))""",
459+ self.searchClause(all(u'fred', u'-bob')))
460 self.assertEqualIgnoringWhitespace(
461- self.searchClause(all(u'fred', u'-bob', u'eric', u'-harry')),
462- """(BugTask.bug IN
463- (SELECT bug FROM BugTag
464- WHERE tag = 'eric'
465+ """(EXISTS
466+ (SELECT TRUE FROM BugTag
467+ WHERE BugTag.bug = Bug.id
468+ AND BugTag.tag = 'eric'
469 INTERSECT
470- SELECT bug FROM BugTag
471- WHERE tag = 'fred')
472- AND BugTask.bug NOT IN
473- (SELECT bug FROM BugTag
474- WHERE tag = 'bob'
475+ SELECT TRUE FROM BugTag
476+ WHERE BugTag.bug = Bug.id
477+ AND BugTag.tag = 'fred')
478+ AND NOT EXISTS
479+ (SELECT TRUE FROM BugTag
480+ WHERE BugTag.bug = Bug.id
481+ AND BugTag.tag = 'bob'
482 UNION
483- SELECT bug FROM BugTag
484- WHERE tag = 'harry'))""")
485+ SELECT TRUE FROM BugTag
486+ WHERE BugTag.bug = Bug.id
487+ AND BugTag.tag = 'harry'))""",
488+ self.searchClause(all(u'fred', u'-bob', u'eric', u'-harry')))
489 # The positive wildcard is superfluous in the presence of
490 # other positive tags.
491 self.assertEqualIgnoringWhitespace(
492- self.searchClause(all(u'fred', u'-bob', u'*', u'-harry')),
493- """(BugTask.bug IN
494- (SELECT bug FROM BugTag
495- WHERE tag = 'fred')
496- AND BugTask.bug NOT IN
497- (SELECT bug FROM BugTag
498- WHERE tag = 'bob'
499+ """(EXISTS
500+ (SELECT TRUE FROM BugTag
501+ WHERE BugTag.bug = Bug.id
502+ AND BugTag.tag = 'fred')
503+ AND NOT EXISTS
504+ (SELECT TRUE FROM BugTag
505+ WHERE BugTag.bug = Bug.id
506+ AND BugTag.tag = 'bob'
507 UNION
508- SELECT bug FROM BugTag
509- WHERE tag = 'harry'))""")
510+ SELECT TRUE FROM BugTag
511+ WHERE BugTag.bug = Bug.id
512+ AND BugTag.tag = 'harry'))""",
513+ self.searchClause(all(u'fred', u'-bob', u'*', u'-harry')))
514 # The positive wildcard is not superfluous in the absence of
515 # other positive tags.
516 self.assertEqualIgnoringWhitespace(
517- self.searchClause(all(u'-bob', u'*', u'-harry')),
518- """(BugTask.bug IN
519- (SELECT bug FROM BugTag)
520- AND BugTask.bug NOT IN
521- (SELECT bug FROM BugTag
522- WHERE tag = 'bob'
523+ """(EXISTS
524+ (SELECT TRUE FROM BugTag
525+ WHERE BugTag.bug = Bug.id)
526+ AND NOT EXISTS
527+ (SELECT TRUE FROM BugTag
528+ WHERE BugTag.bug = Bug.id
529+ AND BugTag.tag = 'bob'
530 UNION
531- SELECT bug FROM BugTag
532- WHERE tag = 'harry'))""")
533+ SELECT TRUE FROM BugTag
534+ WHERE BugTag.bug = Bug.id
535+ AND BugTag.tag = 'harry'))""",
536+ self.searchClause(all(u'-bob', u'*', u'-harry')))
537 # The negative wildcard is dominant over other negative tags.
538 self.assertEqualIgnoringWhitespace(
539- self.searchClause(all(u'fred', u'-bob', u'eric', u'-*')),
540- """(BugTask.bug IN
541- (SELECT bug FROM BugTag
542- WHERE tag = 'eric'
543+ """(EXISTS
544+ (SELECT TRUE FROM BugTag
545+ WHERE BugTag.bug = Bug.id
546+ AND BugTag.tag = 'eric'
547 INTERSECT
548- SELECT bug FROM BugTag
549- WHERE tag = 'fred')
550- AND BugTask.bug NOT IN
551- (SELECT bug FROM BugTag))""")
552+ SELECT TRUE FROM BugTag
553+ WHERE BugTag.bug = Bug.id
554+ AND BugTag.tag = 'fred')
555+ AND NOT EXISTS
556+ (SELECT TRUE FROM BugTag
557+ WHERE BugTag.bug = Bug.id))""",
558+ self.searchClause(all(u'fred', u'-bob', u'eric', u'-*')))
559 # The positive wildcard is superfluous in the presence of
560 # other positive tags, and the negative wildcard is dominant
561 # over other negative tags.
562 self.assertEqualIgnoringWhitespace(
563- self.searchClause(all(u'fred', u'-*', u'*', u'-harry')),
564- """(BugTask.bug IN
565- (SELECT bug FROM BugTag
566- WHERE tag = 'fred')
567- AND BugTask.bug NOT IN
568- (SELECT bug FROM BugTag))""")
569+ """(EXISTS
570+ (SELECT TRUE FROM BugTag
571+ WHERE BugTag.bug = Bug.id
572+ AND BugTag.tag = 'fred')
573+ AND NOT EXISTS
574+ (SELECT TRUE FROM BugTag
575+ WHERE BugTag.bug = Bug.id))""",
576+ self.searchClause(all(u'fred', u'-*', u'*', u'-harry')))
577
578 def test_mixed_wildcards(self):
579 # The WHERE clause to test for the presence of tags or the
580 # absence of tags.
581 self.assertEqualIgnoringWhitespace(
582- self.searchClause(any(u'*', u'-*')),
583- """(BugTask.bug IN
584- (SELECT bug FROM BugTag)
585- OR BugTask.bug NOT IN
586- (SELECT bug FROM BugTag))""")
587+ """(EXISTS
588+ (SELECT TRUE FROM BugTag
589+ WHERE BugTag.bug = Bug.id)
590+ OR NOT EXISTS
591+ (SELECT TRUE FROM BugTag
592+ WHERE BugTag.bug = Bug.id))""",
593+ self.searchClause(any(u'*', u'-*')))
594 # The WHERE clause to test for the presence of tags and the
595 # absence of tags.
596 self.assertEqualIgnoringWhitespace(
597- self.searchClause(all(u'*', u'-*')),
598- """(BugTask.bug IN
599- (SELECT bug FROM BugTag)
600- AND BugTask.bug NOT IN
601- (SELECT bug FROM BugTag))""")
602+ """(EXISTS
603+ (SELECT TRUE FROM BugTag
604+ WHERE BugTag.bug = Bug.id)
605+ AND NOT EXISTS
606+ (SELECT TRUE FROM BugTag
607+ WHERE BugTag.bug = Bug.id))""",
608+ self.searchClause(all(u'*', u'-*')))
609
610
611 class TestBugTaskHardwareSearch(TestCaseWithFactory):