Debian Codegraph @

tests/queries/test_qs_combinators.py

raw
1 import operator
2 from datetime import datetime
3
4 from django.db import DatabaseError, NotSupportedError, connection
5 from django.db.models import (
6 DateTimeField,
7 Exists,
8 F,
9 IntegerField,
10 OuterRef,
11 Subquery,
12 Transform,
13 Value,
14 )
15 from django.db.models.functions import Cast, Mod
16 from django.test import TestCase, skipIfDBFeature, skipUnlessDBFeature
17 from django.test.utils import CaptureQueriesContext
18
19 from .models import (
20 Annotation,
21 Article,
22 Author,
23 Celebrity,
24 ExtraInfo,
25 Note,
26 Number,
27 ReservedName,
28 Tag,
29 )
30
31
32 @skipUnlessDBFeature("supports_select_union")
33 class QuerySetSetOperationTests(TestCase):
34 @classmethod
35 def setUpTestData(cls):
36 Number.objects.bulk_create(Number(num=i, other_num=10 - i) for i in range(10))
37
38 def assertNumbersEqual(self, queryset, expected_numbers, ordered=True):
39 self.assertQuerySetEqual(
40 queryset, expected_numbers, operator.attrgetter("num"), ordered
41 )
42
43 def test_simple_union(self):
44 qs1 = Number.objects.filter(num__lte=1)
45 qs2 = Number.objects.filter(num__gte=8)
46 qs3 = Number.objects.filter(num=5)
47 self.assertNumbersEqual(qs1.union(qs2, qs3), [0, 1, 5, 8, 9], ordered=False)
48
49 @skipUnlessDBFeature("supports_select_intersection")
50 def test_simple_intersection(self):
51 qs1 = Number.objects.filter(num__lte=5)
52 qs2 = Number.objects.filter(num__gte=5)
53 qs3 = Number.objects.filter(num__gte=4, num__lte=6)
54 self.assertNumbersEqual(qs1.intersection(qs2, qs3), [5], ordered=False)
55
56 @skipUnlessDBFeature("supports_select_intersection")
57 def test_intersection_with_values(self):
58 ReservedName.objects.create(name="a", order=2)
59 qs1 = ReservedName.objects.all()
60 reserved_name = qs1.intersection(qs1).values("name", "order", "id").get()
61 self.assertEqual(reserved_name["name"], "a")
62 self.assertEqual(reserved_name["order"], 2)
63 reserved_name = qs1.intersection(qs1).values_list("name", "order", "id").get()
64 self.assertEqual(reserved_name[:2], ("a", 2))
65
66 @skipUnlessDBFeature("supports_select_difference")
67 def test_simple_difference(self):
68 qs1 = Number.objects.filter(num__lte=5)
69 qs2 = Number.objects.filter(num__lte=4)
70 self.assertNumbersEqual(qs1.difference(qs2), [5], ordered=False)
71
72 def test_union_distinct(self):
73 qs1 = Number.objects.all()
74 qs2 = Number.objects.all()
75 self.assertEqual(len(list(qs1.union(qs2, all=True))), 20)
76 self.assertEqual(len(list(qs1.union(qs2))), 10)
77
78 def test_union_none(self):
79 qs1 = Number.objects.filter(num__lte=1)
80 qs2 = Number.objects.filter(num__gte=8)
81 qs3 = qs1.union(qs2)
82 self.assertSequenceEqual(qs3.none(), [])
83 self.assertNumbersEqual(qs3, [0, 1, 8, 9], ordered=False)
84
85 def test_union_none_slice(self):
86 qs1 = Number.objects.filter(num__lte=0)
87 qs2 = Number.objects.none()
88 qs3 = qs1.union(qs2)
89 self.assertNumbersEqual(qs3[:1], [0])
90
91 def test_union_empty_slice(self):
92 qs = Number.objects.union()
93 self.assertNumbersEqual(qs[:1], [0])
94 qs = Number.objects.union(all=True)
95 self.assertNumbersEqual(qs[:1], [0])
96 self.assertNumbersEqual(qs.order_by("num")[0:], list(range(0, 10)))
97
98 def test_union_all_none_slice(self):
99 qs = Number.objects.filter(id__in=[])
100 with self.assertNumQueries(0):
101 self.assertSequenceEqual(qs.union(qs), [])
102 self.assertSequenceEqual(qs.union(qs)[0:0], [])
103
104 def test_union_empty_filter_slice(self):
105 qs1 = Number.objects.filter(num__lte=0)
106 qs2 = Number.objects.filter(pk__in=[])
107 qs3 = qs1.union(qs2)
108 self.assertNumbersEqual(qs3[:1], [0])
109
110 @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
111 def test_union_slice_compound_empty(self):
112 qs1 = Number.objects.filter(num__lte=0)[:1]
113 qs2 = Number.objects.none()
114 qs3 = qs1.union(qs2)
115 self.assertNumbersEqual(qs3[:1], [0])
116
117 @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
118 def test_union_combined_slice_compound_empty(self):
119 qs1 = Number.objects.filter(num__lte=2)[:3]
120 qs2 = Number.objects.none()
121 qs3 = qs1.union(qs2)
122 self.assertNumbersEqual(qs3.order_by("num")[2:3], [2])
123
124 def test_union_slice_index(self):
125 Celebrity.objects.create(name="Famous")
126 c1 = Celebrity.objects.create(name="Very famous")
127
128 qs1 = Celebrity.objects.filter(name="nonexistent")
129 qs2 = Celebrity.objects.all()
130 combined_qs = qs1.union(qs2).order_by("name")
131 self.assertEqual(combined_qs[1], c1)
132
133 def test_union_order_with_null_first_last(self):
134 Number.objects.filter(other_num=5).update(other_num=None)
135 qs1 = Number.objects.filter(num__lte=1)
136 qs2 = Number.objects.filter(num__gte=2)
137 qs3 = qs1.union(qs2)
138 self.assertSequenceEqual(
139 qs3.order_by(
140 F("other_num").asc(nulls_first=True),
141 ).values_list("other_num", flat=True),
142 [None, 1, 2, 3, 4, 6, 7, 8, 9, 10],
143 )
144 self.assertSequenceEqual(
145 qs3.order_by(
146 F("other_num").asc(nulls_last=True),
147 ).values_list("other_num", flat=True),
148 [1, 2, 3, 4, 6, 7, 8, 9, 10, None],
149 )
150
151 def test_union_nested(self):
152 qs1 = Number.objects.all()
153 qs2 = qs1.union(qs1)
154 self.assertNumbersEqual(
155 qs1.union(qs2),
156 [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
157 ordered=False,
158 )
159
160 @skipUnlessDBFeature("supports_select_intersection")
161 def test_intersection_with_empty_qs(self):
162 qs1 = Number.objects.all()
163 qs2 = Number.objects.none()
164 qs3 = Number.objects.filter(pk__in=[])
165 self.assertEqual(len(qs1.intersection(qs2)), 0)
166 self.assertEqual(len(qs1.intersection(qs3)), 0)
167 self.assertEqual(len(qs2.intersection(qs1)), 0)
168 self.assertEqual(len(qs3.intersection(qs1)), 0)
169 self.assertEqual(len(qs2.intersection(qs2)), 0)
170 self.assertEqual(len(qs3.intersection(qs3)), 0)
171
172 @skipUnlessDBFeature("supports_select_difference")
173 def test_difference_with_empty_qs(self):
174 qs1 = Number.objects.all()
175 qs2 = Number.objects.none()
176 qs3 = Number.objects.filter(pk__in=[])
177 self.assertEqual(len(qs1.difference(qs2)), 10)
178 self.assertEqual(len(qs1.difference(qs3)), 10)
179 self.assertEqual(len(qs2.difference(qs1)), 0)
180 self.assertEqual(len(qs3.difference(qs1)), 0)
181 self.assertEqual(len(qs2.difference(qs2)), 0)
182 self.assertEqual(len(qs3.difference(qs3)), 0)
183
184 @skipUnlessDBFeature("supports_select_difference")
185 def test_difference_with_values(self):
186 ReservedName.objects.create(name="a", order=2)
187 qs1 = ReservedName.objects.all()
188 qs2 = ReservedName.objects.none()
189 reserved_name = qs1.difference(qs2).values("name", "order", "id").get()
190 self.assertEqual(reserved_name["name"], "a")
191 self.assertEqual(reserved_name["order"], 2)
192 reserved_name = qs1.difference(qs2).values_list("name", "order", "id").get()
193 self.assertEqual(reserved_name[:2], ("a", 2))
194
195 def test_union_with_empty_qs(self):
196 qs1 = Number.objects.all()
197 qs2 = Number.objects.none()
198 qs3 = Number.objects.filter(pk__in=[])
199 self.assertEqual(len(qs1.union(qs2)), 10)
200 self.assertEqual(len(qs2.union(qs1)), 10)
201 self.assertEqual(len(qs1.union(qs3)), 10)
202 self.assertEqual(len(qs3.union(qs1)), 10)
203 self.assertEqual(len(qs2.union(qs1, qs1, qs1)), 10)
204 self.assertEqual(len(qs2.union(qs1, qs1, all=True)), 20)
205 self.assertEqual(len(qs2.union(qs2)), 0)
206 self.assertEqual(len(qs3.union(qs3)), 0)
207
208 def test_empty_qs_union_with_ordered_qs(self):
209 qs1 = Number.objects.order_by("num")
210 qs2 = Number.objects.none().union(qs1).order_by("num")
211 self.assertEqual(list(qs1), list(qs2))
212
213 def test_limits(self):
214 qs1 = Number.objects.all()
215 qs2 = Number.objects.all()
216 self.assertEqual(len(list(qs1.union(qs2)[:2])), 2)
217
218 def test_ordering(self):
219 qs1 = Number.objects.filter(num__lte=1)
220 qs2 = Number.objects.filter(num__gte=2, num__lte=3)
221 self.assertNumbersEqual(qs1.union(qs2).order_by("-num"), [3, 2, 1, 0])
222
223 def test_ordering_by_alias(self):
224 qs1 = Number.objects.filter(num__lte=1).values(alias=F("num"))
225 qs2 = Number.objects.filter(num__gte=2, num__lte=3).values(alias=F("num"))
226 self.assertQuerySetEqual(
227 qs1.union(qs2).order_by("-alias"),
228 [3, 2, 1, 0],
229 operator.itemgetter("alias"),
230 )
231
232 def test_ordering_by_f_expression(self):
233 qs1 = Number.objects.filter(num__lte=1)
234 qs2 = Number.objects.filter(num__gte=2, num__lte=3)
235 self.assertNumbersEqual(qs1.union(qs2).order_by(F("num").desc()), [3, 2, 1, 0])
236
237 def test_ordering_by_f_expression_and_alias(self):
238 qs1 = Number.objects.filter(num__lte=1).values(alias=F("other_num"))
239 qs2 = Number.objects.filter(num__gte=2, num__lte=3).values(alias=F("other_num"))
240 self.assertQuerySetEqual(
241 qs1.union(qs2).order_by(F("alias").desc()),
242 [10, 9, 8, 7],
243 operator.itemgetter("alias"),
244 )
245 Number.objects.create(num=-1)
246 self.assertQuerySetEqual(
247 qs1.union(qs2).order_by(F("alias").desc(nulls_last=True)),
248 [10, 9, 8, 7, None],
249 operator.itemgetter("alias"),
250 )
251
252 def test_union_with_values(self):
253 ReservedName.objects.create(name="a", order=2)
254 qs1 = ReservedName.objects.all()
255 reserved_name = qs1.union(qs1).values("name", "order", "id").get()
256 self.assertEqual(reserved_name["name"], "a")
257 self.assertEqual(reserved_name["order"], 2)
258 reserved_name = qs1.union(qs1).values_list("name", "order", "id").get()
259 self.assertEqual(reserved_name[:2], ("a", 2))
260 # List of columns can be changed.
261 reserved_name = qs1.union(qs1).values_list("order").get()
262 self.assertEqual(reserved_name, (2,))
263
264 def test_union_with_two_annotated_values_list(self):
265 qs1 = (
266 Number.objects.filter(num=1)
267 .annotate(
268 count=Value(0, IntegerField()),
269 )
270 .values_list("num", "count")
271 )
272 qs2 = (
273 Number.objects.filter(num=2)
274 .values("pk")
275 .annotate(
276 count=F("num"),
277 )
278 .annotate(
279 num=Value(1, IntegerField()),
280 )
281 .values_list("num", "count")
282 )
283 self.assertCountEqual(qs1.union(qs2), [(1, 0), (1, 2)])
284
285 def test_union_with_field_and_annotation_values(self):
286 qs1 = (
287 Number.objects.filter(num=1)
288 .annotate(
289 zero=Value(0, IntegerField()),
290 )
291 .values_list("num", "zero")
292 )
293 qs2 = (
294 Number.objects.filter(num=2)
295 .annotate(
296 zero=Value(0, IntegerField()),
297 )
298 .values_list("zero", "num")
299 )
300 self.assertCountEqual(qs1.union(qs2), [(1, 0), (0, 2)])
301
302 def test_union_with_extra_and_values_list(self):
303 qs1 = (
304 Number.objects.filter(num=1)
305 .extra(
306 select={"count": 0},
307 )
308 .values_list("num", "count")
309 )
310 qs2 = Number.objects.filter(num=2).extra(select={"count": 1})
311 self.assertCountEqual(qs1.union(qs2), [(1, 0), (2, 1)])
312
313 def test_union_with_values_list_on_annotated_and_unannotated(self):
314 ReservedName.objects.create(name="rn1", order=1)
315 qs1 = Number.objects.annotate(
316 has_reserved_name=Exists(ReservedName.objects.filter(order=OuterRef("num")))
317 ).filter(has_reserved_name=True)
318 qs2 = Number.objects.filter(num=9)
319 self.assertCountEqual(qs1.union(qs2).values_list("num", flat=True), [1, 9])
320
321 def test_union_with_values_list_and_order(self):
322 ReservedName.objects.bulk_create(
323 [
324 ReservedName(name="rn1", order=7),
325 ReservedName(name="rn2", order=5),
326 ReservedName(name="rn0", order=6),
327 ReservedName(name="rn9", order=-1),
328 ]
329 )
330 qs1 = ReservedName.objects.filter(order__gte=6)
331 qs2 = ReservedName.objects.filter(order__lte=5)
332 union_qs = qs1.union(qs2)
333 for qs, expected_result in (
334 # Order by a single column.
335 (union_qs.order_by("-pk").values_list("order", flat=True), [-1, 6, 5, 7]),
336 (union_qs.order_by("pk").values_list("order", flat=True), [7, 5, 6, -1]),
337 (union_qs.values_list("order", flat=True).order_by("-pk"), [-1, 6, 5, 7]),
338 (union_qs.values_list("order", flat=True).order_by("pk"), [7, 5, 6, -1]),
339 # Order by multiple columns.
340 (
341 union_qs.order_by("-name", "pk").values_list("order", flat=True),
342 [-1, 5, 7, 6],
343 ),
344 (
345 union_qs.values_list("order", flat=True).order_by("-name", "pk"),
346 [-1, 5, 7, 6],
347 ),
348 ):
349 with self.subTest(qs=qs):
350 self.assertEqual(list(qs), expected_result)
351
352 def test_union_with_values_list_and_order_on_annotation(self):
353 qs1 = Number.objects.annotate(
354 annotation=Value(-1),
355 multiplier=F("annotation"),
356 ).filter(num__gte=6)
357 qs2 = Number.objects.annotate(
358 annotation=Value(2),
359 multiplier=F("annotation"),
360 ).filter(num__lte=5)
361 self.assertSequenceEqual(
362 qs1.union(qs2).order_by("annotation", "num").values_list("num", flat=True),
363 [6, 7, 8, 9, 0, 1, 2, 3, 4, 5],
364 )
365 self.assertQuerySetEqual(
366 qs1.union(qs2)
367 .order_by(
368 F("annotation") * F("multiplier"),
369 "num",
370 )
371 .values("num"),
372 [6, 7, 8, 9, 0, 1, 2, 3, 4, 5],
373 operator.itemgetter("num"),
374 )
375
376 def test_order_by_annotation_transform(self):
377 class Mod2(Mod, Transform):
378 def __init__(self, expr):
379 super().__init__(expr, 2)
380
381 output_field = IntegerField()
382 output_field.register_lookup(Mod2, "mod2")
383 qs1 = Number.objects.annotate(
384 annotation=Value(1, output_field=output_field),
385 )
386 qs2 = Number.objects.annotate(
387 annotation=Value(2, output_field=output_field),
388 )
389 msg = "Ordering combined queries by transforms is not implemented."
390 with self.assertRaisesMessage(NotImplementedError, msg):
391 list(qs1.union(qs2).order_by("annotation__mod2"))
392
393 def test_union_with_select_related_and_order(self):
394 e1 = ExtraInfo.objects.create(value=7, info="e1")
395 a1 = Author.objects.create(name="a1", num=1, extra=e1)
396 a2 = Author.objects.create(name="a2", num=3, extra=e1)
397 Author.objects.create(name="a3", num=2, extra=e1)
398 base_qs = Author.objects.select_related("extra").order_by()
399 qs1 = base_qs.filter(name="a1")
400 qs2 = base_qs.filter(name="a2")
401 self.assertSequenceEqual(qs1.union(qs2).order_by("pk"), [a1, a2])
402
403 @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
404 def test_union_with_select_related_and_first(self):
405 e1 = ExtraInfo.objects.create(value=7, info="e1")
406 a1 = Author.objects.create(name="a1", num=1, extra=e1)
407 Author.objects.create(name="a2", num=3, extra=e1)
408 base_qs = Author.objects.select_related("extra").order_by()
409 qs1 = base_qs.filter(name="a1")
410 qs2 = base_qs.filter(name="a2")
411 self.assertEqual(qs1.union(qs2).order_by("name").first(), a1)
412
413 def test_union_with_first(self):
414 e1 = ExtraInfo.objects.create(value=7, info="e1")
415 a1 = Author.objects.create(name="a1", num=1, extra=e1)
416 base_qs = Author.objects.order_by()
417 qs1 = base_qs.filter(name="a1")
418 qs2 = base_qs.filter(name="a2")
419 self.assertEqual(qs1.union(qs2).first(), a1)
420
421 def test_union_multiple_models_with_values_list_and_order(self):
422 reserved_name = ReservedName.objects.create(name="rn1", order=0)
423 qs1 = Celebrity.objects.all()
424 qs2 = ReservedName.objects.all()
425 self.assertSequenceEqual(
426 qs1.union(qs2).order_by("name").values_list("pk", flat=True),
427 [reserved_name.pk],
428 )
429
430 def test_union_multiple_models_with_values_list_and_order_by_extra_select(self):
431 reserved_name = ReservedName.objects.create(name="rn1", order=0)
432 qs1 = Celebrity.objects.extra(select={"extra_name": "name"})
433 qs2 = ReservedName.objects.extra(select={"extra_name": "name"})
434 self.assertSequenceEqual(
435 qs1.union(qs2).order_by("extra_name").values_list("pk", flat=True),
436 [reserved_name.pk],
437 )
438
439 def test_union_multiple_models_with_values_list_and_annotations(self):
440 ReservedName.objects.create(name="rn1", order=10)
441 Celebrity.objects.create(name="c1")
442 qs1 = ReservedName.objects.annotate(row_type=Value("rn")).values_list(
443 "name", "order", "row_type"
444 )
445 qs2 = Celebrity.objects.annotate(
446 row_type=Value("cb"), order=Value(-10)
447 ).values_list("name", "order", "row_type")
448 self.assertSequenceEqual(
449 qs1.union(qs2).order_by("order"),
450 [("c1", -10, "cb"), ("rn1", 10, "rn")],
451 )
452
453 def test_union_multiple_models_with_values_list_and_datetime_annotations(self):
454 gen_x = datetime(1966, 6, 6)
455 Article.objects.create(name="Bellatrix", created=gen_x)
456 column_names = ["name", "created", "order"]
457 qs1 = Article.objects.annotate(order=Value(1)).values_list(*column_names)
458
459 gen_y = datetime(1991, 10, 10)
460 ReservedName.objects.create(name="Rigel", order=2)
461 qs2 = ReservedName.objects.annotate(
462 created=Cast(Value(gen_y), DateTimeField())
463 ).values_list(*column_names)
464
465 expected_result = [("Bellatrix", gen_x, 1), ("Rigel", gen_y, 2)]
466 self.assertEqual(list(qs1.union(qs2).order_by("order")), expected_result)
467
468 def test_union_multiple_models_with_values_and_datetime_annotations(self):
469 gen_x = datetime(1966, 6, 6)
470 Article.objects.create(name="Bellatrix", created=gen_x)
471 column_names = ["name", "created", "order"]
472 qs1 = Article.objects.values(*column_names, order=Value(1))
473
474 gen_y = datetime(1991, 10, 10)
475 ReservedName.objects.create(name="Rigel", order=2)
476 qs2 = ReservedName.objects.values(
477 *column_names, created=Cast(Value(gen_y), DateTimeField())
478 )
479
480 expected_result = [
481 {"name": "Bellatrix", "created": gen_x, "order": 1},
482 {"name": "Rigel", "created": gen_y, "order": 2},
483 ]
484 self.assertEqual(list(qs1.union(qs2).order_by("order")), expected_result)
485
486 def test_union_in_subquery(self):
487 ReservedName.objects.bulk_create(
488 [
489 ReservedName(name="rn1", order=8),
490 ReservedName(name="rn2", order=1),
491 ReservedName(name="rn3", order=5),
492 ]
493 )
494 qs1 = Number.objects.filter(num__gt=7, num=OuterRef("order"))
495 qs2 = Number.objects.filter(num__lt=2, num=OuterRef("order"))
496 self.assertCountEqual(
497 ReservedName.objects.annotate(
498 number=Subquery(qs1.union(qs2).values("num")),
499 )
500 .filter(number__isnull=False)
501 .values_list("order", flat=True),
502 [8, 1],
503 )
504
505 @skipUnlessDBFeature("supports_select_intersection")
506 def test_intersection_in_nested_subquery(self):
507 tag = Tag.objects.create(name="tag")
508 note = Note.objects.create(tag=tag)
509 annotation = Annotation.objects.create(tag=tag)
510 tags = Tag.objects.order_by()
511 tags = tags.filter(id=OuterRef("tag_id")).intersection(
512 tags.filter(id=OuterRef(OuterRef("tag_id")))
513 )
514 qs = Note.objects.filter(
515 Exists(
516 Annotation.objects.filter(
517 Exists(tags),
518 notes__in=OuterRef("pk"),
519 )
520 )
521 )
522 self.assertIsNone(qs.first())
523 annotation.notes.add(note)
524 self.assertEqual(qs.first(), note)
525
526 def test_union_in_subquery_related_outerref(self):
527 e1 = ExtraInfo.objects.create(value=7, info="e3")
528 e2 = ExtraInfo.objects.create(value=5, info="e2")
529 e3 = ExtraInfo.objects.create(value=1, info="e1")
530 Author.objects.bulk_create(
531 [
532 Author(name="a1", num=1, extra=e1),
533 Author(name="a2", num=3, extra=e2),
534 Author(name="a3", num=2, extra=e3),
535 ]
536 )
537 qs1 = ExtraInfo.objects.order_by().filter(value=OuterRef("num"))
538 qs2 = ExtraInfo.objects.order_by().filter(value__lt=OuterRef("extra__value"))
539 qs = (
540 Author.objects.annotate(
541 info=Subquery(qs1.union(qs2).values("info")[:1]),
542 )
543 .filter(info__isnull=False)
544 .values_list("name", flat=True)
545 )
546 self.assertCountEqual(qs, ["a1", "a2"])
547 # Combined queries don't mutate.
548 self.assertCountEqual(qs, ["a1", "a2"])
549
550 @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
551 def test_union_in_with_ordering(self):
552 qs1 = Number.objects.filter(num__gt=7).order_by("num")
553 qs2 = Number.objects.filter(num__lt=2).order_by("num")
554 self.assertNumbersEqual(
555 Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
556 [2, 3, 4, 5, 6, 7],
557 ordered=False,
558 )
559
560 @skipUnlessDBFeature(
561 "supports_slicing_ordering_in_compound", "allow_sliced_subqueries_with_in"
562 )
563 def test_union_in_with_ordering_and_slice(self):
564 qs1 = Number.objects.filter(num__gt=7).order_by("num")[:1]
565 qs2 = Number.objects.filter(num__lt=2).order_by("-num")[:1]
566 self.assertNumbersEqual(
567 Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
568 [0, 2, 3, 4, 5, 6, 7, 9],
569 ordered=False,
570 )
571
572 def test_count_union(self):
573 qs1 = Number.objects.filter(num__lte=1).values("num")
574 qs2 = Number.objects.filter(num__gte=2, num__lte=3).values("num")
575 self.assertEqual(qs1.union(qs2).count(), 4)
576
577 def test_count_union_empty_result(self):
578 qs = Number.objects.filter(pk__in=[])
579 self.assertEqual(qs.union(qs).count(), 0)
580
581 def test_count_union_with_select_related(self):
582 e1 = ExtraInfo.objects.create(value=1, info="e1")
583 Author.objects.create(name="a1", num=1, extra=e1)
584 qs = Author.objects.select_related("extra").order_by()
585 self.assertEqual(qs.union(qs).count(), 1)
586
587 @skipUnlessDBFeature("supports_select_difference")
588 def test_count_difference(self):
589 qs1 = Number.objects.filter(num__lt=10)
590 qs2 = Number.objects.filter(num__lt=9)
591 self.assertEqual(qs1.difference(qs2).count(), 1)
592
593 @skipUnlessDBFeature("supports_select_intersection")
594 def test_count_intersection(self):
595 qs1 = Number.objects.filter(num__gte=5)
596 qs2 = Number.objects.filter(num__lte=5)
597 self.assertEqual(qs1.intersection(qs2).count(), 1)
598
599 def test_exists_union(self):
600 qs1 = Number.objects.filter(num__gte=5)
601 qs2 = Number.objects.filter(num__lte=5)
602 with CaptureQueriesContext(connection) as context:
603 self.assertIs(qs1.union(qs2).exists(), True)
604 captured_queries = context.captured_queries
605 self.assertEqual(len(captured_queries), 1)
606 captured_sql = captured_queries[0]["sql"]
607 self.assertNotIn(
608 connection.ops.quote_name(Number._meta.pk.column),
609 captured_sql,
610 )
611 self.assertEqual(
612 captured_sql.count(connection.ops.limit_offset_sql(None, 1)), 1
613 )
614
615 def test_exists_union_empty_result(self):
616 qs = Number.objects.filter(pk__in=[])
617 self.assertIs(qs.union(qs).exists(), False)
618
619 @skipUnlessDBFeature("supports_select_intersection")
620 def test_exists_intersection(self):
621 qs1 = Number.objects.filter(num__gt=5)
622 qs2 = Number.objects.filter(num__lt=5)
623 self.assertIs(qs1.intersection(qs1).exists(), True)
624 self.assertIs(qs1.intersection(qs2).exists(), False)
625
626 @skipUnlessDBFeature("supports_select_difference")
627 def test_exists_difference(self):
628 qs1 = Number.objects.filter(num__gte=5)
629 qs2 = Number.objects.filter(num__gte=3)
630 self.assertIs(qs1.difference(qs2).exists(), False)
631 self.assertIs(qs2.difference(qs1).exists(), True)
632
633 def test_get_union(self):
634 qs = Number.objects.filter(num=2)
635 self.assertEqual(qs.union(qs).get().num, 2)
636
637 @skipUnlessDBFeature("supports_select_difference")
638 def test_get_difference(self):
639 qs1 = Number.objects.all()
640 qs2 = Number.objects.exclude(num=2)
641 self.assertEqual(qs1.difference(qs2).get().num, 2)
642
643 @skipUnlessDBFeature("supports_select_intersection")
644 def test_get_intersection(self):
645 qs1 = Number.objects.all()
646 qs2 = Number.objects.filter(num=2)
647 self.assertEqual(qs1.intersection(qs2).get().num, 2)
648
649 @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
650 def test_ordering_subqueries(self):
651 qs1 = Number.objects.order_by("num")[:2]
652 qs2 = Number.objects.order_by("-num")[:2]
653 self.assertNumbersEqual(qs1.union(qs2).order_by("-num")[:4], [9, 8, 1, 0])
654
655 @skipIfDBFeature("supports_slicing_ordering_in_compound")
656 def test_unsupported_ordering_slicing_raises_db_error(self):
657 qs1 = Number.objects.all()
658 qs2 = Number.objects.all()
659 qs3 = Number.objects.all()
660 msg = "LIMIT/OFFSET not allowed in subqueries of compound statements"
661 with self.assertRaisesMessage(DatabaseError, msg):
662 list(qs1.union(qs2[:10]))
663 msg = "ORDER BY not allowed in subqueries of compound statements"
664 with self.assertRaisesMessage(DatabaseError, msg):
665 list(qs1.order_by("id").union(qs2))
666 with self.assertRaisesMessage(DatabaseError, msg):
667 list(qs1.union(qs2).order_by("id").union(qs3))
668
669 @skipIfDBFeature("supports_select_intersection")
670 def test_unsupported_intersection_raises_db_error(self):
671 qs1 = Number.objects.all()
672 qs2 = Number.objects.all()
673 msg = "intersection is not supported on this database backend"
674 with self.assertRaisesMessage(NotSupportedError, msg):
675 list(qs1.intersection(qs2))
676
677 def test_combining_multiple_models(self):
678 ReservedName.objects.create(name="99 little bugs", order=99)
679 qs1 = Number.objects.filter(num=1).values_list("num", flat=True)
680 qs2 = ReservedName.objects.values_list("order")
681 self.assertEqual(list(qs1.union(qs2).order_by("num")), [1, 99])
682
683 def test_order_raises_on_non_selected_column(self):
684 qs1 = (
685 Number.objects.filter()
686 .annotate(
687 annotation=Value(1, IntegerField()),
688 )
689 .values("annotation", num2=F("num"))
690 )
691 qs2 = Number.objects.filter().values("id", "num")
692 # Should not raise
693 list(qs1.union(qs2).order_by("annotation"))
694 list(qs1.union(qs2).order_by("num2"))
695 msg = "ORDER BY term does not match any column in the result set"
696 # 'id' is not part of the select
697 with self.assertRaisesMessage(DatabaseError, msg):
698 list(qs1.union(qs2).order_by("id"))
699 # 'num' got realiased to num2
700 with self.assertRaisesMessage(DatabaseError, msg):
701 list(qs1.union(qs2).order_by("num"))
702 with self.assertRaisesMessage(DatabaseError, msg):
703 list(qs1.union(qs2).order_by(F("num")))
704 with self.assertRaisesMessage(DatabaseError, msg):
705 list(qs1.union(qs2).order_by(F("num").desc()))
706 # switched order, now 'exists' again:
707 list(qs2.union(qs1).order_by("num"))
708
709 @skipUnlessDBFeature("supports_select_difference", "supports_select_intersection")
710 def test_qs_with_subcompound_qs(self):
711 qs1 = Number.objects.all()
712 qs2 = Number.objects.intersection(Number.objects.filter(num__gt=1))
713 self.assertEqual(qs1.difference(qs2).count(), 2)
714
715 def test_order_by_same_type(self):
716 qs = Number.objects.all()
717 union = qs.union(qs)
718 numbers = list(range(10))
719 self.assertNumbersEqual(union.order_by("num"), numbers)
720 self.assertNumbersEqual(union.order_by("other_num"), reversed(numbers))
721
722 def test_unsupported_operations_on_combined_qs(self):
723 qs = Number.objects.all()
724 msg = "Calling QuerySet.%s() after %s() is not supported."
725 combinators = ["union"]
726 if connection.features.supports_select_difference:
727 combinators.append("difference")
728 if connection.features.supports_select_intersection:
729 combinators.append("intersection")
730 for combinator in combinators:
731 for operation in (
732 "alias",
733 "annotate",
734 "defer",
735 "delete",
736 "distinct",
737 "exclude",
738 "extra",
739 "filter",
740 "only",
741 "prefetch_related",
742 "select_related",
743 "update",
744 ):
745 with self.subTest(combinator=combinator, operation=operation):
746 with self.assertRaisesMessage(
747 NotSupportedError,
748 msg % (operation, combinator),
749 ):
750 getattr(getattr(qs, combinator)(qs), operation)()
751 with self.assertRaisesMessage(
752 NotSupportedError,
753 msg % ("contains", combinator),
754 ):
755 obj = Number.objects.first()
756 getattr(qs, combinator)(qs).contains(obj)
757
758 def test_get_with_filters_unsupported_on_combined_qs(self):
759 qs = Number.objects.all()
760 msg = "Calling QuerySet.get(...) with filters after %s() is not supported."
761 combinators = ["union"]
762 if connection.features.supports_select_difference:
763 combinators.append("difference")
764 if connection.features.supports_select_intersection:
765 combinators.append("intersection")
766 for combinator in combinators:
767 with self.subTest(combinator=combinator):
768 with self.assertRaisesMessage(NotSupportedError, msg % combinator):
769 getattr(qs, combinator)(qs).get(num=2)
770
771 def test_operator_on_combined_qs_error(self):
772 qs = Number.objects.all()
773 msg = "Cannot use %s operator with combined queryset."
774 combinators = ["union"]
775 if connection.features.supports_select_difference:
776 combinators.append("difference")
777 if connection.features.supports_select_intersection:
778 combinators.append("intersection")
779 operators = [
780 ("|", operator.or_),
781 ("&", operator.and_),
782 ("^", operator.xor),
783 ]
784 for combinator in combinators:
785 combined_qs = getattr(qs, combinator)(qs)
786 for operator_, operator_func in operators:
787 with self.subTest(combinator=combinator):
788 with self.assertRaisesMessage(TypeError, msg % operator_):
789 operator_func(qs, combined_qs)
790 with self.assertRaisesMessage(TypeError, msg % operator_):
791 operator_func(combined_qs, qs)