Debian Codegraph @

tests/queries/tests.py

raw
1 import datetime
2 import pickle
3 import sys
4 import unittest
5 from itertools import chain
6 from operator import attrgetter
7
8 from django.core.exceptions import EmptyResultSet, FieldError, FullResultSet
9 from django.db import DEFAULT_DB_ALIAS, connection
10 from django.db.models import CharField, Count, Exists, F, Max, OuterRef, Q
11 from django.db.models.expressions import RawSQL
12 from django.db.models.functions import ExtractYear, Length, LTrim
13 from django.db.models.sql.constants import LOUTER
14 from django.db.models.sql.where import AND, OR, NothingNode, WhereNode
15 from django.test import SimpleTestCase, TestCase, skipUnlessDBFeature
16 from django.test.utils import CaptureQueriesContext, register_lookup
17
18 from .models import (
19 FK1,
20 Annotation,
21 Article,
22 Author,
23 BaseA,
24 BaseUser,
25 Book,
26 CategoryItem,
27 CategoryRelationship,
28 Celebrity,
29 Channel,
30 Chapter,
31 Child,
32 ChildObjectA,
33 Classroom,
34 CommonMixedCaseForeignKeys,
35 Company,
36 Cover,
37 CustomPk,
38 CustomPkTag,
39 DateTimePK,
40 Detail,
41 DumbCategory,
42 Eaten,
43 Employment,
44 ExtraInfo,
45 Fan,
46 Food,
47 Identifier,
48 Individual,
49 Item,
50 Job,
51 JobResponsibilities,
52 Join,
53 LeafA,
54 LeafB,
55 LoopX,
56 LoopZ,
57 ManagedModel,
58 Member,
59 MixedCaseDbColumnCategoryItem,
60 MixedCaseFieldCategoryItem,
61 ModelA,
62 ModelB,
63 ModelC,
64 ModelD,
65 MyObject,
66 NamedCategory,
67 Node,
68 Note,
69 NullableName,
70 Number,
71 ObjectA,
72 ObjectB,
73 ObjectC,
74 OneToOneCategory,
75 Order,
76 OrderItem,
77 Page,
78 Paragraph,
79 Person,
80 Plaything,
81 PointerA,
82 Program,
83 ProxyCategory,
84 ProxyObjectA,
85 ProxyObjectB,
86 Ranking,
87 Related,
88 RelatedIndividual,
89 RelatedObject,
90 Report,
91 ReportComment,
92 ReservedName,
93 Responsibility,
94 School,
95 SharedConnection,
96 SimpleCategory,
97 SingleObject,
98 SpecialCategory,
99 Staff,
100 StaffUser,
101 Student,
102 Tag,
103 Task,
104 Teacher,
105 Ticket21203Child,
106 Ticket21203Parent,
107 Ticket23605A,
108 Ticket23605B,
109 Ticket23605C,
110 TvChef,
111 Valid,
112 X,
113 )
114
115
116 class UnpickleableError(Exception):
117 def __reduce__(self):
118 raise type(self)("Cannot pickle.")
119
120
121 class Queries1Tests(TestCase):
122 @classmethod
123 def setUpTestData(cls):
124 cls.nc1 = generic = NamedCategory.objects.create(name="Generic")
125 cls.t1 = Tag.objects.create(name="t1", category=generic)
126 cls.t2 = Tag.objects.create(name="t2", parent=cls.t1, category=generic)
127 cls.t3 = Tag.objects.create(name="t3", parent=cls.t1)
128 cls.t4 = Tag.objects.create(name="t4", parent=cls.t3)
129 cls.t5 = Tag.objects.create(name="t5", parent=cls.t3)
130
131 cls.n1 = Note.objects.create(note="n1", misc="foo", id=1)
132 cls.n2 = Note.objects.create(note="n2", misc="bar", id=2)
133 cls.n3 = Note.objects.create(note="n3", misc="foo", id=3, negate=False)
134
135 cls.ann1 = Annotation.objects.create(name="a1", tag=cls.t1)
136 cls.ann1.notes.add(cls.n1)
137 ann2 = Annotation.objects.create(name="a2", tag=cls.t4)
138 ann2.notes.add(cls.n2, cls.n3)
139
140 # Create these out of order so that sorting by 'id' will be different to sorting
141 # by 'info'. Helps detect some problems later.
142 cls.e2 = ExtraInfo.objects.create(
143 info="e2", note=cls.n2, value=41, filterable=False
144 )
145 e1 = ExtraInfo.objects.create(info="e1", note=cls.n1, value=42)
146
147 cls.a1 = Author.objects.create(name="a1", num=1001, extra=e1)
148 cls.a2 = Author.objects.create(name="a2", num=2002, extra=e1)
149 cls.a3 = Author.objects.create(name="a3", num=3003, extra=cls.e2)
150 cls.a4 = Author.objects.create(name="a4", num=4004, extra=cls.e2)
151
152 cls.time1 = datetime.datetime(2007, 12, 19, 22, 25, 0)
153 cls.time2 = datetime.datetime(2007, 12, 19, 21, 0, 0)
154 time3 = datetime.datetime(2007, 12, 20, 22, 25, 0)
155 time4 = datetime.datetime(2007, 12, 20, 21, 0, 0)
156 cls.i1 = Item.objects.create(
157 name="one",
158 created=cls.time1,
159 modified=cls.time1,
160 creator=cls.a1,
161 note=cls.n3,
162 )
163 cls.i1.tags.set([cls.t1, cls.t2])
164 cls.i2 = Item.objects.create(
165 name="two", created=cls.time2, creator=cls.a2, note=cls.n2
166 )
167 cls.i2.tags.set([cls.t1, cls.t3])
168 cls.i3 = Item.objects.create(
169 name="three", created=time3, creator=cls.a2, note=cls.n3
170 )
171 cls.i4 = Item.objects.create(
172 name="four", created=time4, creator=cls.a4, note=cls.n3
173 )
174 cls.i4.tags.set([cls.t4])
175
176 cls.r1 = Report.objects.create(name="r1", creator=cls.a1)
177 cls.r2 = Report.objects.create(name="r2", creator=cls.a3)
178 cls.r3 = Report.objects.create(name="r3")
179
180 # Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the Meta.ordering
181 # will be rank3, rank2, rank1.
182 cls.rank1 = Ranking.objects.create(rank=2, author=cls.a2)
183
184 cls.c1 = Cover.objects.create(title="first", item=cls.i4)
185 cls.c2 = Cover.objects.create(title="second", item=cls.i2)
186
187 def test_subquery_condition(self):
188 qs1 = Tag.objects.filter(pk__lte=0)
189 qs2 = Tag.objects.filter(parent__in=qs1)
190 qs3 = Tag.objects.filter(parent__in=qs2)
191 self.assertEqual(qs3.query.subq_aliases, {"T", "U", "V"})
192 self.assertIn("v0", str(qs3.query).lower())
193 qs4 = qs3.filter(parent__in=qs1)
194 self.assertEqual(qs4.query.subq_aliases, {"T", "U", "V"})
195 # It is possible to reuse U for the second subquery, no need to use W.
196 self.assertNotIn("w0", str(qs4.query).lower())
197 # So, 'U0."id"' is referenced in SELECT and WHERE twice.
198 self.assertEqual(str(qs4.query).lower().count("u0."), 4)
199
200 def test_ticket1050(self):
201 self.assertSequenceEqual(
202 Item.objects.filter(tags__isnull=True),
203 [self.i3],
204 )
205 self.assertSequenceEqual(
206 Item.objects.filter(tags__id__isnull=True),
207 [self.i3],
208 )
209
210 def test_ticket1801(self):
211 self.assertSequenceEqual(
212 Author.objects.filter(item=self.i2),
213 [self.a2],
214 )
215 self.assertSequenceEqual(
216 Author.objects.filter(item=self.i3),
217 [self.a2],
218 )
219 self.assertSequenceEqual(
220 Author.objects.filter(item=self.i2) & Author.objects.filter(item=self.i3),
221 [self.a2],
222 )
223
224 def test_ticket2306(self):
225 # Checking that no join types are "left outer" joins.
226 query = Item.objects.filter(tags=self.t2).query
227 self.assertNotIn(LOUTER, [x.join_type for x in query.alias_map.values()])
228
229 self.assertSequenceEqual(
230 Item.objects.filter(Q(tags=self.t1)).order_by("name"),
231 [self.i1, self.i2],
232 )
233 self.assertSequenceEqual(
234 Item.objects.filter(Q(tags=self.t1)).filter(Q(tags=self.t2)),
235 [self.i1],
236 )
237 self.assertSequenceEqual(
238 Item.objects.filter(Q(tags=self.t1)).filter(
239 Q(creator__name="fred") | Q(tags=self.t2)
240 ),
241 [self.i1],
242 )
243
244 # Each filter call is processed "at once" against a single table, so this is
245 # different from the previous example as it tries to find tags that are two
246 # things at once (rather than two tags).
247 self.assertSequenceEqual(
248 Item.objects.filter(Q(tags=self.t1) & Q(tags=self.t2)), []
249 )
250 self.assertSequenceEqual(
251 Item.objects.filter(
252 Q(tags=self.t1), Q(creator__name="fred") | Q(tags=self.t2)
253 ),
254 [],
255 )
256
257 qs = Author.objects.filter(ranking__rank=2, ranking__id=self.rank1.id)
258 self.assertSequenceEqual(list(qs), [self.a2])
259 self.assertEqual(2, qs.query.count_active_tables(), 2)
260 qs = Author.objects.filter(ranking__rank=2).filter(ranking__id=self.rank1.id)
261 self.assertEqual(qs.query.count_active_tables(), 3)
262
263 def test_ticket4464(self):
264 self.assertSequenceEqual(
265 Item.objects.filter(tags=self.t1).filter(tags=self.t2),
266 [self.i1],
267 )
268 self.assertSequenceEqual(
269 Item.objects.filter(tags__in=[self.t1, self.t2])
270 .distinct()
271 .order_by("name"),
272 [self.i1, self.i2],
273 )
274 self.assertSequenceEqual(
275 Item.objects.filter(tags__in=[self.t1, self.t2]).filter(tags=self.t3),
276 [self.i2],
277 )
278
279 # Make sure .distinct() works with slicing (this was broken in Oracle).
280 self.assertSequenceEqual(
281 Item.objects.filter(tags__in=[self.t1, self.t2]).order_by("name")[:3],
282 [self.i1, self.i1, self.i2],
283 )
284 self.assertSequenceEqual(
285 Item.objects.filter(tags__in=[self.t1, self.t2])
286 .distinct()
287 .order_by("name")[:3],
288 [self.i1, self.i2],
289 )
290
291 def test_tickets_2080_3592(self):
292 self.assertSequenceEqual(
293 Author.objects.filter(item__name="one") | Author.objects.filter(name="a3"),
294 [self.a1, self.a3],
295 )
296 self.assertSequenceEqual(
297 Author.objects.filter(Q(item__name="one") | Q(name="a3")),
298 [self.a1, self.a3],
299 )
300 self.assertSequenceEqual(
301 Author.objects.filter(Q(name="a3") | Q(item__name="one")),
302 [self.a1, self.a3],
303 )
304 self.assertSequenceEqual(
305 Author.objects.filter(Q(item__name="three") | Q(report__name="r3")),
306 [self.a2],
307 )
308
309 def test_ticket6074(self):
310 # Merging two empty result sets shouldn't leave a queryset with no constraints
311 # (which would match everything).
312 self.assertSequenceEqual(Author.objects.filter(Q(id__in=[])), [])
313 self.assertSequenceEqual(Author.objects.filter(Q(id__in=[]) | Q(id__in=[])), [])
314
315 def test_tickets_1878_2939(self):
316 self.assertEqual(Item.objects.values("creator").distinct().count(), 3)
317
318 # Create something with a duplicate 'name' so that we can test multi-column
319 # cases (which require some tricky SQL transformations under the covers).
320 xx = Item(name="four", created=self.time1, creator=self.a2, note=self.n1)
321 xx.save()
322 self.assertEqual(
323 Item.objects.exclude(name="two")
324 .values("creator", "name")
325 .distinct()
326 .count(),
327 4,
328 )
329 self.assertEqual(
330 (
331 Item.objects.exclude(name="two")
332 .extra(select={"foo": "%s"}, select_params=(1,))
333 .values("creator", "name", "foo")
334 .distinct()
335 .count()
336 ),
337 4,
338 )
339 self.assertEqual(
340 (
341 Item.objects.exclude(name="two")
342 .extra(select={"foo": "%s"}, select_params=(1,))
343 .values("creator", "name")
344 .distinct()
345 .count()
346 ),
347 4,
348 )
349 xx.delete()
350
351 def test_ticket7323(self):
352 self.assertEqual(Item.objects.values("creator", "name").count(), 4)
353
354 def test_ticket2253(self):
355 q1 = Item.objects.order_by("name")
356 q2 = Item.objects.filter(id=self.i1.id)
357 self.assertSequenceEqual(q1, [self.i4, self.i1, self.i3, self.i2])
358 self.assertSequenceEqual(q2, [self.i1])
359 self.assertSequenceEqual(
360 (q1 | q2).order_by("name"),
361 [self.i4, self.i1, self.i3, self.i2],
362 )
363 self.assertSequenceEqual((q1 & q2).order_by("name"), [self.i1])
364
365 q1 = Item.objects.filter(tags=self.t1)
366 q2 = Item.objects.filter(note=self.n3, tags=self.t2)
367 q3 = Item.objects.filter(creator=self.a4)
368 self.assertSequenceEqual(
369 ((q1 & q2) | q3).order_by("name"),
370 [self.i4, self.i1],
371 )
372
373 def test_order_by_tables(self):
374 q1 = Item.objects.order_by("name")
375 q2 = Item.objects.filter(id=self.i1.id)
376 list(q2)
377 combined_query = (q1 & q2).order_by("name").query
378 self.assertEqual(
379 len(
380 [
381 t
382 for t in combined_query.alias_map
383 if combined_query.alias_refcount[t]
384 ]
385 ),
386 1,
387 )
388
389 def test_order_by_join_unref(self):
390 """
391 This test is related to the above one, testing that there aren't
392 old JOINs in the query.
393 """
394 qs = Celebrity.objects.order_by("greatest_fan__fan_of")
395 self.assertIn("OUTER JOIN", str(qs.query))
396 qs = qs.order_by("id")
397 self.assertNotIn("OUTER JOIN", str(qs.query))
398
399 def test_order_by_related_field_transform(self):
400 extra_12 = ExtraInfo.objects.create(
401 info="extra 12",
402 date=DateTimePK.objects.create(date=datetime.datetime(2021, 12, 10)),
403 )
404 extra_11 = ExtraInfo.objects.create(
405 info="extra 11",
406 date=DateTimePK.objects.create(date=datetime.datetime(2022, 11, 10)),
407 )
408 self.assertSequenceEqual(
409 ExtraInfo.objects.filter(date__isnull=False).order_by("date__month"),
410 [extra_11, extra_12],
411 )
412
413 def test_filter_by_related_field_transform(self):
414 extra_old = ExtraInfo.objects.create(
415 info="extra 12",
416 date=DateTimePK.objects.create(date=datetime.datetime(2020, 12, 10)),
417 )
418 ExtraInfo.objects.create(info="extra 11", date=DateTimePK.objects.create())
419 a5 = Author.objects.create(name="a5", num=5005, extra=extra_old)
420
421 fk_field = ExtraInfo._meta.get_field("date")
422 with register_lookup(fk_field, ExtractYear):
423 self.assertSequenceEqual(
424 ExtraInfo.objects.filter(date__year=2020),
425 [extra_old],
426 )
427 self.assertSequenceEqual(
428 Author.objects.filter(extra__date__year=2020), [a5]
429 )
430
431 def test_filter_by_related_field_nested_transforms(self):
432 extra = ExtraInfo.objects.create(info=" extra")
433 a5 = Author.objects.create(name="a5", num=5005, extra=extra)
434 info_field = ExtraInfo._meta.get_field("info")
435 with register_lookup(info_field, Length), register_lookup(CharField, LTrim):
436 self.assertSequenceEqual(
437 Author.objects.filter(extra__info__ltrim__length=5), [a5]
438 )
439
440 def test_get_clears_ordering(self):
441 """
442 get() should clear ordering for optimization purposes.
443 """
444 with CaptureQueriesContext(connection) as captured_queries:
445 Author.objects.order_by("name").get(pk=self.a1.pk)
446 self.assertNotIn("order by", captured_queries[0]["sql"].lower())
447
448 def test_tickets_4088_4306(self):
449 self.assertSequenceEqual(Report.objects.filter(creator=1001), [self.r1])
450 self.assertSequenceEqual(Report.objects.filter(creator__num=1001), [self.r1])
451 self.assertSequenceEqual(Report.objects.filter(creator__id=1001), [])
452 self.assertSequenceEqual(
453 Report.objects.filter(creator__id=self.a1.id), [self.r1]
454 )
455 self.assertSequenceEqual(Report.objects.filter(creator__name="a1"), [self.r1])
456
457 def test_ticket4510(self):
458 self.assertSequenceEqual(
459 Author.objects.filter(report__name="r1"),
460 [self.a1],
461 )
462
463 def test_ticket7378(self):
464 self.assertSequenceEqual(self.a1.report_set.all(), [self.r1])
465
466 def test_tickets_5324_6704(self):
467 self.assertSequenceEqual(
468 Item.objects.filter(tags__name="t4"),
469 [self.i4],
470 )
471 self.assertSequenceEqual(
472 Item.objects.exclude(tags__name="t4").order_by("name").distinct(),
473 [self.i1, self.i3, self.i2],
474 )
475 self.assertSequenceEqual(
476 Item.objects.exclude(tags__name="t4").order_by("name").distinct().reverse(),
477 [self.i2, self.i3, self.i1],
478 )
479 self.assertSequenceEqual(
480 Author.objects.exclude(item__name="one").distinct().order_by("name"),
481 [self.a2, self.a3, self.a4],
482 )
483
484 # Excluding across a m2m relation when there is more than one related
485 # object associated was problematic.
486 self.assertSequenceEqual(
487 Item.objects.exclude(tags__name="t1").order_by("name"),
488 [self.i4, self.i3],
489 )
490 self.assertSequenceEqual(
491 Item.objects.exclude(tags__name="t1").exclude(tags__name="t4"),
492 [self.i3],
493 )
494
495 # Excluding from a relation that cannot be NULL should not use outer joins.
496 query = Item.objects.exclude(creator__in=[self.a1, self.a2]).query
497 self.assertNotIn(LOUTER, [x.join_type for x in query.alias_map.values()])
498
499 # Similarly, when one of the joins cannot possibly, ever, involve NULL
500 # values (Author -> ExtraInfo, in the following), it should never be
501 # promoted to a left outer join. So the following query should only
502 # involve one "left outer" join (Author -> Item is 0-to-many).
503 qs = Author.objects.filter(id=self.a1.id).filter(
504 Q(extra__note=self.n1) | Q(item__note=self.n3)
505 )
506 self.assertEqual(
507 len(
508 [
509 x
510 for x in qs.query.alias_map.values()
511 if x.join_type == LOUTER and qs.query.alias_refcount[x.table_alias]
512 ]
513 ),
514 1,
515 )
516
517 # The previous changes shouldn't affect nullable foreign key joins.
518 self.assertSequenceEqual(
519 Tag.objects.filter(parent__isnull=True).order_by("name"), [self.t1]
520 )
521 self.assertSequenceEqual(
522 Tag.objects.exclude(parent__isnull=True).order_by("name"),
523 [self.t2, self.t3, self.t4, self.t5],
524 )
525 self.assertSequenceEqual(
526 Tag.objects.exclude(Q(parent__name="t1") | Q(parent__isnull=True)).order_by(
527 "name"
528 ),
529 [self.t4, self.t5],
530 )
531 self.assertSequenceEqual(
532 Tag.objects.exclude(Q(parent__isnull=True) | Q(parent__name="t1")).order_by(
533 "name"
534 ),
535 [self.t4, self.t5],
536 )
537 self.assertSequenceEqual(
538 Tag.objects.exclude(Q(parent__parent__isnull=True)).order_by("name"),
539 [self.t4, self.t5],
540 )
541 self.assertSequenceEqual(
542 Tag.objects.filter(~Q(parent__parent__isnull=True)).order_by("name"),
543 [self.t4, self.t5],
544 )
545
546 def test_ticket2091(self):
547 t = Tag.objects.get(name="t4")
548 self.assertSequenceEqual(Item.objects.filter(tags__in=[t]), [self.i4])
549
550 def test_avoid_infinite_loop_on_too_many_subqueries(self):
551 x = Tag.objects.filter(pk=1)
552 local_recursion_limit = sys.getrecursionlimit() // 16
553 msg = "Maximum recursion depth exceeded: too many subqueries."
554 with self.assertRaisesMessage(RecursionError, msg):
555 for i in range(local_recursion_limit + 2):
556 x = Tag.objects.filter(pk__in=x)
557
558 def test_reasonable_number_of_subq_aliases(self):
559 x = Tag.objects.filter(pk=1)
560 for _ in range(20):
561 x = Tag.objects.filter(pk__in=x)
562 self.assertEqual(
563 x.query.subq_aliases,
564 {
565 "T",
566 "U",
567 "V",
568 "W",
569 "X",
570 "Y",
571 "Z",
572 "AA",
573 "AB",
574 "AC",
575 "AD",
576 "AE",
577 "AF",
578 "AG",
579 "AH",
580 "AI",
581 "AJ",
582 "AK",
583 "AL",
584 "AM",
585 "AN",
586 },
587 )
588
589 def test_heterogeneous_qs_combination(self):
590 # Combining querysets built on different models should behave in a well-defined
591 # fashion. We raise an error.
592 msg = "Cannot combine queries on two different base models."
593 with self.assertRaisesMessage(TypeError, msg):
594 Author.objects.all() & Tag.objects.all()
595 with self.assertRaisesMessage(TypeError, msg):
596 Author.objects.all() | Tag.objects.all()
597
598 def test_ticket3141(self):
599 self.assertEqual(Author.objects.extra(select={"foo": "1"}).count(), 4)
600 self.assertEqual(
601 Author.objects.extra(select={"foo": "%s"}, select_params=(1,)).count(), 4
602 )
603
604 def test_ticket2400(self):
605 self.assertSequenceEqual(
606 Author.objects.filter(item__isnull=True),
607 [self.a3],
608 )
609 self.assertSequenceEqual(
610 Tag.objects.filter(item__isnull=True),
611 [self.t5],
612 )
613
614 def test_ticket2496(self):
615 self.assertSequenceEqual(
616 Item.objects.extra(tables=["queries_author"])
617 .select_related()
618 .order_by("name")[:1],
619 [self.i4],
620 )
621
622 def test_error_raised_on_filter_with_dictionary(self):
623 with self.assertRaisesMessage(FieldError, "Cannot parse keyword query as dict"):
624 Note.objects.filter({"note": "n1", "misc": "foo"})
625
626 def test_tickets_2076_7256(self):
627 # Ordering on related tables should be possible, even if the table is
628 # not otherwise involved.
629 self.assertSequenceEqual(
630 Item.objects.order_by("note__note", "name"),
631 [self.i2, self.i4, self.i1, self.i3],
632 )
633
634 # Ordering on a related field should use the remote model's default
635 # ordering as a final step.
636 self.assertSequenceEqual(
637 Author.objects.order_by("extra", "-name"),
638 [self.a2, self.a1, self.a4, self.a3],
639 )
640
641 # Using remote model default ordering can span multiple models (in this
642 # case, Cover is ordered by Item's default, which uses Note's default).
643 self.assertSequenceEqual(Cover.objects.all(), [self.c1, self.c2])
644
645 # If the remote model does not have a default ordering, we order by its 'id'
646 # field.
647 self.assertSequenceEqual(
648 Item.objects.order_by("creator", "name"),
649 [self.i1, self.i3, self.i2, self.i4],
650 )
651
652 # Ordering by a many-valued attribute (e.g. a many-to-many or reverse
653 # ForeignKey) is legal, but the results might not make sense. That
654 # isn't Django's problem. Garbage in, garbage out.
655 self.assertSequenceEqual(
656 Item.objects.filter(tags__isnull=False).order_by("tags", "id"),
657 [self.i1, self.i2, self.i1, self.i2, self.i4],
658 )
659
660 # If we replace the default ordering, Django adjusts the required
661 # tables automatically. Item normally requires a join with Note to do
662 # the default ordering, but that isn't needed here.
663 qs = Item.objects.order_by("name")
664 self.assertSequenceEqual(qs, [self.i4, self.i1, self.i3, self.i2])
665 self.assertEqual(len(qs.query.alias_map), 1)
666
667 def test_tickets_2874_3002(self):
668 qs = Item.objects.select_related().order_by("note__note", "name")
669 self.assertQuerySetEqual(qs, [self.i2, self.i4, self.i1, self.i3])
670
671 # This is also a good select_related() test because there are multiple
672 # Note entries in the SQL. The two Note items should be different.
673 self.assertEqual(repr(qs[0].note), "<Note: n2>")
674 self.assertEqual(repr(qs[0].creator.extra.note), "<Note: n1>")
675
676 def test_ticket3037(self):
677 self.assertSequenceEqual(
678 Item.objects.filter(
679 Q(creator__name="a3", name="two") | Q(creator__name="a4", name="four")
680 ),
681 [self.i4],
682 )
683
684 def test_tickets_5321_7070(self):
685 # Ordering columns must be included in the output columns. Note that
686 # this means results that might otherwise be distinct are not (if there
687 # are multiple values in the ordering cols), as in this example. This
688 # isn't a bug; it's a warning to be careful with the selection of
689 # ordering columns.
690 self.assertSequenceEqual(
691 Note.objects.values("misc").distinct().order_by("note", "-misc"),
692 [{"misc": "foo"}, {"misc": "bar"}, {"misc": "foo"}],
693 )
694
695 def test_ticket4358(self):
696 # If you don't pass any fields to values(), relation fields are
697 # returned as "foo_id" keys, not "foo". For consistency, you should be
698 # able to pass "foo_id" in the fields list and have it work, too. We
699 # actually allow both "foo" and "foo_id".
700 # The *_id version is returned by default.
701 self.assertIn("note_id", ExtraInfo.objects.values()[0])
702 # You can also pass it in explicitly.
703 self.assertSequenceEqual(
704 ExtraInfo.objects.values("note_id"), [{"note_id": 1}, {"note_id": 2}]
705 )
706 # ...or use the field name.
707 self.assertSequenceEqual(
708 ExtraInfo.objects.values("note"), [{"note": 1}, {"note": 2}]
709 )
710
711 def test_ticket6154(self):
712 # Multiple filter statements are joined using "AND" all the time.
713
714 self.assertSequenceEqual(
715 Author.objects.filter(id=self.a1.id).filter(
716 Q(extra__note=self.n1) | Q(item__note=self.n3)
717 ),
718 [self.a1],
719 )
720 self.assertSequenceEqual(
721 Author.objects.filter(
722 Q(extra__note=self.n1) | Q(item__note=self.n3)
723 ).filter(id=self.a1.id),
724 [self.a1],
725 )
726
727 def test_ticket6981(self):
728 self.assertSequenceEqual(
729 Tag.objects.select_related("parent").order_by("name"),
730 [self.t1, self.t2, self.t3, self.t4, self.t5],
731 )
732
733 def test_ticket9926(self):
734 self.assertSequenceEqual(
735 Tag.objects.select_related("parent", "category").order_by("name"),
736 [self.t1, self.t2, self.t3, self.t4, self.t5],
737 )
738 self.assertSequenceEqual(
739 Tag.objects.select_related("parent", "parent__category").order_by("name"),
740 [self.t1, self.t2, self.t3, self.t4, self.t5],
741 )
742
743 def test_tickets_6180_6203(self):
744 # Dates with limits and/or counts
745 self.assertEqual(Item.objects.count(), 4)
746 self.assertEqual(Item.objects.datetimes("created", "month").count(), 1)
747 self.assertEqual(Item.objects.datetimes("created", "day").count(), 2)
748 self.assertEqual(len(Item.objects.datetimes("created", "day")), 2)
749 self.assertEqual(
750 Item.objects.datetimes("created", "day")[0],
751 datetime.datetime(2007, 12, 19, 0, 0),
752 )
753
754 def test_tickets_7087_12242(self):
755 # Dates with extra select columns
756 self.assertSequenceEqual(
757 Item.objects.datetimes("created", "day").extra(select={"a": 1}),
758 [
759 datetime.datetime(2007, 12, 19, 0, 0),
760 datetime.datetime(2007, 12, 20, 0, 0),
761 ],
762 )
763 self.assertSequenceEqual(
764 Item.objects.extra(select={"a": 1}).datetimes("created", "day"),
765 [
766 datetime.datetime(2007, 12, 19, 0, 0),
767 datetime.datetime(2007, 12, 20, 0, 0),
768 ],
769 )
770
771 name = "one"
772 self.assertSequenceEqual(
773 Item.objects.datetimes("created", "day").extra(
774 where=["name=%s"], params=[name]
775 ),
776 [datetime.datetime(2007, 12, 19, 0, 0)],
777 )
778
779 self.assertSequenceEqual(
780 Item.objects.extra(where=["name=%s"], params=[name]).datetimes(
781 "created", "day"
782 ),
783 [datetime.datetime(2007, 12, 19, 0, 0)],
784 )
785
786 def test_ticket7155(self):
787 # Nullable dates
788 self.assertSequenceEqual(
789 Item.objects.datetimes("modified", "day"),
790 [datetime.datetime(2007, 12, 19, 0, 0)],
791 )
792
793 def test_order_by_rawsql(self):
794 self.assertSequenceEqual(
795 Item.objects.values("note__note").order_by(
796 RawSQL("queries_note.note", ()),
797 "id",
798 ),
799 [
800 {"note__note": "n2"},
801 {"note__note": "n3"},
802 {"note__note": "n3"},
803 {"note__note": "n3"},
804 ],
805 )
806
807 def test_ticket7096(self):
808 # Make sure exclude() with multiple conditions continues to work.
809 self.assertSequenceEqual(
810 Tag.objects.filter(parent=self.t1, name="t3").order_by("name"),
811 [self.t3],
812 )
813 self.assertSequenceEqual(
814 Tag.objects.exclude(parent=self.t1, name="t3").order_by("name"),
815 [self.t1, self.t2, self.t4, self.t5],
816 )
817 self.assertSequenceEqual(
818 Item.objects.exclude(tags__name="t1", name="one")
819 .order_by("name")
820 .distinct(),
821 [self.i4, self.i3, self.i2],
822 )
823 self.assertSequenceEqual(
824 Item.objects.filter(name__in=["three", "four"])
825 .exclude(tags__name="t1")
826 .order_by("name"),
827 [self.i4, self.i3],
828 )
829
830 # More twisted cases, involving nested negations.
831 self.assertSequenceEqual(
832 Item.objects.exclude(~Q(tags__name="t1", name="one")),
833 [self.i1],
834 )
835 self.assertSequenceEqual(
836 Item.objects.filter(~Q(tags__name="t1", name="one"), name="two"),
837 [self.i2],
838 )
839 self.assertSequenceEqual(
840 Item.objects.exclude(~Q(tags__name="t1", name="one"), name="two"),
841 [self.i4, self.i1, self.i3],
842 )
843
844 def test_tickets_7204_7506(self):
845 # Make sure querysets with related fields can be pickled. If this
846 # doesn't crash, it's a Good Thing.
847 pickle.dumps(Item.objects.all())
848
849 def test_ticket7813(self):
850 # We should also be able to pickle things that use select_related().
851 # The only tricky thing here is to ensure that we do the related
852 # selections properly after unpickling.
853 qs = Item.objects.select_related()
854 query = qs.query.get_compiler(qs.db).as_sql()[0]
855 query2 = pickle.loads(pickle.dumps(qs.query))
856 self.assertEqual(query2.get_compiler(qs.db).as_sql()[0], query)
857
858 def test_deferred_load_qs_pickling(self):
859 # Check pickling of deferred-loading querysets
860 qs = Item.objects.defer("name", "creator")
861 q2 = pickle.loads(pickle.dumps(qs))
862 self.assertEqual(list(qs), list(q2))
863 q3 = pickle.loads(pickle.dumps(qs, pickle.HIGHEST_PROTOCOL))
864 self.assertEqual(list(qs), list(q3))
865
866 def test_ticket7277(self):
867 self.assertSequenceEqual(
868 self.n1.annotation_set.filter(
869 Q(tag=self.t5)
870 | Q(tag__children=self.t5)
871 | Q(tag__children__children=self.t5)
872 ),
873 [self.ann1],
874 )
875
876 def test_tickets_7448_7707(self):
877 # Complex objects should be converted to strings before being used in
878 # lookups.
879 self.assertSequenceEqual(
880 Item.objects.filter(created__in=[self.time1, self.time2]),
881 [self.i1, self.i2],
882 )
883
884 def test_ticket7235(self):
885 # An EmptyQuerySet should not raise exceptions if it is filtered.
886 Eaten.objects.create(meal="m")
887 q = Eaten.objects.none()
888 with self.assertNumQueries(0):
889 self.assertSequenceEqual(q.all(), [])
890 self.assertSequenceEqual(q.filter(meal="m"), [])
891 self.assertSequenceEqual(q.exclude(meal="m"), [])
892 self.assertSequenceEqual(q.complex_filter({"pk": 1}), [])
893 self.assertSequenceEqual(q.select_related("food"), [])
894 self.assertSequenceEqual(q.annotate(Count("food")), [])
895 self.assertSequenceEqual(q.order_by("meal", "food"), [])
896 self.assertSequenceEqual(q.distinct(), [])
897 self.assertSequenceEqual(q.extra(select={"foo": "1"}), [])
898 self.assertSequenceEqual(q.reverse(), [])
899 q.query.low_mark = 1
900 msg = "Cannot change a query once a slice has been taken."
901 with self.assertRaisesMessage(TypeError, msg):
902 q.extra(select={"foo": "1"})
903 self.assertSequenceEqual(q.defer("meal"), [])
904 self.assertSequenceEqual(q.only("meal"), [])
905
906 def test_ticket7791(self):
907 # There were "issues" when ordering and distinct-ing on fields related
908 # via ForeignKeys.
909 self.assertEqual(len(Note.objects.order_by("extrainfo__info").distinct()), 3)
910
911 # Pickling of QuerySets using datetimes() should work.
912 qs = Item.objects.datetimes("created", "month")
913 pickle.loads(pickle.dumps(qs))
914
915 def test_ticket9997(self):
916 # If a ValuesList or Values queryset is passed as an inner query, we
917 # make sure it's only requesting a single value and use that as the
918 # thing to select.
919 self.assertSequenceEqual(
920 Tag.objects.filter(
921 name__in=Tag.objects.filter(parent=self.t1).values("name")
922 ),
923 [self.t2, self.t3],
924 )
925
926 def test_ticket9985(self):
927 # qs.values_list(...).values(...) combinations should work.
928 self.assertSequenceEqual(
929 Note.objects.values_list("note", flat=True).values("id").order_by("id"),
930 [{"id": 1}, {"id": 2}, {"id": 3}],
931 )
932 self.assertSequenceEqual(
933 Annotation.objects.filter(
934 notes__in=Note.objects.filter(note="n1")
935 .values_list("note")
936 .values("id")
937 ),
938 [self.ann1],
939 )
940
941 def test_ticket10205(self):
942 # When bailing out early because of an empty "__in" filter, we need
943 # to set things up correctly internally so that subqueries can continue
944 # properly.
945 self.assertEqual(Tag.objects.filter(name__in=()).update(name="foo"), 0)
946
947 def test_ticket10432(self):
948 # Testing an empty "__in" filter with a generator as the value.
949 def f():
950 return iter([])
951
952 n_obj = Note.objects.all()[0]
953
954 def g():
955 yield n_obj.pk
956
957 self.assertSequenceEqual(Note.objects.filter(pk__in=f()), [])
958 self.assertEqual(list(Note.objects.filter(pk__in=g())), [n_obj])
959
960 def test_ticket10742(self):
961 # Queries used in an __in clause don't execute subqueries
962
963 subq = Author.objects.filter(num__lt=3000)
964 qs = Author.objects.filter(pk__in=subq)
965 self.assertSequenceEqual(qs, [self.a1, self.a2])
966
967 # The subquery result cache should not be populated
968 self.assertIsNone(subq._result_cache)
969
970 subq = Author.objects.filter(num__lt=3000)
971 qs = Author.objects.exclude(pk__in=subq)
972 self.assertSequenceEqual(qs, [self.a3, self.a4])
973
974 # The subquery result cache should not be populated
975 self.assertIsNone(subq._result_cache)
976
977 subq = Author.objects.filter(num__lt=3000)
978 self.assertSequenceEqual(
979 Author.objects.filter(Q(pk__in=subq) & Q(name="a1")),
980 [self.a1],
981 )
982
983 # The subquery result cache should not be populated
984 self.assertIsNone(subq._result_cache)
985
986 def test_ticket7076(self):
987 # Excluding shouldn't eliminate NULL entries.
988 self.assertSequenceEqual(
989 Item.objects.exclude(modified=self.time1).order_by("name"),
990 [self.i4, self.i3, self.i2],
991 )
992 self.assertSequenceEqual(
993 Tag.objects.exclude(parent__name=self.t1.name),
994 [self.t1, self.t4, self.t5],
995 )
996
997 def test_ticket7181(self):
998 # Ordering by related tables should accommodate nullable fields (this
999 # test is a little tricky, since NULL ordering is database dependent.
1000 # Instead, we just count the number of results).
1001 self.assertEqual(len(Tag.objects.order_by("parent__name")), 5)
1002
1003 # Empty querysets can be merged with others.
1004 self.assertSequenceEqual(
1005 Note.objects.none() | Note.objects.all(),
1006 [self.n1, self.n2, self.n3],
1007 )
1008 self.assertSequenceEqual(
1009 Note.objects.all() | Note.objects.none(),
1010 [self.n1, self.n2, self.n3],
1011 )
1012 self.assertSequenceEqual(Note.objects.none() & Note.objects.all(), [])
1013 self.assertSequenceEqual(Note.objects.all() & Note.objects.none(), [])
1014
1015 def test_ticket8439(self):
1016 # Complex combinations of conjunctions, disjunctions and nullable
1017 # relations.
1018 self.assertSequenceEqual(
1019 Author.objects.filter(
1020 Q(item__note__extrainfo=self.e2) | Q(report=self.r1, name="xyz")
1021 ),
1022 [self.a2],
1023 )
1024 self.assertSequenceEqual(
1025 Author.objects.filter(
1026 Q(report=self.r1, name="xyz") | Q(item__note__extrainfo=self.e2)
1027 ),
1028 [self.a2],
1029 )
1030 self.assertSequenceEqual(
1031 Annotation.objects.filter(
1032 Q(tag__parent=self.t1) | Q(notes__note="n1", name="a1")
1033 ),
1034 [self.ann1],
1035 )
1036 xx = ExtraInfo.objects.create(info="xx", note=self.n3)
1037 self.assertSequenceEqual(
1038 Note.objects.filter(Q(extrainfo__author=self.a1) | Q(extrainfo=xx)),
1039 [self.n1, self.n3],
1040 )
1041 q = Note.objects.filter(Q(extrainfo__author=self.a1) | Q(extrainfo=xx)).query
1042 self.assertEqual(
1043 len(
1044 [
1045 x
1046 for x in q.alias_map.values()
1047 if x.join_type == LOUTER and q.alias_refcount[x.table_alias]
1048 ]
1049 ),
1050 1,
1051 )
1052
1053 def test_ticket17429(self):
1054 """
1055 Meta.ordering=None works the same as Meta.ordering=[]
1056 """
1057 original_ordering = Tag._meta.ordering
1058 Tag._meta.ordering = None
1059 try:
1060 self.assertCountEqual(
1061 Tag.objects.all(),
1062 [self.t1, self.t2, self.t3, self.t4, self.t5],
1063 )
1064 finally:
1065 Tag._meta.ordering = original_ordering
1066
1067 def test_exclude(self):
1068 self.assertQuerySetEqual(
1069 Item.objects.exclude(tags__name="t4"),
1070 Item.objects.filter(~Q(tags__name="t4")),
1071 )
1072 self.assertQuerySetEqual(
1073 Item.objects.exclude(Q(tags__name="t4") | Q(tags__name="t3")),
1074 Item.objects.filter(~(Q(tags__name="t4") | Q(tags__name="t3"))),
1075 )
1076 self.assertQuerySetEqual(
1077 Item.objects.exclude(Q(tags__name="t4") | ~Q(tags__name="t3")),
1078 Item.objects.filter(~(Q(tags__name="t4") | ~Q(tags__name="t3"))),
1079 )
1080
1081 def test_nested_exclude(self):
1082 self.assertQuerySetEqual(
1083 Item.objects.exclude(~Q(tags__name="t4")),
1084 Item.objects.filter(~~Q(tags__name="t4")),
1085 )
1086
1087 def test_double_exclude(self):
1088 self.assertQuerySetEqual(
1089 Item.objects.filter(Q(tags__name="t4")),
1090 Item.objects.filter(~~Q(tags__name="t4")),
1091 )
1092 self.assertQuerySetEqual(
1093 Item.objects.filter(Q(tags__name="t4")),
1094 Item.objects.filter(~Q(~Q(tags__name="t4"))),
1095 )
1096
1097 def test_exclude_in(self):
1098 self.assertQuerySetEqual(
1099 Item.objects.exclude(Q(tags__name__in=["t4", "t3"])),
1100 Item.objects.filter(~Q(tags__name__in=["t4", "t3"])),
1101 )
1102 self.assertQuerySetEqual(
1103 Item.objects.filter(Q(tags__name__in=["t4", "t3"])),
1104 Item.objects.filter(~~Q(tags__name__in=["t4", "t3"])),
1105 )
1106
1107 def test_ticket_10790_1(self):
1108 # Querying direct fields with isnull should trim the left outer join.
1109 # It also should not create INNER JOIN.
1110 q = Tag.objects.filter(parent__isnull=True)
1111
1112 self.assertSequenceEqual(q, [self.t1])
1113 self.assertNotIn("JOIN", str(q.query))
1114
1115 q = Tag.objects.filter(parent__isnull=False)
1116
1117 self.assertSequenceEqual(q, [self.t2, self.t3, self.t4, self.t5])
1118 self.assertNotIn("JOIN", str(q.query))
1119
1120 q = Tag.objects.exclude(parent__isnull=True)
1121 self.assertSequenceEqual(q, [self.t2, self.t3, self.t4, self.t5])
1122 self.assertNotIn("JOIN", str(q.query))
1123
1124 q = Tag.objects.exclude(parent__isnull=False)
1125 self.assertSequenceEqual(q, [self.t1])
1126 self.assertNotIn("JOIN", str(q.query))
1127
1128 q = Tag.objects.exclude(parent__parent__isnull=False)
1129
1130 self.assertSequenceEqual(q, [self.t1, self.t2, self.t3])
1131 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 1)
1132 self.assertNotIn("INNER JOIN", str(q.query))
1133
1134 def test_ticket_10790_2(self):
1135 # Querying across several tables should strip only the last outer join,
1136 # while preserving the preceding inner joins.
1137 q = Tag.objects.filter(parent__parent__isnull=False)
1138
1139 self.assertSequenceEqual(q, [self.t4, self.t5])
1140 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1141 self.assertEqual(str(q.query).count("INNER JOIN"), 1)
1142
1143 # Querying without isnull should not convert anything to left outer join.
1144 q = Tag.objects.filter(parent__parent=self.t1)
1145 self.assertSequenceEqual(q, [self.t4, self.t5])
1146 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1147 self.assertEqual(str(q.query).count("INNER JOIN"), 1)
1148
1149 def test_ticket_10790_3(self):
1150 # Querying via indirect fields should populate the left outer join
1151 q = NamedCategory.objects.filter(tag__isnull=True)
1152 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 1)
1153 # join to dumbcategory ptr_id
1154 self.assertEqual(str(q.query).count("INNER JOIN"), 1)
1155 self.assertSequenceEqual(q, [])
1156
1157 # Querying across several tables should strip only the last join, while
1158 # preserving the preceding left outer joins.
1159 q = NamedCategory.objects.filter(tag__parent__isnull=True)
1160 self.assertEqual(str(q.query).count("INNER JOIN"), 1)
1161 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 1)
1162 self.assertSequenceEqual(q, [self.nc1])
1163
1164 def test_ticket_10790_4(self):
1165 # Querying across m2m field should not strip the m2m table from join.
1166 q = Author.objects.filter(item__tags__isnull=True)
1167 self.assertSequenceEqual(q, [self.a2, self.a3])
1168 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 2)
1169 self.assertNotIn("INNER JOIN", str(q.query))
1170
1171 q = Author.objects.filter(item__tags__parent__isnull=True)
1172 self.assertSequenceEqual(q, [self.a1, self.a2, self.a2, self.a3])
1173 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 3)
1174 self.assertNotIn("INNER JOIN", str(q.query))
1175
1176 def test_ticket_10790_5(self):
1177 # Querying with isnull=False across m2m field should not create outer joins
1178 q = Author.objects.filter(item__tags__isnull=False)
1179 self.assertSequenceEqual(q, [self.a1, self.a1, self.a2, self.a2, self.a4])
1180 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1181 self.assertEqual(str(q.query).count("INNER JOIN"), 2)
1182
1183 q = Author.objects.filter(item__tags__parent__isnull=False)
1184 self.assertSequenceEqual(q, [self.a1, self.a2, self.a4])
1185 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1186 self.assertEqual(str(q.query).count("INNER JOIN"), 3)
1187
1188 q = Author.objects.filter(item__tags__parent__parent__isnull=False)
1189 self.assertSequenceEqual(q, [self.a4])
1190 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1191 self.assertEqual(str(q.query).count("INNER JOIN"), 4)
1192
1193 def test_ticket_10790_6(self):
1194 # Querying with isnull=True across m2m field should not create inner joins
1195 # and strip last outer join
1196 q = Author.objects.filter(item__tags__parent__parent__isnull=True)
1197 self.assertSequenceEqual(
1198 q,
1199 [self.a1, self.a1, self.a2, self.a2, self.a2, self.a3],
1200 )
1201 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 4)
1202 self.assertEqual(str(q.query).count("INNER JOIN"), 0)
1203
1204 q = Author.objects.filter(item__tags__parent__isnull=True)
1205 self.assertSequenceEqual(q, [self.a1, self.a2, self.a2, self.a3])
1206 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 3)
1207 self.assertEqual(str(q.query).count("INNER JOIN"), 0)
1208
1209 def test_ticket_10790_7(self):
1210 # Reverse querying with isnull should not strip the join
1211 q = Author.objects.filter(item__isnull=True)
1212 self.assertSequenceEqual(q, [self.a3])
1213 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 1)
1214 self.assertEqual(str(q.query).count("INNER JOIN"), 0)
1215
1216 q = Author.objects.filter(item__isnull=False)
1217 self.assertSequenceEqual(q, [self.a1, self.a2, self.a2, self.a4])
1218 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1219 self.assertEqual(str(q.query).count("INNER JOIN"), 1)
1220
1221 def test_ticket_10790_8(self):
1222 # Querying with combined q-objects should also strip the left outer join
1223 q = Tag.objects.filter(Q(parent__isnull=True) | Q(parent=self.t1))
1224 self.assertSequenceEqual(q, [self.t1, self.t2, self.t3])
1225 self.assertEqual(str(q.query).count("LEFT OUTER JOIN"), 0)
1226 self.assertEqual(str(q.query).count("INNER JOIN"), 0)
1227
1228 def test_ticket_10790_combine(self):
1229 # Combining queries should not re-populate the left outer join
1230 q1 = Tag.objects.filter(parent__isnull=True)
1231 q2 = Tag.objects.filter(parent__isnull=False)
1232
1233 q3 = q1 | q2
1234 self.assertSequenceEqual(q3, [self.t1, self.t2, self.t3, self.t4, self.t5])
1235 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 0)
1236 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1237
1238 q3 = q1 & q2
1239 self.assertSequenceEqual(q3, [])
1240 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 0)
1241 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1242
1243 q2 = Tag.objects.filter(parent=self.t1)
1244 q3 = q1 | q2
1245 self.assertSequenceEqual(q3, [self.t1, self.t2, self.t3])
1246 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 0)
1247 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1248
1249 q3 = q2 | q1
1250 self.assertSequenceEqual(q3, [self.t1, self.t2, self.t3])
1251 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 0)
1252 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1253
1254 q1 = Tag.objects.filter(parent__isnull=True)
1255 q2 = Tag.objects.filter(parent__parent__isnull=True)
1256
1257 q3 = q1 | q2
1258 self.assertSequenceEqual(q3, [self.t1, self.t2, self.t3])
1259 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 1)
1260 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1261
1262 q3 = q2 | q1
1263 self.assertSequenceEqual(q3, [self.t1, self.t2, self.t3])
1264 self.assertEqual(str(q3.query).count("LEFT OUTER JOIN"), 1)
1265 self.assertEqual(str(q3.query).count("INNER JOIN"), 0)
1266
1267 def test_ticket19672(self):
1268 self.assertSequenceEqual(
1269 Report.objects.filter(
1270 Q(creator__isnull=False) & ~Q(creator__extra__value=41)
1271 ),
1272 [self.r1],
1273 )
1274
1275 def test_ticket_20250(self):
1276 # A negated Q along with an annotated queryset failed in Django 1.4
1277 qs = Author.objects.annotate(Count("item"))
1278 qs = qs.filter(~Q(extra__value=0)).order_by("name")
1279
1280 self.assertIn("SELECT", str(qs.query))
1281 self.assertSequenceEqual(qs, [self.a1, self.a2, self.a3, self.a4])
1282
1283 def test_lookup_constraint_fielderror(self):
1284 msg = (
1285 "Cannot resolve keyword 'unknown_field' into field. Choices are: "
1286 "annotation, category, category_id, children, id, item, "
1287 "managedmodel, name, note, parent, parent_id"
1288 )
1289 with self.assertRaisesMessage(FieldError, msg):
1290 Tag.objects.filter(unknown_field__name="generic")
1291
1292 def test_common_mixed_case_foreign_keys(self):
1293 """
1294 Valid query should be generated when fields fetched from joined tables
1295 include FKs whose names only differ by case.
1296 """
1297 c1 = SimpleCategory.objects.create(name="c1")
1298 c2 = SimpleCategory.objects.create(name="c2")
1299 c3 = SimpleCategory.objects.create(name="c3")
1300 category = CategoryItem.objects.create(category=c1)
1301 mixed_case_field_category = MixedCaseFieldCategoryItem.objects.create(
1302 CaTeGoRy=c2
1303 )
1304 mixed_case_db_column_category = MixedCaseDbColumnCategoryItem.objects.create(
1305 category=c3
1306 )
1307 CommonMixedCaseForeignKeys.objects.create(
1308 category=category,
1309 mixed_case_field_category=mixed_case_field_category,
1310 mixed_case_db_column_category=mixed_case_db_column_category,
1311 )
1312 qs = CommonMixedCaseForeignKeys.objects.values(
1313 "category",
1314 "mixed_case_field_category",
1315 "mixed_case_db_column_category",
1316 "category__category",
1317 "mixed_case_field_category__CaTeGoRy",
1318 "mixed_case_db_column_category__category",
1319 )
1320 self.assertTrue(qs.first())
1321
1322 def test_excluded_intermediary_m2m_table_joined(self):
1323 self.assertSequenceEqual(
1324 Note.objects.filter(~Q(tag__annotation__name=F("note"))),
1325 [self.n1, self.n2, self.n3],
1326 )
1327 self.assertSequenceEqual(
1328 Note.objects.filter(tag__annotation__name="a1").filter(
1329 ~Q(tag__annotation__name=F("note"))
1330 ),
1331 [],
1332 )
1333
1334 def test_field_with_filterable(self):
1335 self.assertSequenceEqual(
1336 Author.objects.filter(extra=self.e2),
1337 [self.a3, self.a4],
1338 )
1339
1340 def test_negate_field(self):
1341 self.assertSequenceEqual(
1342 Note.objects.filter(negate=True),
1343 [self.n1, self.n2],
1344 )
1345 self.assertSequenceEqual(Note.objects.exclude(negate=True), [self.n3])
1346
1347 def test_combining_does_not_mutate(self):
1348 all_authors = Author.objects.all()
1349 authors_with_report = Author.objects.filter(
1350 Exists(Report.objects.filter(creator__pk=OuterRef("id")))
1351 )
1352 authors_without_report = all_authors.exclude(pk__in=authors_with_report)
1353 items_before = Item.objects.filter(creator__in=authors_without_report)
1354 self.assertCountEqual(items_before, [self.i2, self.i3, self.i4])
1355 # Combining querysets doesn't mutate them.
1356 all_authors | authors_with_report
1357 all_authors & authors_with_report
1358
1359 authors_without_report = all_authors.exclude(pk__in=authors_with_report)
1360 items_after = Item.objects.filter(creator__in=authors_without_report)
1361
1362 self.assertCountEqual(items_after, [self.i2, self.i3, self.i4])
1363 self.assertCountEqual(items_before, items_after)
1364
1365 @skipUnlessDBFeature("supports_select_union")
1366 def test_union_values_subquery(self):
1367 items = Item.objects.filter(creator=OuterRef("pk"))
1368 item_authors = Author.objects.annotate(is_creator=Exists(items)).order_by()
1369 reports = Report.objects.filter(creator=OuterRef("pk"))
1370 report_authors = Author.objects.annotate(is_creator=Exists(reports)).order_by()
1371 all_authors = item_authors.union(report_authors).order_by("is_creator")
1372 self.assertEqual(
1373 list(all_authors.values_list("is_creator", flat=True)), [False, True]
1374 )
1375
1376
1377 class Queries2Tests(TestCase):
1378 @classmethod
1379 def setUpTestData(cls):
1380 cls.num4 = Number.objects.create(num=4)
1381 cls.num8 = Number.objects.create(num=8)
1382 cls.num12 = Number.objects.create(num=12)
1383
1384 def test_ticket4289(self):
1385 # A slight variation on the restricting the filtering choices by the
1386 # lookup constraints.
1387 self.assertSequenceEqual(Number.objects.filter(num__lt=4), [])
1388 self.assertSequenceEqual(Number.objects.filter(num__gt=8, num__lt=12), [])
1389 self.assertSequenceEqual(
1390 Number.objects.filter(num__gt=8, num__lt=13),
1391 [self.num12],
1392 )
1393 self.assertSequenceEqual(
1394 Number.objects.filter(Q(num__lt=4) | Q(num__gt=8, num__lt=12)), []
1395 )
1396 self.assertSequenceEqual(
1397 Number.objects.filter(Q(num__gt=8, num__lt=12) | Q(num__lt=4)), []
1398 )
1399 self.assertSequenceEqual(
1400 Number.objects.filter(Q(num__gt=8) & Q(num__lt=12) | Q(num__lt=4)), []
1401 )
1402 self.assertSequenceEqual(
1403 Number.objects.filter(Q(num__gt=7) & Q(num__lt=12) | Q(num__lt=4)),
1404 [self.num8],
1405 )
1406
1407 def test_ticket12239(self):
1408 # Custom lookups are registered to round float values correctly on gte
1409 # and lt IntegerField queries.
1410 self.assertSequenceEqual(
1411 Number.objects.filter(num__gt=11.9),
1412 [self.num12],
1413 )
1414 self.assertSequenceEqual(Number.objects.filter(num__gt=12), [])
1415 self.assertSequenceEqual(Number.objects.filter(num__gt=12.0), [])
1416 self.assertSequenceEqual(Number.objects.filter(num__gt=12.1), [])
1417 self.assertCountEqual(
1418 Number.objects.filter(num__lt=12),
1419 [self.num4, self.num8],
1420 )
1421 self.assertCountEqual(
1422 Number.objects.filter(num__lt=12.0),
1423 [self.num4, self.num8],
1424 )
1425 self.assertCountEqual(
1426 Number.objects.filter(num__lt=12.1),
1427 [self.num4, self.num8, self.num12],
1428 )
1429 self.assertCountEqual(
1430 Number.objects.filter(num__gte=11.9),
1431 [self.num12],
1432 )
1433 self.assertCountEqual(
1434 Number.objects.filter(num__gte=12),
1435 [self.num12],
1436 )
1437 self.assertCountEqual(
1438 Number.objects.filter(num__gte=12.0),
1439 [self.num12],
1440 )
1441 self.assertSequenceEqual(Number.objects.filter(num__gte=12.1), [])
1442 self.assertSequenceEqual(Number.objects.filter(num__gte=12.9), [])
1443 self.assertCountEqual(
1444 Number.objects.filter(num__lte=11.9),
1445 [self.num4, self.num8],
1446 )
1447 self.assertCountEqual(
1448 Number.objects.filter(num__lte=12),
1449 [self.num4, self.num8, self.num12],
1450 )
1451 self.assertCountEqual(
1452 Number.objects.filter(num__lte=12.0),
1453 [self.num4, self.num8, self.num12],
1454 )
1455 self.assertCountEqual(
1456 Number.objects.filter(num__lte=12.1),
1457 [self.num4, self.num8, self.num12],
1458 )
1459 self.assertCountEqual(
1460 Number.objects.filter(num__lte=12.9),
1461 [self.num4, self.num8, self.num12],
1462 )
1463
1464 def test_ticket7759(self):
1465 # Count should work with a partially read result set.
1466 count = Number.objects.count()
1467 qs = Number.objects.all()
1468
1469 def run():
1470 for obj in qs:
1471 return qs.count() == count
1472
1473 self.assertTrue(run())
1474
1475
1476 class Queries3Tests(TestCase):
1477 def test_ticket7107(self):
1478 # This shouldn't create an infinite loop.
1479 self.assertSequenceEqual(Valid.objects.all(), [])
1480
1481 def test_datetimes_invalid_field(self):
1482 # An error should be raised when QuerySet.datetimes() is passed the
1483 # wrong type of field.
1484 msg = "'name' isn't a DateField, TimeField, or DateTimeField."
1485 with self.assertRaisesMessage(TypeError, msg):
1486 Item.objects.datetimes("name", "month")
1487
1488 def test_ticket22023(self):
1489 with self.assertRaisesMessage(
1490 TypeError, "Cannot call only() after .values() or .values_list()"
1491 ):
1492 Valid.objects.values().only()
1493
1494 with self.assertRaisesMessage(
1495 TypeError, "Cannot call defer() after .values() or .values_list()"
1496 ):
1497 Valid.objects.values().defer()
1498
1499
1500 class Queries4Tests(TestCase):
1501 @classmethod
1502 def setUpTestData(cls):
1503 generic = NamedCategory.objects.create(name="Generic")
1504 cls.t1 = Tag.objects.create(name="t1", category=generic)
1505
1506 n1 = Note.objects.create(note="n1", misc="foo")
1507 n2 = Note.objects.create(note="n2", misc="bar")
1508
1509 e1 = ExtraInfo.objects.create(info="e1", note=n1)
1510 e2 = ExtraInfo.objects.create(info="e2", note=n2)
1511
1512 cls.a1 = Author.objects.create(name="a1", num=1001, extra=e1)
1513 cls.a3 = Author.objects.create(name="a3", num=3003, extra=e2)
1514
1515 cls.r1 = Report.objects.create(name="r1", creator=cls.a1)
1516 cls.r2 = Report.objects.create(name="r2", creator=cls.a3)
1517 cls.r3 = Report.objects.create(name="r3")
1518
1519 cls.i1 = Item.objects.create(
1520 name="i1", created=datetime.datetime.now(), note=n1, creator=cls.a1
1521 )
1522 cls.i2 = Item.objects.create(
1523 name="i2", created=datetime.datetime.now(), note=n1, creator=cls.a3
1524 )
1525
1526 def test_ticket24525(self):
1527 tag = Tag.objects.create()
1528 anth100 = tag.note_set.create(note="ANTH", misc="100")
1529 math101 = tag.note_set.create(note="MATH", misc="101")
1530 s1 = tag.annotation_set.create(name="1")
1531 s2 = tag.annotation_set.create(name="2")
1532 s1.notes.set([math101, anth100])
1533 s2.notes.set([math101])
1534 result = math101.annotation_set.all() & tag.annotation_set.exclude(
1535 notes__in=[anth100]
1536 )
1537 self.assertEqual(list(result), [s2])
1538
1539 def test_ticket11811(self):
1540 unsaved_category = NamedCategory(name="Other")
1541 msg = (
1542 "Unsaved model instance <NamedCategory: Other> cannot be used in an ORM "
1543 "query."
1544 )
1545 with self.assertRaisesMessage(ValueError, msg):
1546 Tag.objects.filter(pk=self.t1.pk).update(category=unsaved_category)
1547
1548 def test_ticket14876(self):
1549 # Note: when combining the query we need to have information available
1550 # about the join type of the trimmed "creator__isnull" join. If we
1551 # don't have that information, then the join is created as INNER JOIN
1552 # and results will be incorrect.
1553 q1 = Report.objects.filter(
1554 Q(creator__isnull=True) | Q(creator__extra__info="e1")
1555 )
1556 q2 = Report.objects.filter(Q(creator__isnull=True)) | Report.objects.filter(
1557 Q(creator__extra__info="e1")
1558 )
1559 self.assertCountEqual(q1, [self.r1, self.r3])
1560 self.assertEqual(str(q1.query), str(q2.query))
1561
1562 q1 = Report.objects.filter(
1563 Q(creator__extra__info="e1") | Q(creator__isnull=True)
1564 )
1565 q2 = Report.objects.filter(
1566 Q(creator__extra__info="e1")
1567 ) | Report.objects.filter(Q(creator__isnull=True))
1568 self.assertCountEqual(q1, [self.r1, self.r3])
1569 self.assertEqual(str(q1.query), str(q2.query))
1570
1571 q1 = Item.objects.filter(
1572 Q(creator=self.a1) | Q(creator__report__name="r1")
1573 ).order_by()
1574 q2 = (
1575 Item.objects.filter(Q(creator=self.a1)).order_by()
1576 | Item.objects.filter(Q(creator__report__name="r1")).order_by()
1577 )
1578 self.assertCountEqual(q1, [self.i1])
1579 self.assertEqual(str(q1.query), str(q2.query))
1580
1581 q1 = Item.objects.filter(
1582 Q(creator__report__name="e1") | Q(creator=self.a1)
1583 ).order_by()
1584 q2 = (
1585 Item.objects.filter(Q(creator__report__name="e1")).order_by()
1586 | Item.objects.filter(Q(creator=self.a1)).order_by()
1587 )
1588 self.assertCountEqual(q1, [self.i1])
1589 self.assertEqual(str(q1.query), str(q2.query))
1590
1591 def test_combine_join_reuse(self):
1592 # Joins having identical connections are correctly recreated in the
1593 # rhs query, in case the query is ORed together (#18748).
1594 Report.objects.create(name="r4", creator=self.a1)
1595 q1 = Author.objects.filter(report__name="r5")
1596 q2 = Author.objects.filter(report__name="r4").filter(report__name="r1")
1597 combined = q1 | q2
1598 self.assertEqual(str(combined.query).count("JOIN"), 2)
1599 self.assertEqual(len(combined), 1)
1600 self.assertEqual(combined[0].name, "a1")
1601
1602 def test_combine_or_filter_reuse(self):
1603 combined = Author.objects.filter(name="a1") | Author.objects.filter(name="a3")
1604 self.assertEqual(combined.get(name="a1"), self.a1)
1605
1606 def test_join_reuse_order(self):
1607 # Join aliases are reused in order. This shouldn't raise AssertionError
1608 # because change_map contains a circular reference (#26522).
1609 s1 = School.objects.create()
1610 s2 = School.objects.create()
1611 s3 = School.objects.create()
1612 t1 = Teacher.objects.create()
1613 otherteachers = Teacher.objects.exclude(pk=t1.pk).exclude(friends=t1)
1614 qs1 = otherteachers.filter(schools=s1).filter(schools=s2)
1615 qs2 = otherteachers.filter(schools=s1).filter(schools=s3)
1616 self.assertSequenceEqual(qs1 | qs2, [])
1617
1618 def test_ticket7095(self):
1619 # Updates that are filtered on the model being updated are somewhat
1620 # tricky in MySQL.
1621 ManagedModel.objects.create(data="mm1", tag=self.t1, public=True)
1622 self.assertEqual(ManagedModel.objects.update(data="mm"), 1)
1623
1624 # A values() or values_list() query across joined models must use outer
1625 # joins appropriately.
1626 # Note: In Oracle, we expect a null CharField to return '' instead of
1627 # None.
1628 if connection.features.interprets_empty_strings_as_nulls:
1629 expected_null_charfield_repr = ""
1630 else:
1631 expected_null_charfield_repr = None
1632 self.assertSequenceEqual(
1633 Report.objects.values_list("creator__extra__info", flat=True).order_by(
1634 "name"
1635 ),
1636 ["e1", "e2", expected_null_charfield_repr],
1637 )
1638
1639 # Similarly for select_related(), joins beyond an initial nullable join
1640 # must use outer joins so that all results are included.
1641 self.assertSequenceEqual(
1642 Report.objects.select_related("creator", "creator__extra").order_by("name"),
1643 [self.r1, self.r2, self.r3],
1644 )
1645
1646 # When there are multiple paths to a table from another table, we have
1647 # to be careful not to accidentally reuse an inappropriate join when
1648 # using select_related(). We used to return the parent's Detail record
1649 # here by mistake.
1650
1651 d1 = Detail.objects.create(data="d1")
1652 d2 = Detail.objects.create(data="d2")
1653 m1 = Member.objects.create(name="m1", details=d1)
1654 m2 = Member.objects.create(name="m2", details=d2)
1655 Child.objects.create(person=m2, parent=m1)
1656 obj = m1.children.select_related("person__details")[0]
1657 self.assertEqual(obj.person.details.data, "d2")
1658
1659 def test_order_by_resetting(self):
1660 # Calling order_by() with no parameters removes any existing ordering on the
1661 # model. But it should still be possible to add new ordering after that.
1662 qs = Author.objects.order_by().order_by("name")
1663 self.assertIn("ORDER BY", qs.query.get_compiler(qs.db).as_sql()[0])
1664
1665 def test_order_by_reverse_fk(self):
1666 # It is possible to order by reverse of foreign key, although that can lead
1667 # to duplicate results.
1668 c1 = SimpleCategory.objects.create(name="category1")
1669 c2 = SimpleCategory.objects.create(name="category2")
1670 CategoryItem.objects.create(category=c1)
1671 CategoryItem.objects.create(category=c2)
1672 CategoryItem.objects.create(category=c1)
1673 self.assertSequenceEqual(
1674 SimpleCategory.objects.order_by("categoryitem", "pk"), [c1, c2, c1]
1675 )
1676
1677 def test_filter_reverse_non_integer_pk(self):
1678 date_obj = DateTimePK.objects.create()
1679 extra_obj = ExtraInfo.objects.create(info="extra", date=date_obj)
1680 self.assertEqual(
1681 DateTimePK.objects.filter(extrainfo=extra_obj).get(),
1682 date_obj,
1683 )
1684
1685 def test_ticket10181(self):
1686 # Avoid raising an EmptyResultSet if an inner query is probably
1687 # empty (and hence, not executed).
1688 self.assertSequenceEqual(
1689 Tag.objects.filter(id__in=Tag.objects.filter(id__in=[])), []
1690 )
1691
1692 def test_ticket15316_filter_false(self):
1693 c1 = SimpleCategory.objects.create(name="category1")
1694 c2 = SpecialCategory.objects.create(
1695 name="named category1", special_name="special1"
1696 )
1697 c3 = SpecialCategory.objects.create(
1698 name="named category2", special_name="special2"
1699 )
1700
1701 CategoryItem.objects.create(category=c1)
1702 ci2 = CategoryItem.objects.create(category=c2)
1703 ci3 = CategoryItem.objects.create(category=c3)
1704
1705 qs = CategoryItem.objects.filter(category__specialcategory__isnull=False)
1706 self.assertEqual(qs.count(), 2)
1707 self.assertCountEqual(qs, [ci2, ci3])
1708
1709 def test_ticket15316_exclude_false(self):
1710 c1 = SimpleCategory.objects.create(name="category1")
1711 c2 = SpecialCategory.objects.create(
1712 name="named category1", special_name="special1"
1713 )
1714 c3 = SpecialCategory.objects.create(
1715 name="named category2", special_name="special2"
1716 )
1717
1718 ci1 = CategoryItem.objects.create(category=c1)
1719 CategoryItem.objects.create(category=c2)
1720 CategoryItem.objects.create(category=c3)
1721
1722 qs = CategoryItem.objects.exclude(category__specialcategory__isnull=False)
1723 self.assertEqual(qs.count(), 1)
1724 self.assertSequenceEqual(qs, [ci1])
1725
1726 def test_ticket15316_filter_true(self):
1727 c1 = SimpleCategory.objects.create(name="category1")
1728 c2 = SpecialCategory.objects.create(
1729 name="named category1", special_name="special1"
1730 )
1731 c3 = SpecialCategory.objects.create(
1732 name="named category2", special_name="special2"
1733 )
1734
1735 ci1 = CategoryItem.objects.create(category=c1)
1736 CategoryItem.objects.create(category=c2)
1737 CategoryItem.objects.create(category=c3)
1738
1739 qs = CategoryItem.objects.filter(category__specialcategory__isnull=True)
1740 self.assertEqual(qs.count(), 1)
1741 self.assertSequenceEqual(qs, [ci1])
1742
1743 def test_ticket15316_exclude_true(self):
1744 c1 = SimpleCategory.objects.create(name="category1")
1745 c2 = SpecialCategory.objects.create(
1746 name="named category1", special_name="special1"
1747 )
1748 c3 = SpecialCategory.objects.create(
1749 name="named category2", special_name="special2"
1750 )
1751
1752 CategoryItem.objects.create(category=c1)
1753 ci2 = CategoryItem.objects.create(category=c2)
1754 ci3 = CategoryItem.objects.create(category=c3)
1755
1756 qs = CategoryItem.objects.exclude(category__specialcategory__isnull=True)
1757 self.assertEqual(qs.count(), 2)
1758 self.assertCountEqual(qs, [ci2, ci3])
1759
1760 def test_ticket15316_one2one_filter_false(self):
1761 c = SimpleCategory.objects.create(name="cat")
1762 c0 = SimpleCategory.objects.create(name="cat0")
1763 c1 = SimpleCategory.objects.create(name="category1")
1764
1765 OneToOneCategory.objects.create(category=c1, new_name="new1")
1766 OneToOneCategory.objects.create(category=c0, new_name="new2")
1767
1768 CategoryItem.objects.create(category=c)
1769 ci2 = CategoryItem.objects.create(category=c0)
1770 ci3 = CategoryItem.objects.create(category=c1)
1771
1772 qs = CategoryItem.objects.filter(
1773 category__onetoonecategory__isnull=False
1774 ).order_by("pk")
1775 self.assertEqual(qs.count(), 2)
1776 self.assertSequenceEqual(qs, [ci2, ci3])
1777
1778 def test_ticket15316_one2one_exclude_false(self):
1779 c = SimpleCategory.objects.create(name="cat")
1780 c0 = SimpleCategory.objects.create(name="cat0")
1781 c1 = SimpleCategory.objects.create(name="category1")
1782
1783 OneToOneCategory.objects.create(category=c1, new_name="new1")
1784 OneToOneCategory.objects.create(category=c0, new_name="new2")
1785
1786 ci1 = CategoryItem.objects.create(category=c)
1787 CategoryItem.objects.create(category=c0)
1788 CategoryItem.objects.create(category=c1)
1789
1790 qs = CategoryItem.objects.exclude(category__onetoonecategory__isnull=False)
1791 self.assertEqual(qs.count(), 1)
1792 self.assertSequenceEqual(qs, [ci1])
1793
1794 def test_ticket15316_one2one_filter_true(self):
1795 c = SimpleCategory.objects.create(name="cat")
1796 c0 = SimpleCategory.objects.create(name="cat0")
1797 c1 = SimpleCategory.objects.create(name="category1")
1798
1799 OneToOneCategory.objects.create(category=c1, new_name="new1")
1800 OneToOneCategory.objects.create(category=c0, new_name="new2")
1801
1802 ci1 = CategoryItem.objects.create(category=c)
1803 CategoryItem.objects.create(category=c0)
1804 CategoryItem.objects.create(category=c1)
1805
1806 qs = CategoryItem.objects.filter(category__onetoonecategory__isnull=True)
1807 self.assertEqual(qs.count(), 1)
1808 self.assertSequenceEqual(qs, [ci1])
1809
1810 def test_ticket15316_one2one_exclude_true(self):
1811 c = SimpleCategory.objects.create(name="cat")
1812 c0 = SimpleCategory.objects.create(name="cat0")
1813 c1 = SimpleCategory.objects.create(name="category1")
1814
1815 OneToOneCategory.objects.create(category=c1, new_name="new1")
1816 OneToOneCategory.objects.create(category=c0, new_name="new2")
1817
1818 CategoryItem.objects.create(category=c)
1819 ci2 = CategoryItem.objects.create(category=c0)
1820 ci3 = CategoryItem.objects.create(category=c1)
1821
1822 qs = CategoryItem.objects.exclude(
1823 category__onetoonecategory__isnull=True
1824 ).order_by("pk")
1825 self.assertEqual(qs.count(), 2)
1826 self.assertSequenceEqual(qs, [ci2, ci3])
1827
1828
1829 class Queries5Tests(TestCase):
1830 @classmethod
1831 def setUpTestData(cls):
1832 # Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the
1833 # Meta.ordering will be rank3, rank2, rank1.
1834 cls.n1 = Note.objects.create(note="n1", misc="foo", id=1)
1835 cls.n2 = Note.objects.create(note="n2", misc="bar", id=2)
1836 e1 = ExtraInfo.objects.create(info="e1", note=cls.n1)
1837 e2 = ExtraInfo.objects.create(info="e2", note=cls.n2)
1838 a1 = Author.objects.create(name="a1", num=1001, extra=e1)
1839 a2 = Author.objects.create(name="a2", num=2002, extra=e1)
1840 a3 = Author.objects.create(name="a3", num=3003, extra=e2)
1841 cls.rank2 = Ranking.objects.create(rank=2, author=a2)
1842 cls.rank1 = Ranking.objects.create(rank=1, author=a3)
1843 cls.rank3 = Ranking.objects.create(rank=3, author=a1)
1844
1845 def test_ordering(self):
1846 # Cross model ordering is possible in Meta, too.
1847 self.assertSequenceEqual(
1848 Ranking.objects.all(),
1849 [self.rank3, self.rank2, self.rank1],
1850 )
1851 self.assertSequenceEqual(
1852 Ranking.objects.order_by("rank"),
1853 [self.rank1, self.rank2, self.rank3],
1854 )
1855
1856 # Ordering of extra() pieces is possible, too and you can mix extra
1857 # fields and model fields in the ordering.
1858 self.assertSequenceEqual(
1859 Ranking.objects.extra(
1860 tables=["django_site"], order_by=["-django_site.id", "rank"]
1861 ),
1862 [self.rank1, self.rank2, self.rank3],
1863 )
1864
1865 sql = "case when %s > 2 then 1 else 0 end" % connection.ops.quote_name("rank")
1866 qs = Ranking.objects.extra(select={"good": sql})
1867 self.assertEqual(
1868 [o.good for o in qs.extra(order_by=("-good",))], [True, False, False]
1869 )
1870 self.assertSequenceEqual(
1871 qs.extra(order_by=("-good", "id")),
1872 [self.rank3, self.rank2, self.rank1],
1873 )
1874
1875 # Despite having some extra aliases in the query, we can still omit
1876 # them in a values() query.
1877 dicts = qs.values("id", "rank").order_by("id")
1878 self.assertEqual([d["rank"] for d in dicts], [2, 1, 3])
1879
1880 def test_ticket7256(self):
1881 # An empty values() call includes all aliases, including those from an
1882 # extra()
1883 sql = "case when %s > 2 then 1 else 0 end" % connection.ops.quote_name("rank")
1884 qs = Ranking.objects.extra(select={"good": sql})
1885 dicts = qs.values().order_by("id")
1886 for d in dicts:
1887 del d["id"]
1888 del d["author_id"]
1889 self.assertEqual(
1890 [sorted(d.items()) for d in dicts],
1891 [
1892 [("good", 0), ("rank", 2)],
1893 [("good", 0), ("rank", 1)],
1894 [("good", 1), ("rank", 3)],
1895 ],
1896 )
1897
1898 def test_ticket7045(self):
1899 # Extra tables used to crash SQL construction on the second use.
1900 qs = Ranking.objects.extra(tables=["django_site"])
1901 qs.query.get_compiler(qs.db).as_sql()
1902 # test passes if this doesn't raise an exception.
1903 qs.query.get_compiler(qs.db).as_sql()
1904
1905 def test_ticket9848(self):
1906 # Make sure that updates which only filter on sub-tables don't
1907 # inadvertently update the wrong records (bug #9848).
1908 author_start = Author.objects.get(name="a1")
1909 ranking_start = Ranking.objects.get(author__name="a1")
1910
1911 # Make sure that the IDs from different tables don't happen to match.
1912 self.assertSequenceEqual(
1913 Ranking.objects.filter(author__name="a1"),
1914 [self.rank3],
1915 )
1916 self.assertEqual(Ranking.objects.filter(author__name="a1").update(rank=4636), 1)
1917
1918 r = Ranking.objects.get(author__name="a1")
1919 self.assertEqual(r.id, ranking_start.id)
1920 self.assertEqual(r.author.id, author_start.id)
1921 self.assertEqual(r.rank, 4636)
1922 r.rank = 3
1923 r.save()
1924 self.assertSequenceEqual(
1925 Ranking.objects.all(),
1926 [self.rank3, self.rank2, self.rank1],
1927 )
1928
1929 def test_ticket5261(self):
1930 # Test different empty excludes.
1931 self.assertSequenceEqual(
1932 Note.objects.exclude(Q()),
1933 [self.n1, self.n2],
1934 )
1935 self.assertSequenceEqual(
1936 Note.objects.filter(~Q()),
1937 [self.n1, self.n2],
1938 )
1939 self.assertSequenceEqual(
1940 Note.objects.filter(~Q() | ~Q()),
1941 [self.n1, self.n2],
1942 )
1943 self.assertSequenceEqual(
1944 Note.objects.exclude(~Q() & ~Q()),
1945 [self.n1, self.n2],
1946 )
1947 self.assertSequenceEqual(
1948 Note.objects.exclude(~Q() ^ ~Q()),
1949 [self.n1, self.n2],
1950 )
1951
1952 def test_extra_select_literal_percent_s(self):
1953 # Allow %%s to escape select clauses
1954 self.assertEqual(Note.objects.extra(select={"foo": "'%%s'"})[0].foo, "%s")
1955 self.assertEqual(
1956 Note.objects.extra(select={"foo": "'%%s bar %%s'"})[0].foo, "%s bar %s"
1957 )
1958 self.assertEqual(
1959 Note.objects.extra(select={"foo": "'bar %%s'"})[0].foo, "bar %s"
1960 )
1961
1962 def test_extra_select_alias_sql_injection(self):
1963 msg = (
1964 "Column aliases cannot contain whitespace characters, hashes, "
1965 "control characters, quotation marks, semicolons, or SQL comments."
1966 )
1967 for crafted_alias in [
1968 """injected_name" from "queries_note"; --""",
1969 # Control characters.
1970 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
1971 ]:
1972 with self.subTest(crafted_alias):
1973 with self.assertRaisesMessage(ValueError, msg):
1974 Note.objects.extra(select={crafted_alias: "1"})
1975
1976 def test_queryset_reuse(self):
1977 # Using querysets doesn't mutate aliases.
1978 authors = Author.objects.filter(Q(name="a1") | Q(name="nonexistent"))
1979 self.assertEqual(Ranking.objects.filter(author__in=authors).get(), self.rank3)
1980 self.assertEqual(authors.count(), 1)
1981
1982 def test_filter_unsaved_object(self):
1983 msg = "Model instances passed to related filters must be saved."
1984 company = Company.objects.create(name="Django")
1985 with self.assertRaisesMessage(ValueError, msg):
1986 Employment.objects.filter(employer=Company(name="unsaved"))
1987 with self.assertRaisesMessage(ValueError, msg):
1988 Employment.objects.filter(employer__in=[company, Company(name="unsaved")])
1989 with self.assertRaisesMessage(ValueError, msg):
1990 StaffUser.objects.filter(staff=Staff(name="unsaved"))
1991
1992
1993 class SelectRelatedTests(TestCase):
1994 def test_tickets_3045_3288(self):
1995 # Once upon a time, select_related() with circular relations would loop
1996 # infinitely if you forgot to specify "depth". Now we set an arbitrary
1997 # default upper bound.
1998 self.assertSequenceEqual(X.objects.all(), [])
1999 self.assertSequenceEqual(X.objects.select_related(), [])
2000
2001
2002 class SubclassFKTests(TestCase):
2003 def test_ticket7778(self):
2004 # Model subclasses could not be deleted if a nullable foreign key
2005 # relates to a model that relates back.
2006
2007 num_celebs = Celebrity.objects.count()
2008 tvc = TvChef.objects.create(name="Huey")
2009 self.assertEqual(Celebrity.objects.count(), num_celebs + 1)
2010 Fan.objects.create(fan_of=tvc)
2011 Fan.objects.create(fan_of=tvc)
2012 tvc.delete()
2013
2014 # The parent object should have been deleted as well.
2015 self.assertEqual(Celebrity.objects.count(), num_celebs)
2016
2017
2018 class CustomPkTests(TestCase):
2019 def test_ticket7371(self):
2020 self.assertQuerySetEqual(Related.objects.order_by("custom"), [])
2021
2022
2023 class NullableRelOrderingTests(TestCase):
2024 def test_ticket10028(self):
2025 # Ordering by model related to nullable relations(!) should use outer
2026 # joins, so that all results are included.
2027 p1 = Plaything.objects.create(name="p1")
2028 self.assertSequenceEqual(Plaything.objects.all(), [p1])
2029
2030 def test_join_already_in_query(self):
2031 # Ordering by model related to nullable relations should not change
2032 # the join type of already existing joins.
2033 Plaything.objects.create(name="p1")
2034 s = SingleObject.objects.create(name="s")
2035 r = RelatedObject.objects.create(single=s, f=1)
2036 p2 = Plaything.objects.create(name="p2", others=r)
2037 qs = Plaything.objects.filter(others__isnull=False).order_by("pk")
2038 self.assertNotIn("JOIN", str(qs.query))
2039 qs = Plaything.objects.filter(others__f__isnull=False).order_by("pk")
2040 self.assertIn("INNER", str(qs.query))
2041 qs = qs.order_by("others__single__name")
2042 # The ordering by others__single__pk will add one new join (to single)
2043 # and that join must be LEFT join. The already existing join to related
2044 # objects must be kept INNER. So, we have both an INNER and a LEFT join
2045 # in the query.
2046 self.assertEqual(str(qs.query).count("LEFT"), 1)
2047 self.assertEqual(str(qs.query).count("INNER"), 1)
2048 self.assertSequenceEqual(qs, [p2])
2049
2050
2051 class DisjunctiveFilterTests(TestCase):
2052 @classmethod
2053 def setUpTestData(cls):
2054 cls.n1 = Note.objects.create(note="n1", misc="foo", id=1)
2055 cls.e1 = ExtraInfo.objects.create(info="e1", note=cls.n1)
2056
2057 def test_ticket7872(self):
2058 # Another variation on the disjunctive filtering theme.
2059
2060 # For the purposes of this regression test, it's important that there is no
2061 # Join object related to the LeafA we create.
2062 l1 = LeafA.objects.create(data="first")
2063 self.assertSequenceEqual(LeafA.objects.all(), [l1])
2064 self.assertSequenceEqual(
2065 LeafA.objects.filter(Q(data="first") | Q(join__b__data="second")),
2066 [l1],
2067 )
2068
2069 def test_ticket8283(self):
2070 # Checking that applying filters after a disjunction works correctly.
2071 self.assertSequenceEqual(
2072 (
2073 ExtraInfo.objects.filter(note=self.n1)
2074 | ExtraInfo.objects.filter(info="e2")
2075 ).filter(note=self.n1),
2076 [self.e1],
2077 )
2078 self.assertSequenceEqual(
2079 (
2080 ExtraInfo.objects.filter(info="e2")
2081 | ExtraInfo.objects.filter(note=self.n1)
2082 ).filter(note=self.n1),
2083 [self.e1],
2084 )
2085
2086
2087 class Queries6Tests(TestCase):
2088 @classmethod
2089 def setUpTestData(cls):
2090 generic = NamedCategory.objects.create(name="Generic")
2091 cls.t1 = Tag.objects.create(name="t1", category=generic)
2092 cls.t2 = Tag.objects.create(name="t2", parent=cls.t1, category=generic)
2093 cls.t3 = Tag.objects.create(name="t3", parent=cls.t1)
2094 cls.t4 = Tag.objects.create(name="t4", parent=cls.t3)
2095 cls.t5 = Tag.objects.create(name="t5", parent=cls.t3)
2096 n1 = Note.objects.create(note="n1", misc="foo", id=1)
2097 cls.ann1 = Annotation.objects.create(name="a1", tag=cls.t1)
2098 cls.ann1.notes.add(n1)
2099 cls.ann2 = Annotation.objects.create(name="a2", tag=cls.t4)
2100
2101 def test_parallel_iterators(self):
2102 # Parallel iterators work.
2103 qs = Tag.objects.all()
2104 i1, i2 = iter(qs), iter(qs)
2105 self.assertEqual(repr(next(i1)), "<Tag: t1>")
2106 self.assertEqual(repr(next(i1)), "<Tag: t2>")
2107 self.assertEqual(repr(next(i2)), "<Tag: t1>")
2108 self.assertEqual(repr(next(i2)), "<Tag: t2>")
2109 self.assertEqual(repr(next(i2)), "<Tag: t3>")
2110 self.assertEqual(repr(next(i1)), "<Tag: t3>")
2111
2112 qs = X.objects.all()
2113 self.assertFalse(qs)
2114 self.assertFalse(qs)
2115
2116 def test_nested_queries_sql(self):
2117 # Nested queries should not evaluate the inner query as part of constructing the
2118 # SQL (so we should see a nested query here, indicated by two "SELECT" calls).
2119 qs = Annotation.objects.filter(notes__in=Note.objects.filter(note="xyzzy"))
2120 self.assertEqual(qs.query.get_compiler(qs.db).as_sql()[0].count("SELECT"), 2)
2121
2122 def test_tickets_8921_9188(self):
2123 # Incorrect SQL was being generated for certain types of exclude()
2124 # queries that crossed multi-valued relations (#8921, #9188 and some
2125 # preemptively discovered cases).
2126
2127 self.assertSequenceEqual(
2128 PointerA.objects.filter(connection__pointerb__id=1), []
2129 )
2130 self.assertSequenceEqual(
2131 PointerA.objects.exclude(connection__pointerb__id=1), []
2132 )
2133
2134 self.assertSequenceEqual(
2135 Tag.objects.exclude(children=None),
2136 [self.t1, self.t3],
2137 )
2138
2139 # This example is tricky because the parent could be NULL, so only checking
2140 # parents with annotations omits some results (tag t1, in this case).
2141 self.assertSequenceEqual(
2142 Tag.objects.exclude(parent__annotation__name="a1"),
2143 [self.t1, self.t4, self.t5],
2144 )
2145
2146 # The annotation->tag link is single values and tag->children links is
2147 # multi-valued. So we have to split the exclude filter in the middle
2148 # and then optimize the inner query without losing results.
2149 self.assertSequenceEqual(
2150 Annotation.objects.exclude(tag__children__name="t2"),
2151 [self.ann2],
2152 )
2153
2154 # Nested queries are possible (although should be used with care, since
2155 # they have performance problems on backends like MySQL.
2156 self.assertSequenceEqual(
2157 Annotation.objects.filter(notes__in=Note.objects.filter(note="n1")),
2158 [self.ann1],
2159 )
2160
2161 def test_ticket3739(self):
2162 # The all() method on querysets returns a copy of the queryset.
2163 q1 = Tag.objects.order_by("name")
2164 self.assertIsNot(q1, q1.all())
2165
2166 def test_ticket_11320(self):
2167 qs = Tag.objects.exclude(category=None).exclude(category__name="foo")
2168 self.assertEqual(str(qs.query).count(" INNER JOIN "), 1)
2169
2170 def test_distinct_ordered_sliced_subquery_aggregation(self):
2171 self.assertEqual(
2172 Tag.objects.distinct().order_by("category__name")[:3].count(), 3
2173 )
2174
2175 def test_multiple_columns_with_the_same_name_slice(self):
2176 self.assertEqual(
2177 list(
2178 Tag.objects.order_by("name").values_list("name", "category__name")[:2]
2179 ),
2180 [("t1", "Generic"), ("t2", "Generic")],
2181 )
2182 self.assertSequenceEqual(
2183 Tag.objects.order_by("name").select_related("category")[:2],
2184 [self.t1, self.t2],
2185 )
2186 self.assertEqual(
2187 list(Tag.objects.order_by("-name").values_list("name", "parent__name")[:2]),
2188 [("t5", "t3"), ("t4", "t3")],
2189 )
2190 self.assertSequenceEqual(
2191 Tag.objects.order_by("-name").select_related("parent")[:2],
2192 [self.t5, self.t4],
2193 )
2194
2195 def test_col_alias_quoted(self):
2196 with CaptureQueriesContext(connection) as captured_queries:
2197 self.assertEqual(
2198 Tag.objects.values("parent")
2199 .annotate(
2200 tag_per_parent=Count("pk"),
2201 )
2202 .aggregate(Max("tag_per_parent")),
2203 {"tag_per_parent__max": 2},
2204 )
2205 sql = captured_queries[0]["sql"]
2206 self.assertIn("AS %s" % connection.ops.quote_name("parent"), sql)
2207
2208 def test_xor_subquery(self):
2209 self.assertSequenceEqual(
2210 Tag.objects.filter(
2211 Exists(Tag.objects.filter(id=OuterRef("id"), name="t3"))
2212 ^ Exists(Tag.objects.filter(id=OuterRef("id"), parent=self.t1))
2213 ),
2214 [self.t2],
2215 )
2216
2217
2218 class RawQueriesTests(TestCase):
2219 @classmethod
2220 def setUpTestData(cls):
2221 Note.objects.create(note="n1", misc="foo", id=1)
2222
2223 def test_ticket14729(self):
2224 # Test representation of raw query with one or few parameters passed as list
2225 query = "SELECT * FROM queries_note WHERE note = %s"
2226 params = ["n1"]
2227 qs = Note.objects.raw(query, params=params)
2228 self.assertEqual(
2229 repr(qs), "<RawQuerySet: SELECT * FROM queries_note WHERE note = n1>"
2230 )
2231
2232 query = "SELECT * FROM queries_note WHERE note = %s and misc = %s"
2233 params = ["n1", "foo"]
2234 qs = Note.objects.raw(query, params=params)
2235 self.assertEqual(
2236 repr(qs),
2237 "<RawQuerySet: SELECT * FROM queries_note WHERE note = n1 and misc = foo>",
2238 )
2239
2240
2241 class GeneratorExpressionTests(SimpleTestCase):
2242 def test_ticket10432(self):
2243 # Using an empty iterator as the rvalue for an "__in"
2244 # lookup is legal.
2245 self.assertCountEqual(Note.objects.filter(pk__in=iter(())), [])
2246
2247
2248 class ComparisonTests(TestCase):
2249 @classmethod
2250 def setUpTestData(cls):
2251 cls.n1 = Note.objects.create(note="n1", misc="foo", id=1)
2252 e1 = ExtraInfo.objects.create(info="e1", note=cls.n1)
2253 cls.a2 = Author.objects.create(name="a2", num=2002, extra=e1)
2254
2255 def test_ticket8597(self):
2256 # Regression tests for case-insensitive comparisons
2257 item_ab = Item.objects.create(
2258 name="a_b", created=datetime.datetime.now(), creator=self.a2, note=self.n1
2259 )
2260 item_xy = Item.objects.create(
2261 name="x%y", created=datetime.datetime.now(), creator=self.a2, note=self.n1
2262 )
2263 self.assertSequenceEqual(
2264 Item.objects.filter(name__iexact="A_b"),
2265 [item_ab],
2266 )
2267 self.assertSequenceEqual(
2268 Item.objects.filter(name__iexact="x%Y"),
2269 [item_xy],
2270 )
2271 self.assertSequenceEqual(
2272 Item.objects.filter(name__istartswith="A_b"),
2273 [item_ab],
2274 )
2275 self.assertSequenceEqual(
2276 Item.objects.filter(name__iendswith="A_b"),
2277 [item_ab],
2278 )
2279
2280
2281 class ExistsSql(TestCase):
2282 def test_exists(self):
2283 with CaptureQueriesContext(connection) as captured_queries:
2284 self.assertFalse(Tag.objects.exists())
2285 # Ok - so the exist query worked - but did it include too many columns?
2286 self.assertEqual(len(captured_queries), 1)
2287 qstr = captured_queries[0]["sql"]
2288 id, name = connection.ops.quote_name("id"), connection.ops.quote_name("name")
2289 self.assertNotIn(id, qstr)
2290 self.assertNotIn(name, qstr)
2291
2292 def test_distinct_exists(self):
2293 with CaptureQueriesContext(connection) as captured_queries:
2294 self.assertIs(Article.objects.distinct().exists(), False)
2295 self.assertEqual(len(captured_queries), 1)
2296 captured_sql = captured_queries[0]["sql"]
2297 self.assertNotIn(connection.ops.quote_name("id"), captured_sql)
2298 self.assertNotIn(connection.ops.quote_name("name"), captured_sql)
2299
2300 def test_sliced_distinct_exists(self):
2301 with CaptureQueriesContext(connection) as captured_queries:
2302 self.assertIs(Article.objects.distinct()[1:3].exists(), False)
2303 self.assertEqual(len(captured_queries), 1)
2304 captured_sql = captured_queries[0]["sql"]
2305 self.assertIn(connection.ops.quote_name("id"), captured_sql)
2306 self.assertIn(connection.ops.quote_name("name"), captured_sql)
2307
2308 def test_ticket_18414(self):
2309 Article.objects.create(name="one", created=datetime.datetime.now())
2310 Article.objects.create(name="one", created=datetime.datetime.now())
2311 Article.objects.create(name="two", created=datetime.datetime.now())
2312 self.assertTrue(Article.objects.exists())
2313 self.assertTrue(Article.objects.distinct().exists())
2314 self.assertTrue(Article.objects.distinct()[1:3].exists())
2315 self.assertFalse(Article.objects.distinct()[1:1].exists())
2316
2317 @skipUnlessDBFeature("can_distinct_on_fields")
2318 def test_ticket_18414_distinct_on(self):
2319 Article.objects.create(name="one", created=datetime.datetime.now())
2320 Article.objects.create(name="one", created=datetime.datetime.now())
2321 Article.objects.create(name="two", created=datetime.datetime.now())
2322 self.assertTrue(Article.objects.distinct("name").exists())
2323 self.assertTrue(Article.objects.distinct("name")[1:2].exists())
2324 self.assertFalse(Article.objects.distinct("name")[2:3].exists())
2325
2326
2327 class QuerysetOrderedTests(unittest.TestCase):
2328 """
2329 Tests for the Queryset.ordered attribute.
2330 """
2331
2332 def test_no_default_or_explicit_ordering(self):
2333 self.assertIs(Annotation.objects.all().ordered, False)
2334
2335 def test_cleared_default_ordering(self):
2336 self.assertIs(Tag.objects.all().ordered, True)
2337 self.assertIs(Tag.objects.order_by().ordered, False)
2338
2339 def test_explicit_ordering(self):
2340 self.assertIs(Annotation.objects.order_by("id").ordered, True)
2341
2342 def test_empty_queryset(self):
2343 self.assertIs(Annotation.objects.none().ordered, True)
2344
2345 def test_order_by_extra(self):
2346 self.assertIs(Annotation.objects.extra(order_by=["id"]).ordered, True)
2347
2348 def test_annotated_ordering(self):
2349 qs = Annotation.objects.annotate(num_notes=Count("notes"))
2350 self.assertIs(qs.ordered, False)
2351 self.assertIs(qs.order_by("num_notes").ordered, True)
2352
2353 def test_annotated_default_ordering(self):
2354 qs = Tag.objects.annotate(num_notes=Count("pk"))
2355 self.assertIs(qs.ordered, False)
2356 self.assertIs(qs.order_by("name").ordered, True)
2357
2358 def test_annotated_values_default_ordering(self):
2359 qs = Tag.objects.values("name").annotate(num_notes=Count("pk"))
2360 self.assertIs(qs.ordered, False)
2361 self.assertIs(qs.order_by("name").ordered, True)
2362
2363
2364 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2365 class SubqueryTests(TestCase):
2366 @classmethod
2367 def setUpTestData(cls):
2368 NamedCategory.objects.create(id=1, name="first")
2369 NamedCategory.objects.create(id=2, name="second")
2370 NamedCategory.objects.create(id=3, name="third")
2371 NamedCategory.objects.create(id=4, name="fourth")
2372
2373 def test_ordered_subselect(self):
2374 "Subselects honor any manual ordering"
2375 query = DumbCategory.objects.filter(
2376 id__in=DumbCategory.objects.order_by("-id")[0:2]
2377 )
2378 self.assertEqual(set(query.values_list("id", flat=True)), {3, 4})
2379
2380 query = DumbCategory.objects.filter(
2381 id__in=DumbCategory.objects.order_by("-id")[:2]
2382 )
2383 self.assertEqual(set(query.values_list("id", flat=True)), {3, 4})
2384
2385 query = DumbCategory.objects.filter(
2386 id__in=DumbCategory.objects.order_by("-id")[1:2]
2387 )
2388 self.assertEqual(set(query.values_list("id", flat=True)), {3})
2389
2390 query = DumbCategory.objects.filter(
2391 id__in=DumbCategory.objects.order_by("-id")[2:]
2392 )
2393 self.assertEqual(set(query.values_list("id", flat=True)), {1, 2})
2394
2395 def test_slice_subquery_and_query(self):
2396 """
2397 Slice a query that has a sliced subquery
2398 """
2399 query = DumbCategory.objects.filter(
2400 id__in=DumbCategory.objects.order_by("-id")[0:2]
2401 ).order_by("id")[0:2]
2402 self.assertSequenceEqual([x.id for x in query], [3, 4])
2403
2404 query = DumbCategory.objects.filter(
2405 id__in=DumbCategory.objects.order_by("-id")[1:3]
2406 ).order_by("id")[1:3]
2407 self.assertSequenceEqual([x.id for x in query], [3])
2408
2409 query = DumbCategory.objects.filter(
2410 id__in=DumbCategory.objects.order_by("-id")[2:]
2411 ).order_by("id")[1:]
2412 self.assertSequenceEqual([x.id for x in query], [2])
2413
2414 def test_related_sliced_subquery(self):
2415 """
2416 Related objects constraints can safely contain sliced subqueries.
2417 refs #22434
2418 """
2419 generic = NamedCategory.objects.create(id=5, name="Generic")
2420 t1 = Tag.objects.create(name="t1", category=generic)
2421 t2 = Tag.objects.create(name="t2", category=generic)
2422 ManagedModel.objects.create(data="mm1", tag=t1, public=True)
2423 mm2 = ManagedModel.objects.create(data="mm2", tag=t2, public=True)
2424
2425 query = ManagedModel.normal_manager.filter(
2426 tag__in=Tag.objects.order_by("-id")[:1]
2427 )
2428 self.assertEqual({x.id for x in query}, {mm2.id})
2429
2430 def test_sliced_delete(self):
2431 "Delete queries can safely contain sliced subqueries"
2432 DumbCategory.objects.filter(
2433 id__in=DumbCategory.objects.order_by("-id")[0:1]
2434 ).delete()
2435 self.assertEqual(
2436 set(DumbCategory.objects.values_list("id", flat=True)), {1, 2, 3}
2437 )
2438
2439 DumbCategory.objects.filter(
2440 id__in=DumbCategory.objects.order_by("-id")[1:2]
2441 ).delete()
2442 self.assertEqual(set(DumbCategory.objects.values_list("id", flat=True)), {1, 3})
2443
2444 DumbCategory.objects.filter(
2445 id__in=DumbCategory.objects.order_by("-id")[1:]
2446 ).delete()
2447 self.assertEqual(set(DumbCategory.objects.values_list("id", flat=True)), {3})
2448
2449 def test_distinct_ordered_sliced_subquery(self):
2450 # Implicit values('id').
2451 self.assertSequenceEqual(
2452 NamedCategory.objects.filter(
2453 id__in=NamedCategory.objects.distinct().order_by("name")[0:2],
2454 )
2455 .order_by("name")
2456 .values_list("name", flat=True),
2457 ["first", "fourth"],
2458 )
2459 # Explicit values('id').
2460 self.assertSequenceEqual(
2461 NamedCategory.objects.filter(
2462 id__in=NamedCategory.objects.distinct()
2463 .order_by("-name")
2464 .values("id")[0:2],
2465 )
2466 .order_by("name")
2467 .values_list("name", flat=True),
2468 ["second", "third"],
2469 )
2470 # Annotated value.
2471 self.assertSequenceEqual(
2472 DumbCategory.objects.filter(
2473 id__in=DumbCategory.objects.annotate(double_id=F("id") * 2)
2474 .order_by("id")
2475 .distinct()
2476 .values("double_id")[0:2],
2477 )
2478 .order_by("id")
2479 .values_list("id", flat=True),
2480 [2, 4],
2481 )
2482
2483
2484 class QuerySetBitwiseOperationTests(TestCase):
2485 @classmethod
2486 def setUpTestData(cls):
2487 cls.school = School.objects.create()
2488 cls.room_1 = Classroom.objects.create(
2489 school=cls.school, has_blackboard=False, name="Room 1"
2490 )
2491 cls.room_2 = Classroom.objects.create(
2492 school=cls.school, has_blackboard=True, name="Room 2"
2493 )
2494 cls.room_3 = Classroom.objects.create(
2495 school=cls.school, has_blackboard=True, name="Room 3"
2496 )
2497 cls.room_4 = Classroom.objects.create(
2498 school=cls.school, has_blackboard=False, name="Room 4"
2499 )
2500 tag = Tag.objects.create()
2501 cls.annotation_1 = Annotation.objects.create(tag=tag)
2502 annotation_2 = Annotation.objects.create(tag=tag)
2503 note = cls.annotation_1.notes.create(tag=tag)
2504 cls.base_user_1 = BaseUser.objects.create(annotation=cls.annotation_1)
2505 cls.base_user_2 = BaseUser.objects.create(annotation=annotation_2)
2506 cls.task = Task.objects.create(
2507 owner=cls.base_user_2,
2508 creator=cls.base_user_2,
2509 note=note,
2510 )
2511
2512 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2513 def test_or_with_rhs_slice(self):
2514 qs1 = Classroom.objects.filter(has_blackboard=True)
2515 qs2 = Classroom.objects.filter(has_blackboard=False)[:1]
2516 self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2, self.room_3])
2517
2518 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2519 def test_or_with_lhs_slice(self):
2520 qs1 = Classroom.objects.filter(has_blackboard=True)[:1]
2521 qs2 = Classroom.objects.filter(has_blackboard=False)
2522 self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2, self.room_4])
2523
2524 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2525 def test_or_with_both_slice(self):
2526 qs1 = Classroom.objects.filter(has_blackboard=False)[:1]
2527 qs2 = Classroom.objects.filter(has_blackboard=True)[:1]
2528 self.assertCountEqual(qs1 | qs2, [self.room_1, self.room_2])
2529
2530 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2531 def test_or_with_both_slice_and_ordering(self):
2532 qs1 = Classroom.objects.filter(has_blackboard=False).order_by("-pk")[:1]
2533 qs2 = Classroom.objects.filter(has_blackboard=True).order_by("-name")[:1]
2534 self.assertCountEqual(qs1 | qs2, [self.room_3, self.room_4])
2535
2536 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2537 def test_xor_with_rhs_slice(self):
2538 qs1 = Classroom.objects.filter(has_blackboard=True)
2539 qs2 = Classroom.objects.filter(has_blackboard=False)[:1]
2540 self.assertCountEqual(qs1 ^ qs2, [self.room_1, self.room_2, self.room_3])
2541
2542 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2543 def test_xor_with_lhs_slice(self):
2544 qs1 = Classroom.objects.filter(has_blackboard=True)[:1]
2545 qs2 = Classroom.objects.filter(has_blackboard=False)
2546 self.assertCountEqual(qs1 ^ qs2, [self.room_1, self.room_2, self.room_4])
2547
2548 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2549 def test_xor_with_both_slice(self):
2550 qs1 = Classroom.objects.filter(has_blackboard=False)[:1]
2551 qs2 = Classroom.objects.filter(has_blackboard=True)[:1]
2552 self.assertCountEqual(qs1 ^ qs2, [self.room_1, self.room_2])
2553
2554 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
2555 def test_xor_with_both_slice_and_ordering(self):
2556 qs1 = Classroom.objects.filter(has_blackboard=False).order_by("-pk")[:1]
2557 qs2 = Classroom.objects.filter(has_blackboard=True).order_by("-name")[:1]
2558 self.assertCountEqual(qs1 ^ qs2, [self.room_3, self.room_4])
2559
2560 def test_subquery_aliases(self):
2561 combined = School.objects.filter(pk__isnull=False) & School.objects.filter(
2562 Exists(
2563 Classroom.objects.filter(
2564 has_blackboard=True,
2565 school=OuterRef("pk"),
2566 )
2567 ),
2568 )
2569 self.assertSequenceEqual(combined, [self.school])
2570 nested_combined = School.objects.filter(pk__in=combined.values("pk"))
2571 self.assertSequenceEqual(nested_combined, [self.school])
2572
2573 def test_conflicting_aliases_during_combine(self):
2574 qs1 = self.annotation_1.baseuser_set.all()
2575 qs2 = BaseUser.objects.filter(
2576 Q(owner__note__in=self.annotation_1.notes.all())
2577 | Q(creator__note__in=self.annotation_1.notes.all())
2578 )
2579 self.assertSequenceEqual(qs1, [self.base_user_1])
2580 self.assertSequenceEqual(qs2, [self.base_user_2])
2581 self.assertCountEqual(qs2 | qs1, qs1 | qs2)
2582 self.assertCountEqual(qs2 | qs1, [self.base_user_1, self.base_user_2])
2583
2584
2585 class CloneTests(TestCase):
2586 def test_evaluated_queryset_as_argument(self):
2587 """
2588 If a queryset is already evaluated, it can still be used as a query arg.
2589 """
2590 n = Note(note="Test1", misc="misc")
2591 n.save()
2592 e = ExtraInfo(info="good", note=n)
2593 e.save()
2594
2595 n_list = Note.objects.all()
2596 # Evaluate the Note queryset, populating the query cache
2597 list(n_list)
2598 # Make one of cached results unpickable.
2599 n_list._result_cache[0].error = UnpickleableError()
2600 with self.assertRaises(UnpickleableError):
2601 pickle.dumps(n_list)
2602 # Use the note queryset in a query, and evaluate
2603 # that query in a way that involves cloning.
2604 self.assertEqual(ExtraInfo.objects.filter(note__in=n_list)[0].info, "good")
2605
2606 def test_no_model_options_cloning(self):
2607 """
2608 Cloning a queryset does not get out of hand. While complete
2609 testing is impossible, this is a sanity check against invalid use of
2610 deepcopy. refs #16759.
2611 """
2612 opts_class = type(Note._meta)
2613 note_deepcopy = getattr(opts_class, "__deepcopy__", None)
2614 opts_class.__deepcopy__ = lambda obj, memo: self.fail(
2615 "Model options shouldn't be cloned."
2616 )
2617 try:
2618 Note.objects.filter(pk__lte=F("pk") + 1).all()
2619 finally:
2620 if note_deepcopy is None:
2621 delattr(opts_class, "__deepcopy__")
2622 else:
2623 opts_class.__deepcopy__ = note_deepcopy
2624
2625 def test_no_fields_cloning(self):
2626 """
2627 Cloning a queryset does not get out of hand. While complete
2628 testing is impossible, this is a sanity check against invalid use of
2629 deepcopy. refs #16759.
2630 """
2631 opts_class = type(Note._meta.get_field("misc"))
2632 note_deepcopy = getattr(opts_class, "__deepcopy__", None)
2633 opts_class.__deepcopy__ = lambda obj, memo: self.fail(
2634 "Model fields shouldn't be cloned"
2635 )
2636 try:
2637 Note.objects.filter(note=F("misc")).all()
2638 finally:
2639 if note_deepcopy is None:
2640 delattr(opts_class, "__deepcopy__")
2641 else:
2642 opts_class.__deepcopy__ = note_deepcopy
2643
2644
2645 class EmptyQuerySetTests(SimpleTestCase):
2646 def test_emptyqueryset_values(self):
2647 # #14366 -- Calling .values() on an empty QuerySet and then cloning
2648 # that should not cause an error
2649 self.assertCountEqual(Number.objects.none().values("num").order_by("num"), [])
2650
2651 def test_values_subquery(self):
2652 self.assertCountEqual(
2653 Number.objects.filter(pk__in=Number.objects.none().values("pk")), []
2654 )
2655 self.assertCountEqual(
2656 Number.objects.filter(pk__in=Number.objects.none().values_list("pk")), []
2657 )
2658
2659 def test_ticket_19151(self):
2660 # #19151 -- Calling .values() or .values_list() on an empty QuerySet
2661 # should return an empty QuerySet and not cause an error.
2662 q = Author.objects.none()
2663 self.assertCountEqual(q.values(), [])
2664 self.assertCountEqual(q.values_list(), [])
2665
2666
2667 class ValuesQuerysetTests(TestCase):
2668 @classmethod
2669 def setUpTestData(cls):
2670 Number.objects.create(num=72)
2671
2672 def test_flat_values_list(self):
2673 qs = Number.objects.values_list("num")
2674 qs = qs.values_list("num", flat=True)
2675 self.assertSequenceEqual(qs, [72])
2676
2677 def test_duplicate_values_list(self):
2678 value = Number.objects.values_list("num", "num").get()
2679 self.assertEqual(value, (72, 72))
2680 value = Number.objects.values_list(F("num"), F("num")).get()
2681 self.assertEqual(value, (72, 72))
2682
2683 def test_extra_values(self):
2684 # testing for ticket 14930 issues
2685 qs = Number.objects.extra(
2686 select={"value_plus_x": "num+%s", "value_minus_x": "num-%s"},
2687 select_params=(1, 2),
2688 )
2689 qs = qs.order_by("value_minus_x")
2690 qs = qs.values("num")
2691 self.assertSequenceEqual(qs, [{"num": 72}])
2692
2693 def test_extra_values_order_twice(self):
2694 # testing for ticket 14930 issues
2695 qs = Number.objects.extra(
2696 select={"value_plus_one": "num+1", "value_minus_one": "num-1"}
2697 )
2698 qs = qs.order_by("value_minus_one").order_by("value_plus_one")
2699 qs = qs.values("num")
2700 self.assertSequenceEqual(qs, [{"num": 72}])
2701
2702 def test_extra_values_order_multiple(self):
2703 # Postgres doesn't allow constants in order by, so check for that.
2704 qs = Number.objects.extra(
2705 select={
2706 "value_plus_one": "num+1",
2707 "value_minus_one": "num-1",
2708 "constant_value": "1",
2709 }
2710 )
2711 qs = qs.order_by("value_plus_one", "value_minus_one", "constant_value")
2712 qs = qs.values("num")
2713 self.assertSequenceEqual(qs, [{"num": 72}])
2714
2715 def test_extra_values_order_in_extra(self):
2716 # testing for ticket 14930 issues
2717 qs = Number.objects.extra(
2718 select={"value_plus_one": "num+1", "value_minus_one": "num-1"},
2719 order_by=["value_minus_one"],
2720 )
2721 qs = qs.values("num")
2722
2723 def test_extra_select_params_values_order_in_extra(self):
2724 # testing for 23259 issue
2725 qs = Number.objects.extra(
2726 select={"value_plus_x": "num+%s"},
2727 select_params=[1],
2728 order_by=["value_plus_x"],
2729 )
2730 qs = qs.filter(num=72)
2731 qs = qs.values("num")
2732 self.assertSequenceEqual(qs, [{"num": 72}])
2733
2734 def test_extra_multiple_select_params_values_order_by(self):
2735 # testing for 23259 issue
2736 qs = Number.objects.extra(
2737 select={"value_plus_x": "num+%s", "value_minus_x": "num-%s"},
2738 select_params=(72, 72),
2739 )
2740 qs = qs.order_by("value_minus_x")
2741 qs = qs.filter(num=1)
2742 qs = qs.values("num")
2743 self.assertSequenceEqual(qs, [])
2744
2745 def test_extra_values_list(self):
2746 # testing for ticket 14930 issues
2747 qs = Number.objects.extra(select={"value_plus_one": "num+1"})
2748 qs = qs.order_by("value_plus_one")
2749 qs = qs.values_list("num")
2750 self.assertSequenceEqual(qs, [(72,)])
2751
2752 def test_flat_extra_values_list(self):
2753 # testing for ticket 14930 issues
2754 qs = Number.objects.extra(select={"value_plus_one": "num+1"})
2755 qs = qs.order_by("value_plus_one")
2756 qs = qs.values_list("num", flat=True)
2757 self.assertSequenceEqual(qs, [72])
2758
2759 def test_field_error_values_list(self):
2760 # see #23443
2761 msg = (
2762 "Cannot resolve keyword %r into field. Join on 'name' not permitted."
2763 % "foo"
2764 )
2765 with self.assertRaisesMessage(FieldError, msg):
2766 Tag.objects.values_list("name__foo")
2767
2768 def test_named_values_list_flat(self):
2769 msg = "'flat' and 'named' can't be used together."
2770 with self.assertRaisesMessage(TypeError, msg):
2771 Number.objects.values_list("num", flat=True, named=True)
2772
2773 def test_named_values_list_bad_field_name(self):
2774 msg = "Type names and field names must be valid identifiers: '1'"
2775 with self.assertRaisesMessage(ValueError, msg):
2776 Number.objects.extra(select={"1": "num+1"}).values_list(
2777 "1", named=True
2778 ).first()
2779
2780 def test_named_values_list_with_fields(self):
2781 qs = Number.objects.extra(select={"num2": "num+1"}).annotate(Count("id"))
2782 values = qs.values_list("num", "num2", named=True).first()
2783 self.assertEqual(type(values).__name__, "Row")
2784 self.assertEqual(values._fields, ("num", "num2"))
2785 self.assertEqual(values.num, 72)
2786 self.assertEqual(values.num2, 73)
2787
2788 def test_named_values_list_without_fields(self):
2789 qs = Number.objects.extra(select={"num2": "num+1"}).annotate(Count("id"))
2790 values = qs.values_list(named=True).first()
2791 self.assertEqual(type(values).__name__, "Row")
2792 self.assertEqual(
2793 values._fields,
2794 ("num2", "id", "num", "other_num", "another_num", "id__count"),
2795 )
2796 self.assertEqual(values.num, 72)
2797 self.assertEqual(values.num2, 73)
2798 self.assertEqual(values.id__count, 1)
2799
2800 def test_named_values_list_expression_with_default_alias(self):
2801 expr = Count("id")
2802 values = (
2803 Number.objects.annotate(id__count1=expr)
2804 .values_list(expr, "id__count1", named=True)
2805 .first()
2806 )
2807 self.assertEqual(values._fields, ("id__count2", "id__count1"))
2808
2809 def test_named_values_list_expression(self):
2810 expr = F("num") + 1
2811 qs = Number.objects.annotate(combinedexpression1=expr).values_list(
2812 expr, "combinedexpression1", named=True
2813 )
2814 values = qs.first()
2815 self.assertEqual(values._fields, ("combinedexpression2", "combinedexpression1"))
2816
2817 def test_named_values_pickle(self):
2818 value = Number.objects.values_list("num", "other_num", named=True).get()
2819 self.assertEqual(value, (72, None))
2820 self.assertEqual(pickle.loads(pickle.dumps(value)), value)
2821
2822
2823 class QuerySetSupportsPythonIdioms(TestCase):
2824 @classmethod
2825 def setUpTestData(cls):
2826 some_date = datetime.datetime(2014, 5, 16, 12, 1)
2827 cls.articles = [
2828 Article.objects.create(name=f"Article {i}", created=some_date)
2829 for i in range(1, 8)
2830 ]
2831
2832 def get_ordered_articles(self):
2833 return Article.objects.order_by("name")
2834
2835 def test_can_get_items_using_index_and_slice_notation(self):
2836 self.assertEqual(self.get_ordered_articles()[0].name, "Article 1")
2837 self.assertSequenceEqual(
2838 self.get_ordered_articles()[1:3],
2839 [self.articles[1], self.articles[2]],
2840 )
2841
2842 def test_slicing_with_steps_can_be_used(self):
2843 self.assertSequenceEqual(
2844 self.get_ordered_articles()[::2],
2845 [
2846 self.articles[0],
2847 self.articles[2],
2848 self.articles[4],
2849 self.articles[6],
2850 ],
2851 )
2852
2853 def test_slicing_without_step_is_lazy(self):
2854 with self.assertNumQueries(0):
2855 self.get_ordered_articles()[0:5]
2856
2857 def test_slicing_with_tests_is_not_lazy(self):
2858 with self.assertNumQueries(1):
2859 self.get_ordered_articles()[0:5:3]
2860
2861 def test_slicing_can_slice_again_after_slicing(self):
2862 self.assertSequenceEqual(
2863 self.get_ordered_articles()[0:5][0:2],
2864 [self.articles[0], self.articles[1]],
2865 )
2866 self.assertSequenceEqual(
2867 self.get_ordered_articles()[0:5][4:], [self.articles[4]]
2868 )
2869 self.assertSequenceEqual(self.get_ordered_articles()[0:5][5:], [])
2870
2871 # Some more tests!
2872 self.assertSequenceEqual(
2873 self.get_ordered_articles()[2:][0:2],
2874 [self.articles[2], self.articles[3]],
2875 )
2876 self.assertSequenceEqual(
2877 self.get_ordered_articles()[2:][:2],
2878 [self.articles[2], self.articles[3]],
2879 )
2880 self.assertSequenceEqual(
2881 self.get_ordered_articles()[2:][2:3], [self.articles[4]]
2882 )
2883
2884 # Using an offset without a limit is also possible.
2885 self.assertSequenceEqual(
2886 self.get_ordered_articles()[5:],
2887 [self.articles[5], self.articles[6]],
2888 )
2889
2890 def test_slicing_cannot_filter_queryset_once_sliced(self):
2891 msg = "Cannot filter a query once a slice has been taken."
2892 with self.assertRaisesMessage(TypeError, msg):
2893 Article.objects.all()[0:5].filter(id=1)
2894
2895 def test_slicing_cannot_reorder_queryset_once_sliced(self):
2896 msg = "Cannot reorder a query once a slice has been taken."
2897 with self.assertRaisesMessage(TypeError, msg):
2898 Article.objects.all()[0:5].order_by("id")
2899
2900 def test_slicing_cannot_combine_queries_once_sliced(self):
2901 msg = "Cannot combine queries once a slice has been taken."
2902 with self.assertRaisesMessage(TypeError, msg):
2903 Article.objects.all()[0:1] & Article.objects.all()[4:5]
2904
2905 def test_slicing_negative_indexing_not_supported_for_single_element(self):
2906 """hint: inverting your ordering might do what you need"""
2907 msg = "Negative indexing is not supported."
2908 with self.assertRaisesMessage(ValueError, msg):
2909 Article.objects.all()[-1]
2910
2911 def test_slicing_negative_indexing_not_supported_for_range(self):
2912 """hint: inverting your ordering might do what you need"""
2913 msg = "Negative indexing is not supported."
2914 with self.assertRaisesMessage(ValueError, msg):
2915 Article.objects.all()[0:-5]
2916 with self.assertRaisesMessage(ValueError, msg):
2917 Article.objects.all()[-1:]
2918
2919 def test_invalid_index(self):
2920 msg = "QuerySet indices must be integers or slices, not str."
2921 with self.assertRaisesMessage(TypeError, msg):
2922 Article.objects.all()["foo"]
2923
2924 def test_can_get_number_of_items_in_queryset_using_standard_len(self):
2925 self.assertEqual(len(Article.objects.filter(name__exact="Article 1")), 1)
2926
2927 def test_can_combine_queries_using_and_and_or_operators(self):
2928 s1 = Article.objects.filter(name__exact="Article 1")
2929 s2 = Article.objects.filter(name__exact="Article 2")
2930 self.assertSequenceEqual(
2931 (s1 | s2).order_by("name"),
2932 [self.articles[0], self.articles[1]],
2933 )
2934 self.assertSequenceEqual(s1 & s2, [])
2935
2936
2937 class WeirdQuerysetSlicingTests(TestCase):
2938 @classmethod
2939 def setUpTestData(cls):
2940 Number.objects.create(num=1)
2941 Number.objects.create(num=2)
2942
2943 Article.objects.create(name="one", created=datetime.datetime.now())
2944 Article.objects.create(name="two", created=datetime.datetime.now())
2945 Article.objects.create(name="three", created=datetime.datetime.now())
2946 Article.objects.create(name="four", created=datetime.datetime.now())
2947
2948 food = Food.objects.create(name="spam")
2949 Eaten.objects.create(meal="spam with eggs", food=food)
2950
2951 def test_tickets_7698_10202(self):
2952 # People like to slice with '0' as the high-water mark.
2953 self.assertSequenceEqual(Article.objects.all()[0:0], [])
2954 self.assertSequenceEqual(Article.objects.all()[0:0][:10], [])
2955 self.assertEqual(Article.objects.all()[:0].count(), 0)
2956 msg = "Cannot change a query once a slice has been taken."
2957 with self.assertRaisesMessage(TypeError, msg):
2958 Article.objects.all()[:0].latest("created")
2959
2960 def test_empty_resultset_sql(self):
2961 # ticket #12192
2962 self.assertNumQueries(0, lambda: list(Number.objects.all()[1:1]))
2963
2964 def test_empty_sliced_subquery(self):
2965 self.assertEqual(
2966 Eaten.objects.filter(food__in=Food.objects.all()[0:0]).count(), 0
2967 )
2968
2969 def test_empty_sliced_subquery_exclude(self):
2970 self.assertEqual(
2971 Eaten.objects.exclude(food__in=Food.objects.all()[0:0]).count(), 1
2972 )
2973
2974 def test_zero_length_values_slicing(self):
2975 n = 42
2976 with self.assertNumQueries(0):
2977 self.assertQuerySetEqual(Article.objects.values()[n:n], [])
2978 self.assertQuerySetEqual(Article.objects.values_list()[n:n], [])
2979
2980
2981 class EscapingTests(TestCase):
2982 def test_ticket_7302(self):
2983 # Reserved names are appropriately escaped
2984 r_a = ReservedName.objects.create(name="a", order=42)
2985 r_b = ReservedName.objects.create(name="b", order=37)
2986 self.assertSequenceEqual(
2987 ReservedName.objects.order_by("order"),
2988 [r_b, r_a],
2989 )
2990 self.assertSequenceEqual(
2991 ReservedName.objects.extra(
2992 select={"stuff": "name"}, order_by=("order", "stuff")
2993 ),
2994 [r_b, r_a],
2995 )
2996
2997
2998 class ToFieldTests(TestCase):
2999 def test_in_query(self):
3000 apple = Food.objects.create(name="apple")
3001 pear = Food.objects.create(name="pear")
3002 lunch = Eaten.objects.create(food=apple, meal="lunch")
3003 dinner = Eaten.objects.create(food=pear, meal="dinner")
3004
3005 self.assertEqual(
3006 set(Eaten.objects.filter(food__in=[apple, pear])),
3007 {lunch, dinner},
3008 )
3009
3010 def test_in_subquery(self):
3011 apple = Food.objects.create(name="apple")
3012 lunch = Eaten.objects.create(food=apple, meal="lunch")
3013 self.assertEqual(
3014 set(Eaten.objects.filter(food__in=Food.objects.filter(name="apple"))),
3015 {lunch},
3016 )
3017 self.assertEqual(
3018 set(
3019 Eaten.objects.filter(
3020 food__in=Food.objects.filter(name="apple").values("eaten__meal")
3021 )
3022 ),
3023 set(),
3024 )
3025 self.assertEqual(
3026 set(Food.objects.filter(eaten__in=Eaten.objects.filter(meal="lunch"))),
3027 {apple},
3028 )
3029
3030 def test_nested_in_subquery(self):
3031 extra = ExtraInfo.objects.create()
3032 author = Author.objects.create(num=42, extra=extra)
3033 report = Report.objects.create(creator=author)
3034 comment = ReportComment.objects.create(report=report)
3035 comments = ReportComment.objects.filter(
3036 report__in=Report.objects.filter(
3037 creator__in=extra.author_set.all(),
3038 ),
3039 )
3040 self.assertSequenceEqual(comments, [comment])
3041
3042 def test_reverse_in(self):
3043 apple = Food.objects.create(name="apple")
3044 pear = Food.objects.create(name="pear")
3045 lunch_apple = Eaten.objects.create(food=apple, meal="lunch")
3046 lunch_pear = Eaten.objects.create(food=pear, meal="dinner")
3047
3048 self.assertEqual(
3049 set(Food.objects.filter(eaten__in=[lunch_apple, lunch_pear])), {apple, pear}
3050 )
3051
3052 def test_single_object(self):
3053 apple = Food.objects.create(name="apple")
3054 lunch = Eaten.objects.create(food=apple, meal="lunch")
3055 dinner = Eaten.objects.create(food=apple, meal="dinner")
3056
3057 self.assertEqual(set(Eaten.objects.filter(food=apple)), {lunch, dinner})
3058
3059 def test_single_object_reverse(self):
3060 apple = Food.objects.create(name="apple")
3061 lunch = Eaten.objects.create(food=apple, meal="lunch")
3062
3063 self.assertEqual(set(Food.objects.filter(eaten=lunch)), {apple})
3064
3065 def test_recursive_fk(self):
3066 node1 = Node.objects.create(num=42)
3067 node2 = Node.objects.create(num=1, parent=node1)
3068
3069 self.assertEqual(list(Node.objects.filter(parent=node1)), [node2])
3070
3071 def test_recursive_fk_reverse(self):
3072 node1 = Node.objects.create(num=42)
3073 node2 = Node.objects.create(num=1, parent=node1)
3074
3075 self.assertEqual(list(Node.objects.filter(node=node2)), [node1])
3076
3077
3078 class IsNullTests(TestCase):
3079 def test_primary_key(self):
3080 custom = CustomPk.objects.create(name="pk")
3081 null = Related.objects.create()
3082 notnull = Related.objects.create(custom=custom)
3083 self.assertSequenceEqual(
3084 Related.objects.filter(custom__isnull=False), [notnull]
3085 )
3086 self.assertSequenceEqual(Related.objects.filter(custom__isnull=True), [null])
3087
3088 def test_to_field(self):
3089 apple = Food.objects.create(name="apple")
3090 e1 = Eaten.objects.create(food=apple, meal="lunch")
3091 e2 = Eaten.objects.create(meal="lunch")
3092 self.assertSequenceEqual(
3093 Eaten.objects.filter(food__isnull=False),
3094 [e1],
3095 )
3096 self.assertSequenceEqual(
3097 Eaten.objects.filter(food__isnull=True),
3098 [e2],
3099 )
3100
3101
3102 class ConditionalTests(TestCase):
3103 """Tests whose execution depend on different environment conditions like
3104 Python version or DB backend features"""
3105
3106 @classmethod
3107 def setUpTestData(cls):
3108 generic = NamedCategory.objects.create(name="Generic")
3109 t1 = Tag.objects.create(name="t1", category=generic)
3110 Tag.objects.create(name="t2", parent=t1, category=generic)
3111 t3 = Tag.objects.create(name="t3", parent=t1)
3112 Tag.objects.create(name="t4", parent=t3)
3113 Tag.objects.create(name="t5", parent=t3)
3114
3115 def test_infinite_loop(self):
3116 # If you're not careful, it's possible to introduce infinite loops via
3117 # default ordering on foreign keys in a cycle. We detect that.
3118 with self.assertRaisesMessage(FieldError, "Infinite loop caused by ordering."):
3119 list(LoopX.objects.all()) # Force queryset evaluation with list()
3120 with self.assertRaisesMessage(FieldError, "Infinite loop caused by ordering."):
3121 list(LoopZ.objects.all()) # Force queryset evaluation with list()
3122
3123 # Note that this doesn't cause an infinite loop, since the default
3124 # ordering on the Tag model is empty (and thus defaults to using "id"
3125 # for the related field).
3126 self.assertEqual(len(Tag.objects.order_by("parent")), 5)
3127
3128 # ... but you can still order in a non-recursive fashion among linked
3129 # fields (the previous test failed because the default ordering was
3130 # recursive).
3131 self.assertSequenceEqual(LoopX.objects.order_by("y__x__y__x__id"), [])
3132
3133 # When grouping without specifying ordering, we add an explicit "ORDER BY NULL"
3134 # portion in MySQL to prevent unnecessary sorting.
3135 @skipUnlessDBFeature("requires_explicit_null_ordering_when_grouping")
3136 def test_null_ordering_added(self):
3137 query = Tag.objects.values_list("parent_id", flat=True).order_by().query
3138 query.group_by = ["parent_id"]
3139 sql = query.get_compiler(DEFAULT_DB_ALIAS).as_sql()[0]
3140 fragment = "ORDER BY "
3141 pos = sql.find(fragment)
3142 self.assertEqual(sql.find(fragment, pos + 1), -1)
3143 self.assertEqual(sql.find("NULL", pos + len(fragment)), pos + len(fragment))
3144
3145 def test_in_list_limit(self):
3146 # The "in" lookup works with lists of 1000 items or more.
3147 # The numbers amount is picked to force three different IN batches
3148 # for Oracle, yet to be less than 2100 parameter limit for MSSQL.
3149 numbers = list(range(2050))
3150 max_query_params = connection.features.max_query_params
3151 if max_query_params is None or max_query_params >= len(numbers):
3152 Number.objects.bulk_create(Number(num=num) for num in numbers)
3153 for number in [1000, 1001, 2000, len(numbers)]:
3154 with self.subTest(number=number):
3155 self.assertEqual(
3156 Number.objects.filter(num__in=numbers[:number]).count(), number
3157 )
3158
3159
3160 class UnionTests(unittest.TestCase):
3161 """
3162 Tests for the union of two querysets. Bug #12252.
3163 """
3164
3165 @classmethod
3166 def setUpTestData(cls):
3167 objectas = []
3168 objectbs = []
3169 objectcs = []
3170 a_info = ["one", "two", "three"]
3171 for name in a_info:
3172 o = ObjectA(name=name)
3173 o.save()
3174 objectas.append(o)
3175 b_info = [
3176 ("un", 1, objectas[0]),
3177 ("deux", 2, objectas[0]),
3178 ("trois", 3, objectas[2]),
3179 ]
3180 for name, number, objecta in b_info:
3181 o = ObjectB(name=name, num=number, objecta=objecta)
3182 o.save()
3183 objectbs.append(o)
3184 c_info = [("ein", objectas[2], objectbs[2]), ("zwei", objectas[1], objectbs[1])]
3185 for name, objecta, objectb in c_info:
3186 o = ObjectC(name=name, objecta=objecta, objectb=objectb)
3187 o.save()
3188 objectcs.append(o)
3189
3190 def check_union(self, model, Q1, Q2):
3191 filter = model.objects.filter
3192 self.assertEqual(set(filter(Q1) | filter(Q2)), set(filter(Q1 | Q2)))
3193 self.assertEqual(set(filter(Q2) | filter(Q1)), set(filter(Q1 | Q2)))
3194
3195 def test_A_AB(self):
3196 Q1 = Q(name="two")
3197 Q2 = Q(objectb__name="deux")
3198 self.check_union(ObjectA, Q1, Q2)
3199
3200 def test_A_AB2(self):
3201 Q1 = Q(name="two")
3202 Q2 = Q(objectb__name="deux", objectb__num=2)
3203 self.check_union(ObjectA, Q1, Q2)
3204
3205 def test_AB_ACB(self):
3206 Q1 = Q(objectb__name="deux")
3207 Q2 = Q(objectc__objectb__name="deux")
3208 self.check_union(ObjectA, Q1, Q2)
3209
3210 def test_BAB_BAC(self):
3211 Q1 = Q(objecta__objectb__name="deux")
3212 Q2 = Q(objecta__objectc__name="ein")
3213 self.check_union(ObjectB, Q1, Q2)
3214
3215 def test_BAB_BACB(self):
3216 Q1 = Q(objecta__objectb__name="deux")
3217 Q2 = Q(objecta__objectc__objectb__name="trois")
3218 self.check_union(ObjectB, Q1, Q2)
3219
3220 def test_BA_BCA__BAB_BAC_BCA(self):
3221 Q1 = Q(objecta__name="one", objectc__objecta__name="two")
3222 Q2 = Q(
3223 objecta__objectc__name="ein",
3224 objectc__objecta__name="three",
3225 objecta__objectb__name="trois",
3226 )
3227 self.check_union(ObjectB, Q1, Q2)
3228
3229
3230 class DefaultValuesInsertTest(TestCase):
3231 def test_no_extra_params(self):
3232 """
3233 Can create an instance of a model with only the PK field (#17056)."
3234 """
3235 DumbCategory.objects.create()
3236
3237
3238 class ExcludeTests(TestCase):
3239 @classmethod
3240 def setUpTestData(cls):
3241 f1 = Food.objects.create(name="apples")
3242 cls.f2 = Food.objects.create(name="oranges")
3243 Eaten.objects.create(food=f1, meal="dinner")
3244 cls.j1 = Job.objects.create(name="Manager")
3245 cls.r1 = Responsibility.objects.create(description="Playing golf")
3246 cls.j2 = Job.objects.create(name="Programmer")
3247 cls.r2 = Responsibility.objects.create(description="Programming")
3248 JobResponsibilities.objects.create(job=cls.j1, responsibility=cls.r1)
3249 JobResponsibilities.objects.create(job=cls.j2, responsibility=cls.r2)
3250
3251 def test_to_field(self):
3252 self.assertSequenceEqual(
3253 Food.objects.exclude(eaten__meal="dinner"),
3254 [self.f2],
3255 )
3256 self.assertSequenceEqual(
3257 Job.objects.exclude(responsibilities__description="Playing golf"),
3258 [self.j2],
3259 )
3260 self.assertSequenceEqual(
3261 Responsibility.objects.exclude(jobs__name="Manager"),
3262 [self.r2],
3263 )
3264
3265 def test_exclude_m2m_through(self):
3266 alex = Person.objects.get_or_create(name="Alex")[0]
3267 jane = Person.objects.get_or_create(name="Jane")[0]
3268
3269 oracle = Company.objects.get_or_create(name="Oracle")[0]
3270 google = Company.objects.get_or_create(name="Google")[0]
3271 microsoft = Company.objects.get_or_create(name="Microsoft")[0]
3272 intel = Company.objects.get_or_create(name="Intel")[0]
3273
3274 def employ(employer, employee, title):
3275 Employment.objects.get_or_create(
3276 employee=employee, employer=employer, title=title
3277 )
3278
3279 employ(oracle, alex, "Engineer")
3280 employ(oracle, alex, "Developer")
3281 employ(google, alex, "Engineer")
3282 employ(google, alex, "Manager")
3283 employ(microsoft, alex, "Manager")
3284 employ(intel, alex, "Manager")
3285
3286 employ(microsoft, jane, "Developer")
3287 employ(intel, jane, "Manager")
3288
3289 alex_tech_employers = (
3290 alex.employers.filter(employment__title__in=("Engineer", "Developer"))
3291 .distinct()
3292 .order_by("name")
3293 )
3294 self.assertSequenceEqual(alex_tech_employers, [google, oracle])
3295
3296 alex_nontech_employers = (
3297 alex.employers.exclude(employment__title__in=("Engineer", "Developer"))
3298 .distinct()
3299 .order_by("name")
3300 )
3301 with self.assertNumQueries(1) as ctx:
3302 self.assertSequenceEqual(alex_nontech_employers, [google, intel, microsoft])
3303 sql = ctx.captured_queries[0]["sql"]
3304 # Company's ID should appear in SELECT and INNER JOIN, not in EXISTS as
3305 # the outer query reference is not necessary when an alias is reused.
3306 company_id = "%s.%s" % (
3307 connection.ops.quote_name(Company._meta.db_table),
3308 connection.ops.quote_name(Company._meta.get_field("id").column),
3309 )
3310 self.assertEqual(sql.count(company_id), 2)
3311
3312 def test_exclude_reverse_fk_field_ref(self):
3313 tag = Tag.objects.create()
3314 Note.objects.create(tag=tag, note="note")
3315 annotation = Annotation.objects.create(name="annotation", tag=tag)
3316 self.assertEqual(
3317 Annotation.objects.exclude(tag__note__note=F("name")).get(), annotation
3318 )
3319
3320 def test_exclude_with_circular_fk_relation(self):
3321 self.assertEqual(
3322 ObjectB.objects.exclude(objecta__objectb__name=F("name")).count(), 0
3323 )
3324
3325 def test_subquery_exclude_outerref(self):
3326 qs = JobResponsibilities.objects.filter(
3327 Exists(Responsibility.objects.exclude(jobs=OuterRef("job"))),
3328 )
3329 self.assertTrue(qs.exists())
3330 self.r1.delete()
3331 self.assertFalse(qs.exists())
3332
3333 def test_exclude_nullable_fields(self):
3334 number = Number.objects.create(num=1, other_num=1)
3335 Number.objects.create(num=2, other_num=2, another_num=2)
3336 self.assertSequenceEqual(
3337 Number.objects.exclude(other_num=F("another_num")),
3338 [number],
3339 )
3340 self.assertSequenceEqual(
3341 Number.objects.exclude(num=F("another_num")),
3342 [number],
3343 )
3344
3345 def test_exclude_multivalued_exists(self):
3346 with CaptureQueriesContext(connection) as captured_queries:
3347 self.assertSequenceEqual(
3348 Job.objects.exclude(responsibilities__description="Programming"),
3349 [self.j1],
3350 )
3351 self.assertIn("exists", captured_queries[0]["sql"].lower())
3352
3353 def test_exclude_subquery(self):
3354 subquery = JobResponsibilities.objects.filter(
3355 responsibility__description="bar",
3356 ) | JobResponsibilities.objects.exclude(
3357 job__responsibilities__description="foo",
3358 )
3359 self.assertCountEqual(
3360 Job.objects.annotate(
3361 responsibility=subquery.filter(job=OuterRef("name")).values("id")[:1]
3362 ),
3363 [self.j1, self.j2],
3364 )
3365
3366 def test_exclude_unsaved_object(self):
3367 company = Company.objects.create(name="Django")
3368 msg = "Model instances passed to related filters must be saved."
3369 with self.assertRaisesMessage(ValueError, msg):
3370 Employment.objects.exclude(employer=Company(name="unsaved"))
3371 with self.assertRaisesMessage(ValueError, msg):
3372 Employment.objects.exclude(employer__in=[company, Company(name="unsaved")])
3373 with self.assertRaisesMessage(ValueError, msg):
3374 StaffUser.objects.exclude(staff=Staff(name="unsaved"))
3375
3376
3377 class ExcludeTest17600(TestCase):
3378 """
3379 Some regressiontests for ticket #17600. Some of these likely duplicate
3380 other existing tests.
3381 """
3382
3383 @classmethod
3384 def setUpTestData(cls):
3385 # Create a few Orders.
3386 cls.o1 = Order.objects.create(pk=1)
3387 cls.o2 = Order.objects.create(pk=2)
3388 cls.o3 = Order.objects.create(pk=3)
3389
3390 # Create some OrderItems for the first order with homogeneous
3391 # status_id values
3392 cls.oi1 = OrderItem.objects.create(order=cls.o1, status=1)
3393 cls.oi2 = OrderItem.objects.create(order=cls.o1, status=1)
3394 cls.oi3 = OrderItem.objects.create(order=cls.o1, status=1)
3395
3396 # Create some OrderItems for the second order with heterogeneous
3397 # status_id values
3398 cls.oi4 = OrderItem.objects.create(order=cls.o2, status=1)
3399 cls.oi5 = OrderItem.objects.create(order=cls.o2, status=2)
3400 cls.oi6 = OrderItem.objects.create(order=cls.o2, status=3)
3401
3402 # Create some OrderItems for the second order with heterogeneous
3403 # status_id values
3404 cls.oi7 = OrderItem.objects.create(order=cls.o3, status=2)
3405 cls.oi8 = OrderItem.objects.create(order=cls.o3, status=3)
3406 cls.oi9 = OrderItem.objects.create(order=cls.o3, status=4)
3407
3408 def test_exclude_plain(self):
3409 """
3410 This should exclude Orders which have some items with status 1
3411 """
3412 self.assertSequenceEqual(
3413 Order.objects.exclude(items__status=1),
3414 [self.o3],
3415 )
3416
3417 def test_exclude_plain_distinct(self):
3418 """
3419 This should exclude Orders which have some items with status 1
3420 """
3421 self.assertSequenceEqual(
3422 Order.objects.exclude(items__status=1).distinct(),
3423 [self.o3],
3424 )
3425
3426 def test_exclude_with_q_object_distinct(self):
3427 """
3428 This should exclude Orders which have some items with status 1
3429 """
3430 self.assertSequenceEqual(
3431 Order.objects.exclude(Q(items__status=1)).distinct(),
3432 [self.o3],
3433 )
3434
3435 def test_exclude_with_q_object_no_distinct(self):
3436 """
3437 This should exclude Orders which have some items with status 1
3438 """
3439 self.assertSequenceEqual(
3440 Order.objects.exclude(Q(items__status=1)),
3441 [self.o3],
3442 )
3443
3444 def test_exclude_with_q_is_equal_to_plain_exclude(self):
3445 """
3446 Using exclude(condition) and exclude(Q(condition)) should
3447 yield the same QuerySet
3448 """
3449 self.assertEqual(
3450 list(Order.objects.exclude(items__status=1).distinct()),
3451 list(Order.objects.exclude(Q(items__status=1)).distinct()),
3452 )
3453
3454 def test_exclude_with_q_is_equal_to_plain_exclude_variation(self):
3455 """
3456 Using exclude(condition) and exclude(Q(condition)) should
3457 yield the same QuerySet
3458 """
3459 self.assertEqual(
3460 list(Order.objects.exclude(items__status=1)),
3461 list(Order.objects.exclude(Q(items__status=1)).distinct()),
3462 )
3463
3464 @unittest.expectedFailure
3465 def test_only_orders_with_all_items_having_status_1(self):
3466 """
3467 This should only return orders having ALL items set to status 1, or
3468 those items not having any orders at all. The correct way to write
3469 this query in SQL seems to be using two nested subqueries.
3470 """
3471 self.assertSequenceEqual(
3472 Order.objects.exclude(~Q(items__status=1)).distinct(),
3473 [self.o1],
3474 )
3475
3476
3477 class Exclude15786(TestCase):
3478 """Regression test for #15786"""
3479
3480 def test_ticket15786(self):
3481 c1 = SimpleCategory.objects.create(name="c1")
3482 c2 = SimpleCategory.objects.create(name="c2")
3483 OneToOneCategory.objects.create(category=c1)
3484 OneToOneCategory.objects.create(category=c2)
3485 rel = CategoryRelationship.objects.create(first=c1, second=c2)
3486 self.assertEqual(
3487 CategoryRelationship.objects.exclude(
3488 first__onetoonecategory=F("second__onetoonecategory")
3489 ).get(),
3490 rel,
3491 )
3492
3493
3494 class NullInExcludeTest(TestCase):
3495 @classmethod
3496 def setUpTestData(cls):
3497 NullableName.objects.create(name="i1")
3498 NullableName.objects.create()
3499
3500 def test_null_in_exclude_qs(self):
3501 none_val = "" if connection.features.interprets_empty_strings_as_nulls else None
3502 self.assertQuerySetEqual(
3503 NullableName.objects.exclude(name__in=[]),
3504 ["i1", none_val],
3505 attrgetter("name"),
3506 )
3507 self.assertQuerySetEqual(
3508 NullableName.objects.exclude(name__in=["i1"]),
3509 [none_val],
3510 attrgetter("name"),
3511 )
3512 self.assertQuerySetEqual(
3513 NullableName.objects.exclude(name__in=["i3"]),
3514 ["i1", none_val],
3515 attrgetter("name"),
3516 )
3517 inner_qs = NullableName.objects.filter(name="i1").values_list("name")
3518 self.assertQuerySetEqual(
3519 NullableName.objects.exclude(name__in=inner_qs),
3520 [none_val],
3521 attrgetter("name"),
3522 )
3523 # The inner queryset wasn't executed - it should be turned
3524 # into subquery above
3525 self.assertIs(inner_qs._result_cache, None)
3526
3527 @unittest.expectedFailure
3528 def test_col_not_in_list_containing_null(self):
3529 """
3530 The following case is not handled properly because
3531 SQL's COL NOT IN (list containing null) handling is too weird to
3532 abstract away.
3533 """
3534 self.assertQuerySetEqual(
3535 NullableName.objects.exclude(name__in=[None]), ["i1"], attrgetter("name")
3536 )
3537
3538 def test_double_exclude(self):
3539 self.assertEqual(
3540 list(NullableName.objects.filter(~~Q(name="i1"))),
3541 list(NullableName.objects.filter(Q(name="i1"))),
3542 )
3543 self.assertNotIn(
3544 "IS NOT NULL", str(NullableName.objects.filter(~~Q(name="i1")).query)
3545 )
3546
3547
3548 class EmptyStringsAsNullTest(TestCase):
3549 """
3550 Filtering on non-null character fields works as expected.
3551 The reason for these tests is that Oracle treats '' as NULL, and this
3552 can cause problems in query construction. Refs #17957.
3553 """
3554
3555 @classmethod
3556 def setUpTestData(cls):
3557 cls.nc = NamedCategory.objects.create(name="")
3558
3559 def test_direct_exclude(self):
3560 self.assertQuerySetEqual(
3561 NamedCategory.objects.exclude(name__in=["nonexistent"]),
3562 [self.nc.pk],
3563 attrgetter("pk"),
3564 )
3565
3566 def test_joined_exclude(self):
3567 self.assertQuerySetEqual(
3568 DumbCategory.objects.exclude(namedcategory__name__in=["nonexistent"]),
3569 [self.nc.pk],
3570 attrgetter("pk"),
3571 )
3572
3573 def test_21001(self):
3574 foo = NamedCategory.objects.create(name="foo")
3575 self.assertQuerySetEqual(
3576 NamedCategory.objects.exclude(name=""), [foo.pk], attrgetter("pk")
3577 )
3578
3579
3580 class ProxyQueryCleanupTest(TestCase):
3581 def test_evaluated_proxy_count(self):
3582 """
3583 Generating the query string doesn't alter the query's state
3584 in irreversible ways. Refs #18248.
3585 """
3586 ProxyCategory.objects.create()
3587 qs = ProxyCategory.objects.all()
3588 self.assertEqual(qs.count(), 1)
3589 str(qs.query)
3590 self.assertEqual(qs.count(), 1)
3591
3592
3593 class WhereNodeTest(SimpleTestCase):
3594 class DummyNode:
3595 def as_sql(self, compiler, connection):
3596 return "dummy", []
3597
3598 class MockCompiler:
3599 def compile(self, node):
3600 return node.as_sql(self, connection)
3601
3602 def __call__(self, name):
3603 return connection.ops.quote_name(name)
3604
3605 def test_empty_full_handling_conjunction(self):
3606 compiler = WhereNodeTest.MockCompiler()
3607 w = WhereNode(children=[NothingNode()])
3608 with self.assertRaises(EmptyResultSet):
3609 w.as_sql(compiler, connection)
3610 w.negate()
3611 with self.assertRaises(FullResultSet):
3612 w.as_sql(compiler, connection)
3613 w = WhereNode(children=[self.DummyNode(), self.DummyNode()])
3614 self.assertEqual(w.as_sql(compiler, connection), ("(dummy AND dummy)", []))
3615 w.negate()
3616 self.assertEqual(w.as_sql(compiler, connection), ("NOT (dummy AND dummy)", []))
3617 w = WhereNode(children=[NothingNode(), self.DummyNode()])
3618 with self.assertRaises(EmptyResultSet):
3619 w.as_sql(compiler, connection)
3620 w.negate()
3621 with self.assertRaises(FullResultSet):
3622 w.as_sql(compiler, connection)
3623
3624 def test_empty_full_handling_disjunction(self):
3625 compiler = WhereNodeTest.MockCompiler()
3626 w = WhereNode(children=[NothingNode()], connector=OR)
3627 with self.assertRaises(EmptyResultSet):
3628 w.as_sql(compiler, connection)
3629 w.negate()
3630 with self.assertRaises(FullResultSet):
3631 w.as_sql(compiler, connection)
3632 w = WhereNode(children=[self.DummyNode(), self.DummyNode()], connector=OR)
3633 self.assertEqual(w.as_sql(compiler, connection), ("(dummy OR dummy)", []))
3634 w.negate()
3635 self.assertEqual(w.as_sql(compiler, connection), ("NOT (dummy OR dummy)", []))
3636 w = WhereNode(children=[NothingNode(), self.DummyNode()], connector=OR)
3637 self.assertEqual(w.as_sql(compiler, connection), ("dummy", []))
3638 w.negate()
3639 self.assertEqual(w.as_sql(compiler, connection), ("NOT (dummy)", []))
3640
3641 def test_empty_nodes(self):
3642 compiler = WhereNodeTest.MockCompiler()
3643 empty_w = WhereNode()
3644 w = WhereNode(children=[empty_w, empty_w])
3645 with self.assertRaises(FullResultSet):
3646 w.as_sql(compiler, connection)
3647 w.negate()
3648 with self.assertRaises(EmptyResultSet):
3649 w.as_sql(compiler, connection)
3650 w.connector = OR
3651 with self.assertRaises(EmptyResultSet):
3652 w.as_sql(compiler, connection)
3653 w.negate()
3654 with self.assertRaises(FullResultSet):
3655 w.as_sql(compiler, connection)
3656 w = WhereNode(children=[empty_w, NothingNode()], connector=OR)
3657 with self.assertRaises(FullResultSet):
3658 w.as_sql(compiler, connection)
3659 w = WhereNode(children=[empty_w, NothingNode()], connector=AND)
3660 with self.assertRaises(EmptyResultSet):
3661 w.as_sql(compiler, connection)
3662
3663
3664 class QuerySetExceptionTests(SimpleTestCase):
3665 def test_invalid_order_by(self):
3666 msg = "Cannot resolve keyword '*' into field. Choices are: created, id, name"
3667 with self.assertRaisesMessage(FieldError, msg):
3668 Article.objects.order_by("*")
3669
3670 def test_invalid_order_by_raw_column_alias(self):
3671 msg = (
3672 "Cannot resolve keyword 'queries_author.name' into field. Choices "
3673 "are: cover, created, creator, creator_id, id, modified, name, "
3674 "note, note_id, tags"
3675 )
3676 with self.assertRaisesMessage(FieldError, msg):
3677 Item.objects.values("creator__name").order_by("queries_author.name")
3678
3679 def test_invalid_queryset_model(self):
3680 msg = 'Cannot use QuerySet for "Article": Use a QuerySet for "ExtraInfo".'
3681 with self.assertRaisesMessage(ValueError, msg):
3682 list(Author.objects.filter(extra=Article.objects.all()))
3683
3684
3685 class NullJoinPromotionOrTest(TestCase):
3686 @classmethod
3687 def setUpTestData(cls):
3688 cls.d1 = ModelD.objects.create(name="foo")
3689 d2 = ModelD.objects.create(name="bar")
3690 cls.a1 = ModelA.objects.create(name="a1", d=cls.d1)
3691 c = ModelC.objects.create(name="c")
3692 b = ModelB.objects.create(name="b", c=c)
3693 cls.a2 = ModelA.objects.create(name="a2", b=b, d=d2)
3694
3695 def test_ticket_17886(self):
3696 # The first Q-object is generating the match, the rest of the filters
3697 # should not remove the match even if they do not match anything. The
3698 # problem here was that b__name generates a LOUTER JOIN, then
3699 # b__c__name generates join to c, which the ORM tried to promote but
3700 # failed as that join isn't nullable.
3701 q_obj = Q(d__name="foo") | Q(b__name="foo") | Q(b__c__name="foo")
3702 qset = ModelA.objects.filter(q_obj)
3703 self.assertEqual(list(qset), [self.a1])
3704 # We generate one INNER JOIN to D. The join is direct and not nullable
3705 # so we can use INNER JOIN for it. However, we can NOT use INNER JOIN
3706 # for the b->c join, as a->b is nullable.
3707 self.assertEqual(str(qset.query).count("INNER JOIN"), 1)
3708
3709 def test_isnull_filter_promotion(self):
3710 qs = ModelA.objects.filter(Q(b__name__isnull=True))
3711 self.assertEqual(str(qs.query).count("LEFT OUTER"), 1)
3712 self.assertEqual(list(qs), [self.a1])
3713
3714 qs = ModelA.objects.filter(~Q(b__name__isnull=True))
3715 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3716 self.assertEqual(list(qs), [self.a2])
3717
3718 qs = ModelA.objects.filter(~~Q(b__name__isnull=True))
3719 self.assertEqual(str(qs.query).count("LEFT OUTER"), 1)
3720 self.assertEqual(list(qs), [self.a1])
3721
3722 qs = ModelA.objects.filter(Q(b__name__isnull=False))
3723 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3724 self.assertEqual(list(qs), [self.a2])
3725
3726 qs = ModelA.objects.filter(~Q(b__name__isnull=False))
3727 self.assertEqual(str(qs.query).count("LEFT OUTER"), 1)
3728 self.assertEqual(list(qs), [self.a1])
3729
3730 qs = ModelA.objects.filter(~~Q(b__name__isnull=False))
3731 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3732 self.assertEqual(list(qs), [self.a2])
3733
3734 def test_null_join_demotion(self):
3735 qs = ModelA.objects.filter(Q(b__name__isnull=False) & Q(b__name__isnull=True))
3736 self.assertIn(" INNER JOIN ", str(qs.query))
3737 qs = ModelA.objects.filter(Q(b__name__isnull=True) & Q(b__name__isnull=False))
3738 self.assertIn(" INNER JOIN ", str(qs.query))
3739 qs = ModelA.objects.filter(Q(b__name__isnull=False) | Q(b__name__isnull=True))
3740 self.assertIn(" LEFT OUTER JOIN ", str(qs.query))
3741 qs = ModelA.objects.filter(Q(b__name__isnull=True) | Q(b__name__isnull=False))
3742 self.assertIn(" LEFT OUTER JOIN ", str(qs.query))
3743
3744 def test_ticket_21366(self):
3745 n = Note.objects.create(note="n", misc="m")
3746 e = ExtraInfo.objects.create(info="info", note=n)
3747 a = Author.objects.create(name="Author1", num=1, extra=e)
3748 Ranking.objects.create(rank=1, author=a)
3749 r1 = Report.objects.create(name="Foo", creator=a)
3750 r2 = Report.objects.create(name="Bar")
3751 Report.objects.create(name="Bar", creator=a)
3752 qs = Report.objects.filter(
3753 Q(creator__ranking__isnull=True) | Q(creator__ranking__rank=1, name="Foo")
3754 )
3755 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
3756 self.assertEqual(str(qs.query).count(" JOIN "), 2)
3757 self.assertSequenceEqual(qs.order_by("name"), [r2, r1])
3758
3759 def test_ticket_21748(self):
3760 i1 = Identifier.objects.create(name="i1")
3761 i2 = Identifier.objects.create(name="i2")
3762 i3 = Identifier.objects.create(name="i3")
3763 Program.objects.create(identifier=i1)
3764 Channel.objects.create(identifier=i1)
3765 Program.objects.create(identifier=i2)
3766 self.assertSequenceEqual(
3767 Identifier.objects.filter(program=None, channel=None), [i3]
3768 )
3769 self.assertSequenceEqual(
3770 Identifier.objects.exclude(program=None, channel=None).order_by("name"),
3771 [i1, i2],
3772 )
3773
3774 def test_ticket_21748_double_negated_and(self):
3775 i1 = Identifier.objects.create(name="i1")
3776 i2 = Identifier.objects.create(name="i2")
3777 Identifier.objects.create(name="i3")
3778 p1 = Program.objects.create(identifier=i1)
3779 c1 = Channel.objects.create(identifier=i1)
3780 Program.objects.create(identifier=i2)
3781 # Check the ~~Q() (or equivalently .exclude(~Q)) works like Q() for
3782 # join promotion.
3783 qs1_doubleneg = Identifier.objects.exclude(
3784 ~Q(program__id=p1.id, channel__id=c1.id)
3785 ).order_by("pk")
3786 qs1_filter = Identifier.objects.filter(
3787 program__id=p1.id, channel__id=c1.id
3788 ).order_by("pk")
3789 self.assertQuerySetEqual(qs1_doubleneg, qs1_filter, lambda x: x)
3790 self.assertEqual(
3791 str(qs1_filter.query).count("JOIN"), str(qs1_doubleneg.query).count("JOIN")
3792 )
3793 self.assertEqual(2, str(qs1_doubleneg.query).count("INNER JOIN"))
3794 self.assertEqual(
3795 str(qs1_filter.query).count("INNER JOIN"),
3796 str(qs1_doubleneg.query).count("INNER JOIN"),
3797 )
3798
3799 def test_ticket_21748_double_negated_or(self):
3800 i1 = Identifier.objects.create(name="i1")
3801 i2 = Identifier.objects.create(name="i2")
3802 Identifier.objects.create(name="i3")
3803 p1 = Program.objects.create(identifier=i1)
3804 c1 = Channel.objects.create(identifier=i1)
3805 p2 = Program.objects.create(identifier=i2)
3806 # Test OR + doubleneg. The expected result is that channel is LOUTER
3807 # joined, program INNER joined
3808 qs1_filter = Identifier.objects.filter(
3809 Q(program__id=p2.id, channel__id=c1.id) | Q(program__id=p1.id)
3810 ).order_by("pk")
3811 qs1_doubleneg = Identifier.objects.exclude(
3812 ~Q(Q(program__id=p2.id, channel__id=c1.id) | Q(program__id=p1.id))
3813 ).order_by("pk")
3814 self.assertQuerySetEqual(qs1_doubleneg, qs1_filter, lambda x: x)
3815 self.assertEqual(
3816 str(qs1_filter.query).count("JOIN"), str(qs1_doubleneg.query).count("JOIN")
3817 )
3818 self.assertEqual(1, str(qs1_doubleneg.query).count("INNER JOIN"))
3819 self.assertEqual(
3820 str(qs1_filter.query).count("INNER JOIN"),
3821 str(qs1_doubleneg.query).count("INNER JOIN"),
3822 )
3823
3824 def test_ticket_21748_complex_filter(self):
3825 i1 = Identifier.objects.create(name="i1")
3826 i2 = Identifier.objects.create(name="i2")
3827 Identifier.objects.create(name="i3")
3828 p1 = Program.objects.create(identifier=i1)
3829 c1 = Channel.objects.create(identifier=i1)
3830 p2 = Program.objects.create(identifier=i2)
3831 # Finally, a more complex case, one time in a way where each
3832 # NOT is pushed to lowest level in the boolean tree, and
3833 # another query where this isn't done.
3834 qs1 = Identifier.objects.filter(
3835 ~Q(~Q(program__id=p2.id, channel__id=c1.id) & Q(program__id=p1.id))
3836 ).order_by("pk")
3837 qs2 = Identifier.objects.filter(
3838 Q(Q(program__id=p2.id, channel__id=c1.id) | ~Q(program__id=p1.id))
3839 ).order_by("pk")
3840 self.assertQuerySetEqual(qs1, qs2, lambda x: x)
3841 self.assertEqual(str(qs1.query).count("JOIN"), str(qs2.query).count("JOIN"))
3842 self.assertEqual(0, str(qs1.query).count("INNER JOIN"))
3843 self.assertEqual(
3844 str(qs1.query).count("INNER JOIN"), str(qs2.query).count("INNER JOIN")
3845 )
3846
3847
3848 class ReverseJoinTrimmingTest(TestCase):
3849 def test_reverse_trimming(self):
3850 # We don't accidentally trim reverse joins - we can't know if there is
3851 # anything on the other side of the join, so trimming reverse joins
3852 # can't be done, ever.
3853 t = Tag.objects.create()
3854 qs = Tag.objects.filter(annotation__tag=t.pk)
3855 self.assertIn("INNER JOIN", str(qs.query))
3856 self.assertEqual(list(qs), [])
3857
3858
3859 class JoinReuseTest(TestCase):
3860 """
3861 The queries reuse joins sensibly (for example, direct joins
3862 are always reused).
3863 """
3864
3865 def test_fk_reuse(self):
3866 qs = Annotation.objects.filter(tag__name="foo").filter(tag__name="bar")
3867 self.assertEqual(str(qs.query).count("JOIN"), 1)
3868
3869 def test_fk_reuse_select_related(self):
3870 qs = Annotation.objects.filter(tag__name="foo").select_related("tag")
3871 self.assertEqual(str(qs.query).count("JOIN"), 1)
3872
3873 def test_fk_reuse_annotation(self):
3874 qs = Annotation.objects.filter(tag__name="foo").annotate(cnt=Count("tag__name"))
3875 self.assertEqual(str(qs.query).count("JOIN"), 1)
3876
3877 def test_fk_reuse_disjunction(self):
3878 qs = Annotation.objects.filter(Q(tag__name="foo") | Q(tag__name="bar"))
3879 self.assertEqual(str(qs.query).count("JOIN"), 1)
3880
3881 def test_fk_reuse_order_by(self):
3882 qs = Annotation.objects.filter(tag__name="foo").order_by("tag__name")
3883 self.assertEqual(str(qs.query).count("JOIN"), 1)
3884
3885 def test_revo2o_reuse(self):
3886 qs = Detail.objects.filter(member__name="foo").filter(member__name="foo")
3887 self.assertEqual(str(qs.query).count("JOIN"), 1)
3888
3889 def test_revfk_noreuse(self):
3890 qs = Author.objects.filter(report__name="r4").filter(report__name="r1")
3891 self.assertEqual(str(qs.query).count("JOIN"), 2)
3892
3893 def test_inverted_q_across_relations(self):
3894 """
3895 When a trimmable join is specified in the query (here school__), the
3896 ORM detects it and removes unnecessary joins. The set of reusable joins
3897 are updated after trimming the query so that other lookups don't
3898 consider that the outer query's filters are in effect for the subquery
3899 (#26551).
3900 """
3901 springfield_elementary = School.objects.create()
3902 hogward = School.objects.create()
3903 Student.objects.create(school=springfield_elementary)
3904 hp = Student.objects.create(school=hogward)
3905 Classroom.objects.create(school=hogward, name="Potion")
3906 Classroom.objects.create(school=springfield_elementary, name="Main")
3907 qs = Student.objects.filter(
3908 ~(
3909 Q(school__classroom__name="Main")
3910 & Q(school__classroom__has_blackboard=None)
3911 )
3912 )
3913 self.assertSequenceEqual(qs, [hp])
3914
3915
3916 class DisjunctionPromotionTests(TestCase):
3917 def test_disjunction_promotion_select_related(self):
3918 fk1 = FK1.objects.create(f1="f1", f2="f2")
3919 basea = BaseA.objects.create(a=fk1)
3920 qs = BaseA.objects.filter(Q(a=fk1) | Q(b=2))
3921 self.assertEqual(str(qs.query).count(" JOIN "), 0)
3922 qs = qs.select_related("a", "b")
3923 self.assertEqual(str(qs.query).count(" INNER JOIN "), 0)
3924 self.assertEqual(str(qs.query).count(" LEFT OUTER JOIN "), 2)
3925 with self.assertNumQueries(1):
3926 self.assertSequenceEqual(qs, [basea])
3927 self.assertEqual(qs[0].a, fk1)
3928 self.assertIs(qs[0].b, None)
3929
3930 def test_disjunction_promotion1(self):
3931 # Pre-existing join, add two ORed filters to the same join,
3932 # all joins can be INNER JOINS.
3933 qs = BaseA.objects.filter(a__f1="foo")
3934 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3935 qs = qs.filter(Q(b__f1="foo") | Q(b__f2="foo"))
3936 self.assertEqual(str(qs.query).count("INNER JOIN"), 2)
3937 # Reverse the order of AND and OR filters.
3938 qs = BaseA.objects.filter(Q(b__f1="foo") | Q(b__f2="foo"))
3939 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3940 qs = qs.filter(a__f1="foo")
3941 self.assertEqual(str(qs.query).count("INNER JOIN"), 2)
3942
3943 def test_disjunction_promotion2(self):
3944 qs = BaseA.objects.filter(a__f1="foo")
3945 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3946 # Now we have two different joins in an ORed condition, these
3947 # must be OUTER joins. The pre-existing join should remain INNER.
3948 qs = qs.filter(Q(b__f1="foo") | Q(c__f2="foo"))
3949 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3950 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
3951 # Reverse case.
3952 qs = BaseA.objects.filter(Q(b__f1="foo") | Q(c__f2="foo"))
3953 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
3954 qs = qs.filter(a__f1="foo")
3955 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3956 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
3957
3958 def test_disjunction_promotion3(self):
3959 qs = BaseA.objects.filter(a__f2="bar")
3960 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3961 # The ANDed a__f2 filter allows us to use keep using INNER JOIN
3962 # even inside the ORed case. If the join to a__ returns nothing,
3963 # the ANDed filter for a__f2 can't be true.
3964 qs = qs.filter(Q(a__f1="foo") | Q(b__f2="foo"))
3965 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3966 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
3967
3968 def test_disjunction_promotion3_demote(self):
3969 # This one needs demotion logic: the first filter causes a to be
3970 # outer joined, the second filter makes it inner join again.
3971 qs = BaseA.objects.filter(Q(a__f1="foo") | Q(b__f2="foo")).filter(a__f2="bar")
3972 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3973 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
3974
3975 def test_disjunction_promotion4_demote(self):
3976 qs = BaseA.objects.filter(Q(a=1) | Q(a=2))
3977 self.assertEqual(str(qs.query).count("JOIN"), 0)
3978 # Demote needed for the "a" join. It is marked as outer join by
3979 # above filter (even if it is trimmed away).
3980 qs = qs.filter(a__f1="foo")
3981 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3982
3983 def test_disjunction_promotion4(self):
3984 qs = BaseA.objects.filter(a__f1="foo")
3985 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3986 qs = qs.filter(Q(a=1) | Q(a=2))
3987 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3988
3989 def test_disjunction_promotion5_demote(self):
3990 qs = BaseA.objects.filter(Q(a=1) | Q(a=2))
3991 # Note that the above filters on a force the join to an
3992 # inner join even if it is trimmed.
3993 self.assertEqual(str(qs.query).count("JOIN"), 0)
3994 qs = qs.filter(Q(a__f1="foo") | Q(b__f1="foo"))
3995 # So, now the a__f1 join doesn't need promotion.
3996 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
3997 # But b__f1 does.
3998 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
3999 qs = BaseA.objects.filter(Q(a__f1="foo") | Q(b__f1="foo"))
4000 # Now the join to a is created as LOUTER
4001 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
4002 qs = qs.filter(Q(a=1) | Q(a=2))
4003 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
4004 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
4005
4006 def test_disjunction_promotion6(self):
4007 qs = BaseA.objects.filter(Q(a=1) | Q(a=2))
4008 self.assertEqual(str(qs.query).count("JOIN"), 0)
4009 qs = BaseA.objects.filter(Q(a__f1="foo") & Q(b__f1="foo"))
4010 self.assertEqual(str(qs.query).count("INNER JOIN"), 2)
4011 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 0)
4012
4013 qs = BaseA.objects.filter(Q(a__f1="foo") & Q(b__f1="foo"))
4014 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 0)
4015 self.assertEqual(str(qs.query).count("INNER JOIN"), 2)
4016 qs = qs.filter(Q(a=1) | Q(a=2))
4017 self.assertEqual(str(qs.query).count("INNER JOIN"), 2)
4018 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 0)
4019
4020 def test_disjunction_promotion7(self):
4021 qs = BaseA.objects.filter(Q(a=1) | Q(a=2))
4022 self.assertEqual(str(qs.query).count("JOIN"), 0)
4023 qs = BaseA.objects.filter(Q(a__f1="foo") | (Q(b__f1="foo") & Q(a__f1="bar")))
4024 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
4025 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
4026 qs = BaseA.objects.filter(
4027 (Q(a__f1="foo") | Q(b__f1="foo")) & (Q(a__f1="bar") | Q(c__f1="foo"))
4028 )
4029 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 3)
4030 self.assertEqual(str(qs.query).count("INNER JOIN"), 0)
4031 qs = BaseA.objects.filter(
4032 Q(a__f1="foo") | Q(a__f1="bar") & (Q(b__f1="bar") | Q(c__f1="foo"))
4033 )
4034 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
4035 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
4036
4037 def test_disjunction_promotion_fexpression(self):
4038 qs = BaseA.objects.filter(Q(a__f1=F("b__f1")) | Q(b__f1="foo"))
4039 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 1)
4040 self.assertEqual(str(qs.query).count("INNER JOIN"), 1)
4041 qs = BaseA.objects.filter(Q(a__f1=F("c__f1")) | Q(b__f1="foo"))
4042 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 3)
4043 qs = BaseA.objects.filter(
4044 Q(a__f1=F("b__f1")) | Q(a__f2=F("b__f2")) | Q(c__f1="foo")
4045 )
4046 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 3)
4047 qs = BaseA.objects.filter(Q(a__f1=F("c__f1")) | (Q(pk=1) & Q(pk=2)))
4048 self.assertEqual(str(qs.query).count("LEFT OUTER JOIN"), 2)
4049 self.assertEqual(str(qs.query).count("INNER JOIN"), 0)
4050
4051
4052 class ManyToManyExcludeTest(TestCase):
4053 def test_exclude_many_to_many(self):
4054 i_extra = Identifier.objects.create(name="extra")
4055 i_program = Identifier.objects.create(name="program")
4056 program = Program.objects.create(identifier=i_program)
4057 i_channel = Identifier.objects.create(name="channel")
4058 channel = Channel.objects.create(identifier=i_channel)
4059 channel.programs.add(program)
4060
4061 # channel contains 'program1', so all Identifiers except that one
4062 # should be returned
4063 self.assertSequenceEqual(
4064 Identifier.objects.exclude(program__channel=channel).order_by("name"),
4065 [i_channel, i_extra],
4066 )
4067 self.assertSequenceEqual(
4068 Identifier.objects.exclude(program__channel=None).order_by("name"),
4069 [i_program],
4070 )
4071
4072 def test_ticket_12823(self):
4073 pg3 = Page.objects.create(text="pg3")
4074 pg2 = Page.objects.create(text="pg2")
4075 pg1 = Page.objects.create(text="pg1")
4076 pa1 = Paragraph.objects.create(text="pa1")
4077 pa1.page.set([pg1, pg2])
4078 pa2 = Paragraph.objects.create(text="pa2")
4079 pa2.page.set([pg2, pg3])
4080 pa3 = Paragraph.objects.create(text="pa3")
4081 ch1 = Chapter.objects.create(title="ch1", paragraph=pa1)
4082 ch2 = Chapter.objects.create(title="ch2", paragraph=pa2)
4083 ch3 = Chapter.objects.create(title="ch3", paragraph=pa3)
4084 b1 = Book.objects.create(title="b1", chapter=ch1)
4085 b2 = Book.objects.create(title="b2", chapter=ch2)
4086 b3 = Book.objects.create(title="b3", chapter=ch3)
4087 q = Book.objects.exclude(chapter__paragraph__page__text="pg1")
4088 self.assertNotIn("IS NOT NULL", str(q.query))
4089 self.assertEqual(len(q), 2)
4090 self.assertNotIn(b1, q)
4091 self.assertIn(b2, q)
4092 self.assertIn(b3, q)
4093
4094
4095 class RelabelCloneTest(TestCase):
4096 def test_ticket_19964(self):
4097 my1 = MyObject.objects.create(data="foo")
4098 my1.parent = my1
4099 my1.save()
4100 my2 = MyObject.objects.create(data="bar", parent=my1)
4101 parents = MyObject.objects.filter(parent=F("id"))
4102 children = MyObject.objects.filter(parent__in=parents).exclude(parent=F("id"))
4103 self.assertEqual(list(parents), [my1])
4104 # Evaluating the children query (which has parents as part of it) does
4105 # not change results for the parents query.
4106 self.assertEqual(list(children), [my2])
4107 self.assertEqual(list(parents), [my1])
4108
4109
4110 class Ticket20101Tests(TestCase):
4111 def test_ticket_20101(self):
4112 """
4113 Tests QuerySet ORed combining in exclude subquery case.
4114 """
4115 t = Tag.objects.create(name="foo")
4116 a1 = Annotation.objects.create(tag=t, name="a1")
4117 a2 = Annotation.objects.create(tag=t, name="a2")
4118 a3 = Annotation.objects.create(tag=t, name="a3")
4119 n = Note.objects.create(note="foo", misc="bar")
4120 qs1 = Note.objects.exclude(annotation__in=[a1, a2])
4121 qs2 = Note.objects.filter(annotation__in=[a3])
4122 self.assertIn(n, qs1)
4123 self.assertNotIn(n, qs2)
4124 self.assertIn(n, (qs1 | qs2))
4125
4126
4127 class EmptyStringPromotionTests(SimpleTestCase):
4128 def test_empty_string_promotion(self):
4129 qs = RelatedObject.objects.filter(single__name="")
4130 if connection.features.interprets_empty_strings_as_nulls:
4131 self.assertIn("LEFT OUTER JOIN", str(qs.query))
4132 else:
4133 self.assertNotIn("LEFT OUTER JOIN", str(qs.query))
4134
4135
4136 class ValuesSubqueryTests(TestCase):
4137 def test_values_in_subquery(self):
4138 # If a values() queryset is used, then the given values
4139 # will be used instead of forcing use of the relation's field.
4140 o1 = Order.objects.create(id=-2)
4141 o2 = Order.objects.create(id=-1)
4142 oi1 = OrderItem.objects.create(order=o1, status=0)
4143 oi1.status = oi1.pk
4144 oi1.save()
4145 OrderItem.objects.create(order=o2, status=0)
4146
4147 # The query below should match o1 as it has related order_item
4148 # with id == status.
4149 self.assertSequenceEqual(
4150 Order.objects.filter(items__in=OrderItem.objects.values_list("status")),
4151 [o1],
4152 )
4153
4154
4155 class DoubleInSubqueryTests(TestCase):
4156 def test_double_subquery_in(self):
4157 lfa1 = LeafA.objects.create(data="foo")
4158 lfa2 = LeafA.objects.create(data="bar")
4159 lfb1 = LeafB.objects.create(data="lfb1")
4160 lfb2 = LeafB.objects.create(data="lfb2")
4161 Join.objects.create(a=lfa1, b=lfb1)
4162 Join.objects.create(a=lfa2, b=lfb2)
4163 leaf_as = LeafA.objects.filter(data="foo").values_list("pk", flat=True)
4164 joins = Join.objects.filter(a__in=leaf_as).values_list("b__id", flat=True)
4165 qs = LeafB.objects.filter(pk__in=joins)
4166 self.assertSequenceEqual(qs, [lfb1])
4167
4168
4169 class Ticket18785Tests(SimpleTestCase):
4170 def test_ticket_18785(self):
4171 # Test join trimming from ticket18785
4172 qs = (
4173 Item.objects.exclude(note__isnull=False)
4174 .filter(name="something", creator__extra__isnull=True)
4175 .order_by()
4176 )
4177 self.assertEqual(1, str(qs.query).count("INNER JOIN"))
4178 self.assertEqual(0, str(qs.query).count("OUTER JOIN"))
4179
4180
4181 class Ticket20788Tests(TestCase):
4182 def test_ticket_20788(self):
4183 Paragraph.objects.create()
4184 paragraph = Paragraph.objects.create()
4185 page = paragraph.page.create()
4186 chapter = Chapter.objects.create(paragraph=paragraph)
4187 Book.objects.create(chapter=chapter)
4188
4189 paragraph2 = Paragraph.objects.create()
4190 Page.objects.create()
4191 chapter2 = Chapter.objects.create(paragraph=paragraph2)
4192 book2 = Book.objects.create(chapter=chapter2)
4193
4194 sentences_not_in_pub = Book.objects.exclude(chapter__paragraph__page=page)
4195 self.assertSequenceEqual(sentences_not_in_pub, [book2])
4196
4197
4198 class Ticket12807Tests(TestCase):
4199 def test_ticket_12807(self):
4200 p1 = Paragraph.objects.create()
4201 p2 = Paragraph.objects.create()
4202 # The ORed condition below should have no effect on the query - the
4203 # ~Q(pk__in=[]) will always be True.
4204 qs = Paragraph.objects.filter((Q(pk=p2.pk) | ~Q(pk__in=[])) & Q(pk=p1.pk))
4205 self.assertSequenceEqual(qs, [p1])
4206
4207
4208 class RelatedLookupTypeTests(TestCase):
4209 error = 'Cannot query "%s": Must be "%s" instance.'
4210
4211 @classmethod
4212 def setUpTestData(cls):
4213 cls.oa = ObjectA.objects.create(name="oa")
4214 cls.poa = ProxyObjectA.objects.get(name="oa")
4215 cls.coa = ChildObjectA.objects.create(name="coa")
4216 cls.wrong_type = Order.objects.create(id=cls.oa.pk)
4217 cls.ob = ObjectB.objects.create(name="ob", objecta=cls.oa, num=1)
4218 cls.pob1 = ProxyObjectB.objects.create(name="pob", objecta=cls.oa, num=2)
4219 cls.pob = ProxyObjectB.objects.all()
4220 cls.c = ObjectC.objects.create(childobjecta=cls.coa)
4221
4222 def test_wrong_type_lookup(self):
4223 """
4224 A ValueError is raised when the incorrect object type is passed to a
4225 query lookup.
4226 """
4227 # Passing incorrect object type
4228 with self.assertRaisesMessage(
4229 ValueError, self.error % (self.wrong_type, ObjectA._meta.object_name)
4230 ):
4231 ObjectB.objects.get(objecta=self.wrong_type)
4232
4233 with self.assertRaisesMessage(
4234 ValueError, self.error % (self.wrong_type, ObjectA._meta.object_name)
4235 ):
4236 ObjectB.objects.filter(objecta__in=[self.wrong_type])
4237
4238 with self.assertRaisesMessage(
4239 ValueError, self.error % (self.wrong_type, ObjectA._meta.object_name)
4240 ):
4241 ObjectB.objects.filter(objecta=self.wrong_type)
4242
4243 with self.assertRaisesMessage(
4244 ValueError, self.error % (self.wrong_type, ObjectB._meta.object_name)
4245 ):
4246 ObjectA.objects.filter(objectb__in=[self.wrong_type, self.ob])
4247
4248 # Passing an object of the class on which query is done.
4249 with self.assertRaisesMessage(
4250 ValueError, self.error % (self.ob, ObjectA._meta.object_name)
4251 ):
4252 ObjectB.objects.filter(objecta__in=[self.poa, self.ob])
4253
4254 with self.assertRaisesMessage(
4255 ValueError, self.error % (self.ob, ChildObjectA._meta.object_name)
4256 ):
4257 ObjectC.objects.exclude(childobjecta__in=[self.coa, self.ob])
4258
4259 def test_wrong_backward_lookup(self):
4260 """
4261 A ValueError is raised when the incorrect object type is passed to a
4262 query lookup for backward relations.
4263 """
4264 with self.assertRaisesMessage(
4265 ValueError, self.error % (self.oa, ObjectB._meta.object_name)
4266 ):
4267 ObjectA.objects.filter(objectb__in=[self.oa, self.ob])
4268
4269 with self.assertRaisesMessage(
4270 ValueError, self.error % (self.oa, ObjectB._meta.object_name)
4271 ):
4272 ObjectA.objects.exclude(objectb=self.oa)
4273
4274 with self.assertRaisesMessage(
4275 ValueError, self.error % (self.wrong_type, ObjectB._meta.object_name)
4276 ):
4277 ObjectA.objects.get(objectb=self.wrong_type)
4278
4279 def test_correct_lookup(self):
4280 """
4281 When passing proxy model objects, child objects, or parent objects,
4282 lookups work fine.
4283 """
4284 out_a = [self.oa]
4285 out_b = [self.ob, self.pob1]
4286 out_c = [self.c]
4287
4288 # proxy model objects
4289 self.assertSequenceEqual(
4290 ObjectB.objects.filter(objecta=self.poa).order_by("name"), out_b
4291 )
4292 self.assertSequenceEqual(
4293 ObjectA.objects.filter(objectb__in=self.pob).order_by("pk"), out_a * 2
4294 )
4295
4296 # child objects
4297 self.assertSequenceEqual(ObjectB.objects.filter(objecta__in=[self.coa]), [])
4298 self.assertSequenceEqual(
4299 ObjectB.objects.filter(objecta__in=[self.poa, self.coa]).order_by("name"),
4300 out_b,
4301 )
4302 self.assertSequenceEqual(
4303 ObjectB.objects.filter(objecta__in=iter([self.poa, self.coa])).order_by(
4304 "name"
4305 ),
4306 out_b,
4307 )
4308
4309 # parent objects
4310 self.assertSequenceEqual(ObjectC.objects.exclude(childobjecta=self.oa), out_c)
4311
4312 # QuerySet related object type checking shouldn't issue queries
4313 # (the querysets aren't evaluated here, hence zero queries) (#23266).
4314 with self.assertNumQueries(0):
4315 ObjectB.objects.filter(objecta__in=ObjectA.objects.all())
4316
4317 def test_values_queryset_lookup(self):
4318 """
4319 ValueQuerySets are not checked for compatibility with the lookup field.
4320 """
4321 # Make sure the num and objecta field values match.
4322 ob = ObjectB.objects.get(name="ob")
4323 ob.num = ob.objecta.pk
4324 ob.save()
4325 pob = ObjectB.objects.get(name="pob")
4326 pob.num = pob.objecta.pk
4327 pob.save()
4328 self.assertSequenceEqual(
4329 ObjectB.objects.filter(
4330 objecta__in=ObjectB.objects.values_list("num")
4331 ).order_by("pk"),
4332 [ob, pob],
4333 )
4334
4335
4336 class Ticket14056Tests(TestCase):
4337 def test_ticket_14056(self):
4338 s1 = SharedConnection.objects.create(data="s1")
4339 s2 = SharedConnection.objects.create(data="s2")
4340 s3 = SharedConnection.objects.create(data="s3")
4341 PointerA.objects.create(connection=s2)
4342 expected_ordering = (
4343 [s1, s3, s2] if connection.features.nulls_order_largest else [s2, s1, s3]
4344 )
4345 self.assertSequenceEqual(
4346 SharedConnection.objects.order_by("-pointera__connection", "pk"),
4347 expected_ordering,
4348 )
4349
4350
4351 class Ticket20955Tests(TestCase):
4352 def test_ticket_20955(self):
4353 jack = Staff.objects.create(name="jackstaff")
4354 jackstaff = StaffUser.objects.create(staff=jack)
4355 jill = Staff.objects.create(name="jillstaff")
4356 jillstaff = StaffUser.objects.create(staff=jill)
4357 task = Task.objects.create(creator=jackstaff, owner=jillstaff, title="task")
4358 task_get = Task.objects.get(pk=task.pk)
4359 # Load data so that assertNumQueries doesn't complain about the get
4360 # version's queries.
4361 task_get.creator.staffuser.staff
4362 task_get.owner.staffuser.staff
4363 qs = Task.objects.select_related(
4364 "creator__staffuser__staff", "owner__staffuser__staff"
4365 )
4366 self.assertEqual(str(qs.query).count(" JOIN "), 6)
4367 task_select_related = qs.get(pk=task.pk)
4368 with self.assertNumQueries(0):
4369 self.assertEqual(
4370 task_select_related.creator.staffuser.staff,
4371 task_get.creator.staffuser.staff,
4372 )
4373 self.assertEqual(
4374 task_select_related.owner.staffuser.staff,
4375 task_get.owner.staffuser.staff,
4376 )
4377
4378
4379 class Ticket21203Tests(TestCase):
4380 def test_ticket_21203(self):
4381 p = Ticket21203Parent.objects.create(parent_bool=True)
4382 c = Ticket21203Child.objects.create(parent=p)
4383 qs = Ticket21203Child.objects.select_related("parent").defer("parent__created")
4384 self.assertSequenceEqual(qs, [c])
4385 self.assertIs(qs[0].parent.parent_bool, True)
4386
4387
4388 class ValuesJoinPromotionTests(TestCase):
4389 def test_values_no_promotion_for_existing(self):
4390 qs = Node.objects.filter(parent__parent__isnull=False)
4391 self.assertIn(" INNER JOIN ", str(qs.query))
4392 qs = qs.values("parent__parent__id")
4393 self.assertIn(" INNER JOIN ", str(qs.query))
4394 # Make sure there is a left outer join without the filter.
4395 qs = Node.objects.values("parent__parent__id")
4396 self.assertIn(" LEFT OUTER JOIN ", str(qs.query))
4397
4398 def test_non_nullable_fk_not_promoted(self):
4399 qs = ObjectB.objects.values("objecta__name")
4400 self.assertIn(" INNER JOIN ", str(qs.query))
4401
4402 def test_ticket_21376(self):
4403 a = ObjectA.objects.create()
4404 ObjectC.objects.create(objecta=a)
4405 qs = ObjectC.objects.filter(
4406 Q(objecta=a) | Q(objectb__objecta=a),
4407 )
4408 qs = qs.filter(
4409 Q(objectb=1) | Q(objecta=a),
4410 )
4411 self.assertEqual(qs.count(), 1)
4412 tblname = connection.ops.quote_name(ObjectB._meta.db_table)
4413 self.assertIn(" LEFT OUTER JOIN %s" % tblname, str(qs.query))
4414
4415
4416 class ForeignKeyToBaseExcludeTests(TestCase):
4417 def test_ticket_21787(self):
4418 sc1 = SpecialCategory.objects.create(special_name="sc1", name="sc1")
4419 sc2 = SpecialCategory.objects.create(special_name="sc2", name="sc2")
4420 sc3 = SpecialCategory.objects.create(special_name="sc3", name="sc3")
4421 c1 = CategoryItem.objects.create(category=sc1)
4422 CategoryItem.objects.create(category=sc2)
4423 self.assertSequenceEqual(
4424 SpecialCategory.objects.exclude(categoryitem__id=c1.pk).order_by("name"),
4425 [sc2, sc3],
4426 )
4427 self.assertSequenceEqual(
4428 SpecialCategory.objects.filter(categoryitem__id=c1.pk), [sc1]
4429 )
4430
4431
4432 class ReverseM2MCustomPkTests(TestCase):
4433 def test_ticket_21879(self):
4434 cpt1 = CustomPkTag.objects.create(id="cpt1", tag="cpt1")
4435 cp1 = CustomPk.objects.create(name="cp1", extra="extra")
4436 cp1.custompktag_set.add(cpt1)
4437 self.assertSequenceEqual(CustomPk.objects.filter(custompktag=cpt1), [cp1])
4438 self.assertSequenceEqual(CustomPkTag.objects.filter(custom_pk=cp1), [cpt1])
4439
4440
4441 class Ticket22429Tests(TestCase):
4442 def test_ticket_22429(self):
4443 sc1 = School.objects.create()
4444 st1 = Student.objects.create(school=sc1)
4445
4446 sc2 = School.objects.create()
4447 st2 = Student.objects.create(school=sc2)
4448
4449 cr = Classroom.objects.create(school=sc1)
4450 cr.students.add(st1)
4451
4452 queryset = Student.objects.filter(~Q(classroom__school=F("school")))
4453 self.assertSequenceEqual(queryset, [st2])
4454
4455
4456 class Ticket23605Tests(TestCase):
4457 def test_ticket_23605(self):
4458 # Test filtering on a complicated q-object from ticket's report.
4459 # The query structure is such that we have multiple nested subqueries.
4460 # The original problem was that the inner queries weren't relabeled
4461 # correctly.
4462 # See also #24090.
4463 a1 = Ticket23605A.objects.create()
4464 a2 = Ticket23605A.objects.create()
4465 c1 = Ticket23605C.objects.create(field_c0=10000.0)
4466 Ticket23605B.objects.create(
4467 field_b0=10000.0, field_b1=True, modelc_fk=c1, modela_fk=a1
4468 )
4469 complex_q = Q(
4470 pk__in=Ticket23605A.objects.filter(
4471 Q(
4472 # True for a1 as field_b0 = 10000, field_c0=10000
4473 # False for a2 as no ticket23605b found
4474 ticket23605b__field_b0__gte=1000000
4475 / F("ticket23605b__modelc_fk__field_c0")
4476 )
4477 &
4478 # True for a1 (field_b1=True)
4479 Q(ticket23605b__field_b1=True)
4480 & ~Q(
4481 ticket23605b__pk__in=Ticket23605B.objects.filter(
4482 ~(
4483 # Same filters as above commented filters, but
4484 # double-negated (one for Q() above, one for
4485 # parentheses). So, again a1 match, a2 not.
4486 Q(field_b1=True)
4487 & Q(field_b0__gte=1000000 / F("modelc_fk__field_c0"))
4488 )
4489 )
4490 )
4491 ).filter(ticket23605b__field_b1=True)
4492 )
4493 qs1 = Ticket23605A.objects.filter(complex_q)
4494 self.assertSequenceEqual(qs1, [a1])
4495 qs2 = Ticket23605A.objects.exclude(complex_q)
4496 self.assertSequenceEqual(qs2, [a2])
4497
4498
4499 class TestTicket24279(TestCase):
4500 def test_ticket_24278(self):
4501 School.objects.create()
4502 qs = School.objects.filter(Q(pk__in=()) | Q())
4503 self.assertSequenceEqual(qs, [])
4504
4505
4506 class TestInvalidValuesRelation(SimpleTestCase):
4507 def test_invalid_values(self):
4508 msg = "Field 'id' expected a number but got 'abc'."
4509 with self.assertRaisesMessage(ValueError, msg):
4510 Annotation.objects.filter(tag="abc")
4511 with self.assertRaisesMessage(ValueError, msg):
4512 Annotation.objects.filter(tag__in=[123, "abc"])
4513
4514
4515 class TestInvalidFilterArguments(TestCase):
4516 def test_filter_rejects_invalid_arguments(self):
4517 school = School.objects.create()
4518 msg = "The following kwargs are invalid: '_connector', '_negated'"
4519 with self.assertRaisesMessage(TypeError, msg):
4520 School.objects.filter(pk=school.pk, _negated=True, _connector="evil")
4521
4522
4523 class TestTicket24605(TestCase):
4524 def test_ticket_24605(self):
4525 """
4526 Subquery table names should be quoted.
4527 """
4528 i1 = Individual.objects.create(alive=True)
4529 RelatedIndividual.objects.create(related=i1)
4530 i2 = Individual.objects.create(alive=False)
4531 RelatedIndividual.objects.create(related=i2)
4532 i3 = Individual.objects.create(alive=True)
4533 i4 = Individual.objects.create(alive=False)
4534
4535 self.assertSequenceEqual(
4536 Individual.objects.filter(
4537 Q(alive=False), Q(related_individual__isnull=True)
4538 ),
4539 [i4],
4540 )
4541 self.assertSequenceEqual(
4542 Individual.objects.exclude(
4543 Q(alive=False), Q(related_individual__isnull=True)
4544 ).order_by("pk"),
4545 [i1, i2, i3],
4546 )
4547
4548
4549 class Ticket23622Tests(TestCase):
4550 @skipUnlessDBFeature("can_distinct_on_fields")
4551 def test_ticket_23622(self):
4552 """
4553 Make sure __pk__in and __in work the same for related fields when
4554 using a distinct on subquery.
4555 """
4556 a1 = Ticket23605A.objects.create()
4557 a2 = Ticket23605A.objects.create()
4558 c1 = Ticket23605C.objects.create(field_c0=0.0)
4559 Ticket23605B.objects.create(
4560 modela_fk=a1,
4561 field_b0=123,
4562 field_b1=True,
4563 modelc_fk=c1,
4564 )
4565 Ticket23605B.objects.create(
4566 modela_fk=a1,
4567 field_b0=23,
4568 field_b1=True,
4569 modelc_fk=c1,
4570 )
4571 Ticket23605B.objects.create(
4572 modela_fk=a1,
4573 field_b0=234,
4574 field_b1=True,
4575 modelc_fk=c1,
4576 )
4577 Ticket23605B.objects.create(
4578 modela_fk=a1,
4579 field_b0=12,
4580 field_b1=True,
4581 modelc_fk=c1,
4582 )
4583 Ticket23605B.objects.create(
4584 modela_fk=a2,
4585 field_b0=567,
4586 field_b1=True,
4587 modelc_fk=c1,
4588 )
4589 Ticket23605B.objects.create(
4590 modela_fk=a2,
4591 field_b0=76,
4592 field_b1=True,
4593 modelc_fk=c1,
4594 )
4595 Ticket23605B.objects.create(
4596 modela_fk=a2,
4597 field_b0=7,
4598 field_b1=True,
4599 modelc_fk=c1,
4600 )
4601 Ticket23605B.objects.create(
4602 modela_fk=a2,
4603 field_b0=56,
4604 field_b1=True,
4605 modelc_fk=c1,
4606 )
4607 qx = Q(
4608 ticket23605b__pk__in=Ticket23605B.objects.order_by(
4609 "modela_fk", "-field_b1"
4610 ).distinct("modela_fk")
4611 ) & Q(ticket23605b__field_b0__gte=300)
4612 qy = Q(
4613 ticket23605b__in=Ticket23605B.objects.order_by(
4614 "modela_fk", "-field_b1"
4615 ).distinct("modela_fk")
4616 ) & Q(ticket23605b__field_b0__gte=300)
4617 self.assertEqual(
4618 set(Ticket23605A.objects.filter(qx).values_list("pk", flat=True)),
4619 set(Ticket23605A.objects.filter(qy).values_list("pk", flat=True)),
4620 )
4621 self.assertSequenceEqual(Ticket23605A.objects.filter(qx), [a2])