Debian Codegraph @

tests/expressions/tests.py

raw
1 import datetime
2 import pickle
3 import unittest
4 import uuid
5 from collections import namedtuple
6 from copy import deepcopy
7 from decimal import Decimal
8 from typing import TYPE_CHECKING, TypeAlias
9 from unittest import mock
10
11 from django.core.exceptions import FieldError
12 from django.db import DatabaseError, NotSupportedError, connection
13 from django.db.models import (
14 AutoField,
15 Avg,
16 BinaryField,
17 BooleanField,
18 Case,
19 CharField,
20 Count,
21 DateField,
22 DateTimeField,
23 DecimalField,
24 DurationField,
25 Exists,
26 Expression,
27 ExpressionList,
28 ExpressionWrapper,
29 F,
30 FloatField,
31 Func,
32 IntegerField,
33 JSONField,
34 Max,
35 Min,
36 Model,
37 OrderBy,
38 OuterRef,
39 PositiveIntegerField,
40 Q,
41 StdDev,
42 Subquery,
43 Sum,
44 TimeField,
45 UUIDField,
46 Value,
47 Variance,
48 When,
49 )
50 from django.db.models.expressions import (
51 Col,
52 ColPairs,
53 Combinable,
54 CombinedExpression,
55 NegatedExpression,
56 OutputFieldIsNoneError,
57 RawSQL,
58 Ref,
59 )
60 from django.db.models.functions import (
61 Coalesce,
62 Concat,
63 ExtractDay,
64 Left,
65 Length,
66 Lower,
67 Substr,
68 TruncDate,
69 Upper,
70 )
71 from django.db.models.sql import constants
72 from django.db.models.sql.datastructures import Join
73 from django.test import SimpleTestCase, TestCase, skipUnlessDBFeature
74 from django.test.utils import (
75 Approximate,
76 CaptureQueriesContext,
77 isolate_apps,
78 register_lookup,
79 )
80 from django.utils.functional import SimpleLazyObject
81 from django.utils.version import PY314
82
83 from .models import (
84 UUID,
85 UUIDPK,
86 Company,
87 Employee,
88 Experiment,
89 JSONFieldModel,
90 Manager,
91 Number,
92 RemoteEmployee,
93 Result,
94 SimulationRun,
95 Text,
96 Time,
97 )
98
99 if TYPE_CHECKING:
100 AnnotatedKwarg: TypeAlias = str
101
102
103 class BasicExpressionsTests(TestCase):
104 @classmethod
105 def setUpTestData(cls):
106 cls.example_inc = Company.objects.create(
107 name="Example Inc.",
108 num_employees=2300,
109 num_chairs=5,
110 ceo=Employee.objects.create(firstname="Joe", lastname="Smith", salary=10),
111 )
112 cls.foobar_ltd = Company.objects.create(
113 name="Foobar Ltd.",
114 num_employees=3,
115 num_chairs=4,
116 based_in_eu=True,
117 ceo=Employee.objects.create(firstname="Frank", lastname="Meyer", salary=20),
118 )
119 cls.max = Employee.objects.create(
120 firstname="Max", lastname="Mustermann", salary=30
121 )
122 cls.gmbh = Company.objects.create(
123 name="Test GmbH", num_employees=32, num_chairs=1, ceo=cls.max
124 )
125
126 def setUp(self):
127 self.company_query = Company.objects.values(
128 "name", "num_employees", "num_chairs"
129 ).order_by("name", "num_employees", "num_chairs")
130
131 def test_annotate_values_aggregate(self):
132 companies = (
133 Company.objects.annotate(
134 salaries=F("ceo__salary"),
135 )
136 .values("num_employees", "salaries")
137 .aggregate(
138 result=Sum(
139 F("salaries") + F("num_employees"), output_field=IntegerField()
140 ),
141 )
142 )
143 self.assertEqual(companies["result"], 2395)
144
145 def test_annotate_values_filter(self):
146 companies = (
147 Company.objects.annotate(
148 foo=RawSQL("%s", ["value"]),
149 )
150 .filter(foo="value")
151 .order_by("name")
152 )
153 self.assertSequenceEqual(
154 companies,
155 [self.example_inc, self.foobar_ltd, self.gmbh],
156 )
157
158 def test_annotate_values_count(self):
159 companies = Company.objects.annotate(foo=RawSQL("%s", ["value"]))
160 self.assertEqual(companies.count(), 3)
161
162 @skipUnlessDBFeature("supports_boolean_expr_in_select_clause")
163 def test_filtering_on_annotate_that_uses_q(self):
164 self.assertEqual(
165 Company.objects.annotate(
166 num_employees_check=ExpressionWrapper(
167 Q(num_employees__gt=3), output_field=BooleanField()
168 )
169 )
170 .filter(num_employees_check=True)
171 .count(),
172 2,
173 )
174
175 def test_filtering_on_q_that_is_boolean(self):
176 self.assertEqual(
177 Company.objects.filter(
178 ExpressionWrapper(Q(num_employees__gt=3), output_field=BooleanField())
179 ).count(),
180 2,
181 )
182
183 def test_filtering_on_rawsql_that_is_boolean(self):
184 self.assertEqual(
185 Company.objects.filter(
186 RawSQL("num_employees > %s", (3,), output_field=BooleanField()),
187 ).count(),
188 2,
189 )
190
191 def test_filter_inter_attribute(self):
192 # We can filter on attribute relationships on same model obj, e.g.
193 # find companies where the number of employees is greater
194 # than the number of chairs.
195 self.assertSequenceEqual(
196 self.company_query.filter(num_employees__gt=F("num_chairs")),
197 [
198 {
199 "num_chairs": 5,
200 "name": "Example Inc.",
201 "num_employees": 2300,
202 },
203 {"num_chairs": 1, "name": "Test GmbH", "num_employees": 32},
204 ],
205 )
206
207 def test_update(self):
208 # We can set one field to have the value of another field
209 # Make sure we have enough chairs
210 self.company_query.update(num_chairs=F("num_employees"))
211 self.assertSequenceEqual(
212 self.company_query,
213 [
214 {"num_chairs": 2300, "name": "Example Inc.", "num_employees": 2300},
215 {"num_chairs": 3, "name": "Foobar Ltd.", "num_employees": 3},
216 {"num_chairs": 32, "name": "Test GmbH", "num_employees": 32},
217 ],
218 )
219
220 def _test_slicing_of_f_expressions(self, model):
221 tests = [
222 (F("name")[:], "Example Inc."),
223 (F("name")[:7], "Example"),
224 (F("name")[:6][:5], "Examp"), # Nested slicing.
225 (F("name")[0], "E"),
226 (F("name")[13], ""),
227 (F("name")[8:], "Inc."),
228 (F("name")[0:15], "Example Inc."),
229 (F("name")[2:7], "ample"),
230 (F("name")[1:][:3], "xam"),
231 (F("name")[2:2], ""),
232 ]
233 for expression, expected in tests:
234 with self.subTest(expression=expression, expected=expected):
235 obj = model.objects.get(name="Example Inc.")
236 try:
237 obj.name = expression
238 obj.save(update_fields=["name"])
239 obj.refresh_from_db()
240 self.assertEqual(obj.name, expected)
241 finally:
242 obj.name = "Example Inc."
243 obj.save(update_fields=["name"])
244
245 def test_slicing_of_f_expressions_charfield(self):
246 self._test_slicing_of_f_expressions(Company)
247
248 def test_slicing_of_f_expressions_textfield(self):
249 Text.objects.bulk_create(
250 [Text(name=company.name) for company in Company.objects.all()]
251 )
252 self._test_slicing_of_f_expressions(Text)
253
254 def test_slicing_of_f_expressions_with_annotate(self):
255 qs = Company.objects.annotate(
256 first_three=F("name")[:3],
257 after_three=F("name")[3:],
258 random_four=F("name")[2:5],
259 first_letter_slice=F("name")[:1],
260 first_letter_index=F("name")[0],
261 )
262 tests = [
263 ("first_three", ["Exa", "Foo", "Tes"]),
264 ("after_three", ["mple Inc.", "bar Ltd.", "t GmbH"]),
265 ("random_four", ["amp", "oba", "st "]),
266 ("first_letter_slice", ["E", "F", "T"]),
267 ("first_letter_index", ["E", "F", "T"]),
268 ]
269 for annotation, expected in tests:
270 with self.subTest(annotation):
271 self.assertCountEqual(qs.values_list(annotation, flat=True), expected)
272
273 def test_slicing_of_f_expression_with_annotated_expression(self):
274 qs = Company.objects.annotate(
275 new_name=Case(
276 When(based_in_eu=True, then=Concat(Value("EU:"), F("name"))),
277 default=F("name"),
278 ),
279 first_two=F("new_name")[:3],
280 )
281 self.assertCountEqual(
282 qs.values_list("first_two", flat=True),
283 ["Exa", "EU:", "Tes"],
284 )
285
286 def test_slicing_of_f_expressions_with_negative_index(self):
287 msg = "Negative indexing is not supported."
288 indexes = [slice(0, -4), slice(-4, 0), slice(-4), -5]
289 for i in indexes:
290 with self.subTest(i=i), self.assertRaisesMessage(ValueError, msg):
291 F("name")[i]
292
293 def test_slicing_of_f_expressions_with_slice_stop_less_than_slice_start(self):
294 msg = "Slice stop must be greater than slice start."
295 with self.assertRaisesMessage(ValueError, msg):
296 F("name")[4:2]
297
298 def test_slicing_of_f_expressions_with_invalid_type(self):
299 msg = "Argument to slice must be either int or slice instance."
300 with self.assertRaisesMessage(TypeError, msg):
301 F("name")["error"]
302
303 def test_slicing_of_f_expressions_with_step(self):
304 msg = "Step argument is not supported."
305 with self.assertRaisesMessage(ValueError, msg):
306 F("name")[::4]
307
308 def test_slicing_of_f_unsupported_field(self):
309 msg = "This field does not support slicing."
310 with self.assertRaisesMessage(NotSupportedError, msg):
311 Company.objects.update(num_chairs=F("num_chairs")[:4])
312
313 def test_slicing_of_outerref(self):
314 inner = Company.objects.filter(name__startswith=OuterRef("ceo__firstname")[0])
315 outer = Company.objects.filter(Exists(inner)).values_list("name", flat=True)
316 self.assertSequenceEqual(outer, ["Foobar Ltd."])
317
318 def test_arithmetic(self):
319 # We can perform arithmetic operations in expressions
320 # Make sure we have 2 spare chairs
321 self.company_query.update(num_chairs=F("num_employees") + 2)
322 self.assertSequenceEqual(
323 self.company_query,
324 [
325 {"num_chairs": 2302, "name": "Example Inc.", "num_employees": 2300},
326 {"num_chairs": 5, "name": "Foobar Ltd.", "num_employees": 3},
327 {"num_chairs": 34, "name": "Test GmbH", "num_employees": 32},
328 ],
329 )
330
331 def test_order_of_operations(self):
332 # Law of order of operations is followed
333 self.company_query.update(
334 num_chairs=F("num_employees") + 2 * F("num_employees")
335 )
336 self.assertSequenceEqual(
337 self.company_query,
338 [
339 {"num_chairs": 6900, "name": "Example Inc.", "num_employees": 2300},
340 {"num_chairs": 9, "name": "Foobar Ltd.", "num_employees": 3},
341 {"num_chairs": 96, "name": "Test GmbH", "num_employees": 32},
342 ],
343 )
344
345 def test_parenthesis_priority(self):
346 # Law of order of operations can be overridden by parentheses
347 self.company_query.update(
348 num_chairs=(F("num_employees") + 2) * F("num_employees")
349 )
350 self.assertSequenceEqual(
351 self.company_query,
352 [
353 {"num_chairs": 5294600, "name": "Example Inc.", "num_employees": 2300},
354 {"num_chairs": 15, "name": "Foobar Ltd.", "num_employees": 3},
355 {"num_chairs": 1088, "name": "Test GmbH", "num_employees": 32},
356 ],
357 )
358
359 def test_update_with_fk(self):
360 # ForeignKey can become updated with the value of another ForeignKey.
361 self.assertEqual(Company.objects.update(point_of_contact=F("ceo")), 3)
362 self.assertQuerySetEqual(
363 Company.objects.all(),
364 ["Joe Smith", "Frank Meyer", "Max Mustermann"],
365 lambda c: str(c.point_of_contact),
366 ordered=False,
367 )
368
369 def test_update_with_none(self):
370 Number.objects.create(integer=1, float=1.0)
371 Number.objects.create(integer=2)
372 Number.objects.filter(float__isnull=False).update(float=Value(None))
373 self.assertQuerySetEqual(
374 Number.objects.all(), [None, None], lambda n: n.float, ordered=False
375 )
376
377 @skipUnlessDBFeature("supports_json_field")
378 def test_update_jsonfield_case_when_key_is_null(self):
379 obj = JSONFieldModel.objects.create(data={"key": None})
380 updated = JSONFieldModel.objects.update(
381 data=Case(
382 When(
383 data__key=Value(None, JSONField()),
384 then=Value({"key": "something"}, JSONField()),
385 ),
386 )
387 )
388 self.assertEqual(updated, 1)
389 obj.refresh_from_db()
390 self.assertEqual(obj.data, {"key": "something"})
391
392 def test_filter_with_join(self):
393 # F Expressions can also span joins
394 Company.objects.update(point_of_contact=F("ceo"))
395 c = Company.objects.first()
396 c.point_of_contact = Employee.objects.create(
397 firstname="Guido", lastname="van Rossum"
398 )
399 c.save()
400
401 self.assertQuerySetEqual(
402 Company.objects.filter(ceo__firstname=F("point_of_contact__firstname")),
403 ["Foobar Ltd.", "Test GmbH"],
404 lambda c: c.name,
405 ordered=False,
406 )
407
408 Company.objects.exclude(ceo__firstname=F("point_of_contact__firstname")).update(
409 name="foo"
410 )
411 self.assertEqual(
412 Company.objects.exclude(ceo__firstname=F("point_of_contact__firstname"))
413 .get()
414 .name,
415 "foo",
416 )
417
418 msg = "Joined field references are not permitted in this query"
419 with self.assertRaisesMessage(FieldError, msg):
420 Company.objects.exclude(
421 ceo__firstname=F("point_of_contact__firstname")
422 ).update(name=F("point_of_contact__lastname"))
423
424 def test_object_update(self):
425 # F expressions can be used to update attributes on single objects
426 self.gmbh.num_employees = F("num_employees") + 4
427 self.gmbh.save()
428 self.gmbh.refresh_from_db()
429 self.assertEqual(self.gmbh.num_employees, 36)
430
431 def test_new_object_save(self):
432 # We should be able to use Funcs when inserting new data
433 test_co = Company(
434 name=Lower(Value("UPPER")), num_employees=32, num_chairs=1, ceo=self.max
435 )
436 test_co.save()
437 test_co.refresh_from_db()
438 self.assertEqual(test_co.name, "upper")
439
440 def test_new_object_create(self):
441 test_co = Company.objects.create(
442 name=Lower(Value("UPPER")), num_employees=32, num_chairs=1, ceo=self.max
443 )
444 test_co.refresh_from_db()
445 self.assertEqual(test_co.name, "upper")
446
447 def test_object_create_with_aggregate(self):
448 # Aggregates are not allowed when inserting new data
449 msg = (
450 "Aggregate functions are not allowed in this query "
451 "(num_employees=Max(Value(1)))."
452 )
453 with self.assertRaisesMessage(FieldError, msg):
454 Company.objects.create(
455 name="Company",
456 num_employees=Max(Value(1)),
457 num_chairs=1,
458 ceo=Employee.objects.create(
459 firstname="Just", lastname="Doit", salary=30
460 ),
461 )
462
463 def test_object_update_fk(self):
464 # F expressions cannot be used to update attributes which are foreign
465 # keys, or attributes which involve joins.
466 test_gmbh = Company.objects.get(pk=self.gmbh.pk)
467 msg = 'F(ceo)": "Company.point_of_contact" must be a "Employee" instance.'
468 with self.assertRaisesMessage(ValueError, msg):
469 test_gmbh.point_of_contact = F("ceo")
470
471 test_gmbh.point_of_contact = self.gmbh.ceo
472 test_gmbh.save()
473 test_gmbh.name = F("ceo__lastname")
474 msg = "Joined field references are not permitted in this query"
475 with self.assertRaisesMessage(FieldError, msg):
476 test_gmbh.save()
477
478 def test_update_inherited_field_value(self):
479 msg = "Joined field references are not permitted in this query"
480 with self.assertRaisesMessage(FieldError, msg):
481 RemoteEmployee.objects.update(adjusted_salary=F("salary") * 5)
482
483 def test_object_update_unsaved_objects(self):
484 # F expressions cannot be used to update attributes on objects which do
485 # not yet exist in the database
486 acme = Company(
487 name="The Acme Widget Co.", num_employees=12, num_chairs=5, ceo=self.max
488 )
489 acme.num_employees = F("num_employees") + 16
490 msg = (
491 'Failed to insert expression "Col(expressions_company, '
492 'expressions.Company.num_employees) + Value(16)" on '
493 "expressions.Company.num_employees. F() expressions can only be "
494 "used to update, not to insert."
495 )
496 with self.assertRaisesMessage(ValueError, msg):
497 acme.save()
498
499 acme.num_employees = 12
500 acme.name = Lower(F("name"))
501 msg = (
502 'Failed to insert expression "Lower(Col(expressions_company, '
503 'expressions.Company.name))" on expressions.Company.name. F() '
504 "expressions can only be used to update, not to insert."
505 )
506 with self.assertRaisesMessage(ValueError, msg):
507 acme.save()
508
509 def test_ticket_11722_iexact_lookup(self):
510 Employee.objects.create(firstname="John", lastname="Doe")
511 test = Employee.objects.create(firstname="Test", lastname="test")
512
513 queryset = Employee.objects.filter(firstname__iexact=F("lastname"))
514 self.assertSequenceEqual(queryset, [test])
515
516 def test_ticket_16731_startswith_lookup(self):
517 Employee.objects.create(firstname="John", lastname="Doe")
518 e2 = Employee.objects.create(firstname="Jack", lastname="Jackson")
519 e3 = Employee.objects.create(firstname="Jack", lastname="jackson")
520 self.assertSequenceEqual(
521 Employee.objects.filter(lastname__startswith=F("firstname")),
522 [e2, e3] if connection.features.has_case_insensitive_like else [e2],
523 )
524 qs = Employee.objects.filter(lastname__istartswith=F("firstname")).order_by(
525 "pk"
526 )
527 self.assertSequenceEqual(qs, [e2, e3])
528
529 def test_ticket_18375_join_reuse(self):
530 # Reverse multijoin F() references and the lookup target the same join.
531 # Pre #18375 the F() join was generated first and the lookup couldn't
532 # reuse that join.
533 qs = Employee.objects.filter(
534 company_ceo_set__num_chairs=F("company_ceo_set__num_employees")
535 )
536 self.assertEqual(str(qs.query).count("JOIN"), 1)
537
538 def test_ticket_18375_kwarg_ordering(self):
539 # The next query was dict-randomization dependent - if the "gte=1"
540 # was seen first, then the F() will reuse the join generated by the
541 # gte lookup, if F() was seen first, then it generated a join the
542 # other lookups could not reuse.
543 qs = Employee.objects.filter(
544 company_ceo_set__num_chairs=F("company_ceo_set__num_employees"),
545 company_ceo_set__num_chairs__gte=1,
546 )
547 self.assertEqual(str(qs.query).count("JOIN"), 1)
548
549 def test_ticket_18375_kwarg_ordering_2(self):
550 # Another similar case for F() than above. Now we have the same join
551 # in two filter kwargs, one in the lhs lookup, one in F. Here pre
552 # #18375 the amount of joins generated was random if dict
553 # randomization was enabled, that is the generated query dependent
554 # on which clause was seen first.
555 qs = Employee.objects.filter(
556 company_ceo_set__num_employees=F("pk"),
557 pk=F("company_ceo_set__num_employees"),
558 )
559 self.assertEqual(str(qs.query).count("JOIN"), 1)
560
561 def test_ticket_18375_chained_filters(self):
562 # F() expressions do not reuse joins from previous filter.
563 qs = Employee.objects.filter(company_ceo_set__num_employees=F("pk")).filter(
564 company_ceo_set__num_employees=F("company_ceo_set__num_employees")
565 )
566 self.assertEqual(str(qs.query).count("JOIN"), 2)
567
568 def test_order_by_exists(self):
569 mary = Employee.objects.create(
570 firstname="Mary", lastname="Mustermann", salary=20
571 )
572 mustermanns_by_seniority = Employee.objects.filter(
573 lastname="Mustermann"
574 ).order_by(
575 # Order by whether the employee is the CEO of a company
576 Exists(Company.objects.filter(ceo=OuterRef("pk"))).desc()
577 )
578 self.assertSequenceEqual(mustermanns_by_seniority, [self.max, mary])
579
580 def test_order_by_multiline_sql(self):
581 raw_order_by = (
582 RawSQL(
583 """
584 CASE WHEN num_employees > 1000
585 THEN num_chairs
586 ELSE 0 END
587 """,
588 [],
589 ).desc(),
590 RawSQL(
591 """
592 CASE WHEN num_chairs > 1
593 THEN 1
594 ELSE 0 END
595 """,
596 [],
597 ).asc(),
598 )
599 for qs in (
600 Company.objects.all(),
601 Company.objects.distinct(),
602 ):
603 with self.subTest(qs=qs):
604 self.assertSequenceEqual(
605 qs.order_by(*raw_order_by),
606 [self.example_inc, self.gmbh, self.foobar_ltd],
607 )
608
609 def test_outerref(self):
610 inner = Company.objects.filter(point_of_contact=OuterRef("pk"))
611 msg = (
612 "This queryset contains a reference to an outer query and may only "
613 "be used in a subquery."
614 )
615 with self.assertRaisesMessage(ValueError, msg):
616 inner.exists()
617
618 outer = Employee.objects.annotate(is_point_of_contact=Exists(inner))
619 self.assertIs(outer.exists(), True)
620
621 def test_exist_single_field_output_field(self):
622 queryset = Company.objects.values("pk")
623 self.assertIsInstance(Exists(queryset).output_field, BooleanField)
624
625 def test_subquery(self):
626 Company.objects.filter(name="Example Inc.").update(
627 point_of_contact=Employee.objects.get(firstname="Joe", lastname="Smith"),
628 ceo=self.max,
629 )
630 Employee.objects.create(firstname="Bob", lastname="Brown", salary=40)
631 qs = (
632 Employee.objects.annotate(
633 is_point_of_contact=Exists(
634 Company.objects.filter(point_of_contact=OuterRef("pk"))
635 ),
636 is_not_point_of_contact=~Exists(
637 Company.objects.filter(point_of_contact=OuterRef("pk"))
638 ),
639 is_ceo_of_small_company=Exists(
640 Company.objects.filter(num_employees__lt=200, ceo=OuterRef("pk"))
641 ),
642 is_ceo_small_2=~~Exists(
643 Company.objects.filter(num_employees__lt=200, ceo=OuterRef("pk"))
644 ),
645 largest_company=Subquery(
646 Company.objects.order_by("-num_employees")
647 .filter(Q(ceo=OuterRef("pk")) | Q(point_of_contact=OuterRef("pk")))
648 .values("name")[:1],
649 output_field=CharField(),
650 ),
651 )
652 .values(
653 "firstname",
654 "is_point_of_contact",
655 "is_not_point_of_contact",
656 "is_ceo_of_small_company",
657 "is_ceo_small_2",
658 "largest_company",
659 )
660 .order_by("firstname")
661 )
662
663 results = list(qs)
664 # Could use Coalesce(subq, Value('')) instead except for the bug in
665 # oracledb mentioned in #23843.
666 bob = results[0]
667 if (
668 bob["largest_company"] == ""
669 and connection.features.interprets_empty_strings_as_nulls
670 ):
671 bob["largest_company"] = None
672
673 self.assertEqual(
674 results,
675 [
676 {
677 "firstname": "Bob",
678 "is_point_of_contact": False,
679 "is_not_point_of_contact": True,
680 "is_ceo_of_small_company": False,
681 "is_ceo_small_2": False,
682 "largest_company": None,
683 },
684 {
685 "firstname": "Frank",
686 "is_point_of_contact": False,
687 "is_not_point_of_contact": True,
688 "is_ceo_of_small_company": True,
689 "is_ceo_small_2": True,
690 "largest_company": "Foobar Ltd.",
691 },
692 {
693 "firstname": "Joe",
694 "is_point_of_contact": True,
695 "is_not_point_of_contact": False,
696 "is_ceo_of_small_company": False,
697 "is_ceo_small_2": False,
698 "largest_company": "Example Inc.",
699 },
700 {
701 "firstname": "Max",
702 "is_point_of_contact": False,
703 "is_not_point_of_contact": True,
704 "is_ceo_of_small_company": True,
705 "is_ceo_small_2": True,
706 "largest_company": "Example Inc.",
707 },
708 ],
709 )
710 # A less elegant way to write the same query: this uses a LEFT OUTER
711 # JOIN and an IS NULL, inside a WHERE NOT IN which is probably less
712 # efficient than EXISTS.
713 self.assertCountEqual(
714 qs.filter(is_point_of_contact=True).values("pk"),
715 Employee.objects.exclude(company_point_of_contact_set=None).values("pk"),
716 )
717
718 def test_subquery_eq(self):
719 qs = Employee.objects.annotate(
720 is_ceo=Exists(Company.objects.filter(ceo=OuterRef("pk"))),
721 is_point_of_contact=Exists(
722 Company.objects.filter(point_of_contact=OuterRef("pk")),
723 ),
724 small_company=Exists(
725 queryset=Company.objects.filter(num_employees__lt=200),
726 ),
727 ).filter(is_ceo=True, is_point_of_contact=False, small_company=True)
728 self.assertNotEqual(
729 qs.query.annotations["is_ceo"],
730 qs.query.annotations["is_point_of_contact"],
731 )
732 self.assertNotEqual(
733 qs.query.annotations["is_ceo"],
734 qs.query.annotations["small_company"],
735 )
736
737 def test_subquery_sql(self):
738 employees = Employee.objects.all()
739 employees_subquery = Subquery(employees)
740 self.assertIs(employees_subquery.query.subquery, True)
741 self.assertIs(employees.query.subquery, False)
742 compiler = employees_subquery.query.get_compiler(connection=connection)
743 sql, _ = employees_subquery.as_sql(compiler, connection)
744 self.assertIn("(SELECT ", sql)
745
746 def test_in_subquery(self):
747 # This is a contrived test (and you really wouldn't write this query),
748 # but it is a succinct way to test the __in=Subquery() construct.
749 small_companies = Company.objects.filter(num_employees__lt=200).values("pk")
750 subquery_test = Company.objects.filter(pk__in=Subquery(small_companies))
751 self.assertCountEqual(subquery_test, [self.foobar_ltd, self.gmbh])
752 subquery_test2 = Company.objects.filter(
753 pk=Subquery(small_companies.filter(num_employees=3))
754 )
755 self.assertCountEqual(subquery_test2, [self.foobar_ltd])
756
757 def test_uuid_pk_subquery(self):
758 u = UUIDPK.objects.create()
759 UUID.objects.create(uuid_fk=u)
760 qs = UUIDPK.objects.filter(id__in=Subquery(UUID.objects.values("uuid_fk__id")))
761 self.assertCountEqual(qs, [u])
762
763 def test_nested_subquery(self):
764 inner = Company.objects.filter(point_of_contact=OuterRef("pk"))
765 outer = Employee.objects.annotate(is_point_of_contact=Exists(inner))
766 contrived = Employee.objects.annotate(
767 is_point_of_contact=Subquery(
768 outer.filter(pk=OuterRef("pk")).values("is_point_of_contact"),
769 output_field=BooleanField(),
770 ),
771 )
772 self.assertCountEqual(contrived.values_list(), outer.values_list())
773
774 def test_nested_subquery_join_outer_ref(self):
775 inner = Employee.objects.filter(pk=OuterRef("ceo__pk")).values("pk")
776 qs = Employee.objects.annotate(
777 ceo_company=Subquery(
778 Company.objects.filter(
779 ceo__in=inner,
780 ceo__pk=OuterRef("pk"),
781 ).values("pk"),
782 ),
783 )
784 self.assertSequenceEqual(
785 qs.values_list("ceo_company", flat=True),
786 [self.example_inc.pk, self.foobar_ltd.pk, self.gmbh.pk],
787 )
788
789 def test_nested_subquery_outer_ref_2(self):
790 first = Time.objects.create(time="09:00")
791 second = Time.objects.create(time="17:00")
792 third = Time.objects.create(time="21:00")
793 SimulationRun.objects.bulk_create(
794 [
795 SimulationRun(start=first, end=second, midpoint="12:00"),
796 SimulationRun(start=first, end=third, midpoint="15:00"),
797 SimulationRun(start=second, end=first, midpoint="00:00"),
798 ]
799 )
800 inner = Time.objects.filter(
801 time=OuterRef(OuterRef("time")), pk=OuterRef("start")
802 ).values("time")
803 middle = SimulationRun.objects.annotate(other=Subquery(inner)).values("other")[
804 :1
805 ]
806 outer = Time.objects.annotate(other=Subquery(middle, output_field=TimeField()))
807 # This is a contrived example. It exercises the double OuterRef form.
808 self.assertCountEqual(outer, [first, second, third])
809
810 def test_nested_subquery_outer_ref_with_autofield(self):
811 first = Time.objects.create(time="09:00")
812 second = Time.objects.create(time="17:00")
813 SimulationRun.objects.create(start=first, end=second, midpoint="12:00")
814 inner = SimulationRun.objects.filter(start=OuterRef(OuterRef("pk"))).values(
815 "start"
816 )
817 middle = Time.objects.annotate(other=Subquery(inner)).values("other")[:1]
818 outer = Time.objects.annotate(
819 other=Subquery(middle, output_field=IntegerField())
820 )
821 # This exercises the double OuterRef form with AutoField as pk.
822 self.assertCountEqual(outer, [first, second])
823
824 def test_annotations_within_subquery(self):
825 Company.objects.filter(num_employees__lt=50).update(
826 ceo=Employee.objects.get(firstname="Frank")
827 )
828 inner = (
829 Company.objects.filter(ceo=OuterRef("pk"))
830 .values("ceo")
831 .annotate(total_employees=Sum("num_employees"))
832 .values("total_employees")
833 )
834 outer = Employee.objects.annotate(total_employees=Subquery(inner)).filter(
835 salary__lte=Subquery(inner)
836 )
837 self.assertSequenceEqual(
838 outer.order_by("-total_employees").values("salary", "total_employees"),
839 [
840 {"salary": 10, "total_employees": 2300},
841 {"salary": 20, "total_employees": 35},
842 ],
843 )
844
845 def test_subquery_references_joined_table_twice(self):
846 inner = Company.objects.filter(
847 num_chairs__gte=OuterRef("ceo__salary"),
848 num_employees__gte=OuterRef("point_of_contact__salary"),
849 )
850 # Another contrived example (there is no need to have a subquery here)
851 outer = Company.objects.filter(pk__in=Subquery(inner.values("pk")))
852 self.assertFalse(outer.exists())
853
854 def test_subquery_filter_by_aggregate(self):
855 Number.objects.create(integer=1000, float=1.2)
856 Employee.objects.create(salary=1000)
857 qs = Number.objects.annotate(
858 min_valuable_count=Subquery(
859 Employee.objects.filter(
860 salary=OuterRef("integer"),
861 )
862 .annotate(cnt=Count("salary"))
863 .filter(cnt__gt=0)
864 .values("cnt")[:1]
865 ),
866 )
867 self.assertEqual(qs.get().float, 1.2)
868
869 def test_subquery_filter_by_lazy(self):
870 self.max.manager = Manager.objects.create(name="Manager")
871 self.max.save()
872 max_manager = SimpleLazyObject(
873 lambda: Manager.objects.get(pk=self.max.manager.pk)
874 )
875 qs = Company.objects.annotate(
876 ceo_manager=Subquery(
877 Employee.objects.filter(
878 lastname=OuterRef("ceo__lastname"),
879 ).values("manager"),
880 ),
881 ).filter(ceo_manager=max_manager)
882 self.assertEqual(qs.get(), self.gmbh)
883
884 def test_aggregate_subquery_annotation(self):
885 with self.assertNumQueries(1) as ctx:
886 aggregate = Company.objects.annotate(
887 ceo_salary=Subquery(
888 Employee.objects.filter(
889 id=OuterRef("ceo_id"),
890 ).values("salary")
891 ),
892 ).aggregate(
893 ceo_salary_gt_20=Count("pk", filter=Q(ceo_salary__gt=20)),
894 )
895 self.assertEqual(aggregate, {"ceo_salary_gt_20": 1})
896 # Aggregation over a subquery annotation doesn't annotate the subquery
897 # twice in the inner query.
898 sql = ctx.captured_queries[0]["sql"]
899 self.assertLessEqual(sql.count("SELECT"), 3)
900 # GROUP BY isn't required to aggregate over a query that doesn't
901 # contain nested aggregates.
902 self.assertNotIn("GROUP BY", sql)
903
904 def test_object_create_with_f_expression_in_subquery(self):
905 Company.objects.create(
906 name="Big company", num_employees=100000, num_chairs=1, ceo=self.max
907 )
908 biggest_company = Company.objects.create(
909 name="Biggest company",
910 num_chairs=1,
911 ceo=self.max,
912 num_employees=Subquery(
913 Company.objects.order_by("-num_employees")
914 .annotate(max_num_employees=Max("num_employees"))
915 .annotate(new_num_employees=F("max_num_employees") + 1)
916 .values("new_num_employees")[:1]
917 ),
918 )
919 biggest_company.refresh_from_db()
920 self.assertEqual(biggest_company.num_employees, 100001)
921
922 @skipUnlessDBFeature("supports_over_clause")
923 def test_aggregate_rawsql_annotation(self):
924 with self.assertNumQueries(1) as ctx:
925 aggregate = Company.objects.annotate(
926 salary=RawSQL("SUM(num_chairs) OVER (ORDER BY num_employees)", []),
927 ).aggregate(
928 count=Count("pk"),
929 )
930 self.assertEqual(aggregate, {"count": 3})
931 sql = ctx.captured_queries[0]["sql"]
932 self.assertNotIn("GROUP BY", sql)
933
934 def test_explicit_output_field(self):
935 class FuncA(Func):
936 output_field = CharField()
937
938 class FuncB(Func):
939 pass
940
941 expr = FuncB(FuncA())
942 self.assertEqual(expr.output_field, FuncA.output_field)
943
944 def test_outerref_mixed_case_table_name(self):
945 inner = Result.objects.filter(result_time__gte=OuterRef("experiment__assigned"))
946 outer = Result.objects.filter(pk__in=Subquery(inner.values("pk")))
947 self.assertFalse(outer.exists())
948
949 def test_outerref_with_operator(self):
950 inner = Company.objects.filter(num_employees=OuterRef("ceo__salary") + 2)
951 outer = Company.objects.filter(pk__in=Subquery(inner.values("pk")))
952 self.assertEqual(outer.get().name, "Test GmbH")
953
954 def test_nested_outerref_with_function(self):
955 self.gmbh.point_of_contact = Employee.objects.get(lastname="Meyer")
956 self.gmbh.save()
957 inner = Employee.objects.filter(
958 lastname__startswith=Left(OuterRef(OuterRef("lastname")), 1),
959 )
960 qs = Employee.objects.annotate(
961 ceo_company=Subquery(
962 Company.objects.filter(
963 point_of_contact__in=inner,
964 ceo__pk=OuterRef("pk"),
965 ).values("name"),
966 ),
967 ).filter(ceo_company__isnull=False)
968 self.assertEqual(qs.get().ceo_company, "Test GmbH")
969
970 def test_annotation_with_outerref(self):
971 gmbh_salary = Company.objects.annotate(
972 max_ceo_salary_raise=Subquery(
973 Company.objects.annotate(
974 salary_raise=OuterRef("num_employees") + F("num_employees"),
975 )
976 .order_by("-salary_raise")
977 .values("salary_raise")[:1],
978 output_field=IntegerField(),
979 ),
980 ).get(pk=self.gmbh.pk)
981 self.assertEqual(gmbh_salary.max_ceo_salary_raise, 2332)
982
983 def test_annotation_with_nested_outerref(self):
984 self.gmbh.point_of_contact = Employee.objects.get(lastname="Meyer")
985 self.gmbh.save()
986 inner = Employee.objects.annotate(
987 outer_lastname=OuterRef(OuterRef("lastname")),
988 ).filter(lastname__startswith=Left("outer_lastname", 1))
989 qs = Employee.objects.annotate(
990 ceo_company=Subquery(
991 Company.objects.filter(
992 point_of_contact__in=inner,
993 ceo__pk=OuterRef("pk"),
994 ).values("name"),
995 ),
996 ).filter(ceo_company__isnull=False)
997 self.assertEqual(qs.get().ceo_company, "Test GmbH")
998
999 def test_annotation_with_deeply_nested_outerref(self):
1000 bob = Employee.objects.create(firstname="Bob", based_in_eu=True)
1001 self.max.manager = Manager.objects.create(name="Rock", secretary=bob)
1002 self.max.save()
1003 qs = Employee.objects.filter(
1004 Exists(
1005 Manager.objects.filter(
1006 Exists(
1007 Employee.objects.filter(
1008 pk=OuterRef("secretary__pk"),
1009 )
1010 .annotate(
1011 secretary_based_in_eu=OuterRef(OuterRef("based_in_eu"))
1012 )
1013 .filter(
1014 Exists(
1015 Company.objects.filter(
1016 # Inner OuterRef refers to an outer
1017 # OuterRef (not ResolvedOuterRef).
1018 based_in_eu=OuterRef("secretary_based_in_eu")
1019 )
1020 )
1021 )
1022 ),
1023 secretary__pk=OuterRef("pk"),
1024 )
1025 )
1026 )
1027 self.assertEqual(qs.get(), bob)
1028
1029 def test_pickle_expression(self):
1030 expr = Value(1)
1031 expr.convert_value # populate cached property
1032 self.assertEqual(pickle.loads(pickle.dumps(expr)), expr)
1033
1034 def test_incorrect_field_in_F_expression(self):
1035 with self.assertRaisesMessage(
1036 FieldError, "Cannot resolve keyword 'nope' into field."
1037 ):
1038 list(Employee.objects.filter(firstname=F("nope")))
1039
1040 def test_incorrect_joined_field_in_F_expression(self):
1041 with self.assertRaisesMessage(
1042 FieldError, "Cannot resolve keyword 'nope' into field."
1043 ):
1044 list(Company.objects.filter(ceo__pk=F("point_of_contact__nope")))
1045
1046 def test_exists_in_filter(self):
1047 inner = Company.objects.filter(ceo=OuterRef("pk")).values("pk")
1048 qs1 = Employee.objects.filter(Exists(inner))
1049 qs2 = Employee.objects.annotate(found=Exists(inner)).filter(found=True)
1050 self.assertCountEqual(qs1, qs2)
1051 self.assertFalse(Employee.objects.exclude(Exists(inner)).exists())
1052 self.assertCountEqual(qs2, Employee.objects.exclude(~Exists(inner)))
1053
1054 def test_subquery_in_filter(self):
1055 inner = Company.objects.filter(ceo=OuterRef("pk")).values("based_in_eu")
1056 self.assertSequenceEqual(
1057 Employee.objects.filter(Subquery(inner)),
1058 [self.foobar_ltd.ceo],
1059 )
1060
1061 def test_subquery_group_by_outerref_in_filter(self):
1062 inner = (
1063 Company.objects.annotate(
1064 employee=OuterRef("pk"),
1065 )
1066 .values("employee")
1067 .annotate(
1068 min_num_chairs=Min("num_chairs"),
1069 )
1070 .values("ceo")
1071 )
1072 self.assertIs(Employee.objects.filter(pk__in=Subquery(inner)).exists(), True)
1073
1074 def test_case_in_filter_if_boolean_output_field(self):
1075 is_ceo = Company.objects.filter(ceo=OuterRef("pk"))
1076 is_poc = Company.objects.filter(point_of_contact=OuterRef("pk"))
1077 qs = Employee.objects.filter(
1078 Case(
1079 When(Exists(is_ceo), then=True),
1080 When(Exists(is_poc), then=True),
1081 default=False,
1082 output_field=BooleanField(),
1083 ),
1084 )
1085 self.assertCountEqual(qs, [self.example_inc.ceo, self.foobar_ltd.ceo, self.max])
1086
1087 def test_boolean_expression_combined(self):
1088 is_ceo = Company.objects.filter(ceo=OuterRef("pk"))
1089 is_poc = Company.objects.filter(point_of_contact=OuterRef("pk"))
1090 self.gmbh.point_of_contact = self.max
1091 self.gmbh.save()
1092 self.assertCountEqual(
1093 Employee.objects.filter(Exists(is_ceo) | Exists(is_poc)),
1094 [self.example_inc.ceo, self.foobar_ltd.ceo, self.max],
1095 )
1096 self.assertCountEqual(
1097 Employee.objects.filter(Exists(is_ceo) & Exists(is_poc)),
1098 [self.max],
1099 )
1100 self.assertCountEqual(
1101 Employee.objects.filter(Exists(is_ceo) & Q(salary__gte=30)),
1102 [self.max],
1103 )
1104 self.assertCountEqual(
1105 Employee.objects.filter(Exists(is_poc) | Q(salary__lt=15)),
1106 [self.example_inc.ceo, self.max],
1107 )
1108 self.assertCountEqual(
1109 Employee.objects.filter(Q(salary__gte=30) & Exists(is_ceo)),
1110 [self.max],
1111 )
1112 self.assertCountEqual(
1113 Employee.objects.filter(Q(salary__lt=15) | Exists(is_poc)),
1114 [self.example_inc.ceo, self.max],
1115 )
1116
1117 def test_boolean_expression_combined_with_empty_Q(self):
1118 is_poc = Company.objects.filter(point_of_contact=OuterRef("pk"))
1119 self.gmbh.point_of_contact = self.max
1120 self.gmbh.save()
1121 tests = [
1122 Exists(is_poc) & Q(),
1123 Q() & Exists(is_poc),
1124 Exists(is_poc) | Q(),
1125 Q() | Exists(is_poc),
1126 Q(Exists(is_poc)) & Q(),
1127 Q() & Q(Exists(is_poc)),
1128 Q(Exists(is_poc)) | Q(),
1129 Q() | Q(Exists(is_poc)),
1130 ]
1131 for conditions in tests:
1132 with self.subTest(conditions):
1133 self.assertCountEqual(Employee.objects.filter(conditions), [self.max])
1134
1135 def test_boolean_expression_in_Q(self):
1136 is_poc = Company.objects.filter(point_of_contact=OuterRef("pk"))
1137 self.gmbh.point_of_contact = self.max
1138 self.gmbh.save()
1139 self.assertCountEqual(Employee.objects.filter(Q(Exists(is_poc))), [self.max])
1140
1141
1142 class IterableLookupInnerExpressionsTests(TestCase):
1143 @classmethod
1144 def setUpTestData(cls):
1145 ceo = Employee.objects.create(firstname="Just", lastname="Doit", salary=30)
1146 # MySQL requires that the values calculated for expressions don't pass
1147 # outside of the field's range, so it's inconvenient to use the values
1148 # in the more general tests.
1149 cls.c5020 = Company.objects.create(
1150 name="5020 Ltd", num_employees=50, num_chairs=20, ceo=ceo
1151 )
1152 cls.c5040 = Company.objects.create(
1153 name="5040 Ltd", num_employees=50, num_chairs=40, ceo=ceo
1154 )
1155 cls.c5050 = Company.objects.create(
1156 name="5050 Ltd", num_employees=50, num_chairs=50, ceo=ceo
1157 )
1158 cls.c5060 = Company.objects.create(
1159 name="5060 Ltd", num_employees=50, num_chairs=60, ceo=ceo
1160 )
1161 cls.c99300 = Company.objects.create(
1162 name="99300 Ltd", num_employees=99, num_chairs=300, ceo=ceo
1163 )
1164
1165 def test_in_lookup_allows_F_expressions_and_expressions_for_integers(self):
1166 # __in lookups can use F() expressions for integers.
1167 queryset = Company.objects.filter(num_employees__in=([F("num_chairs") - 10]))
1168 self.assertSequenceEqual(queryset, [self.c5060])
1169 self.assertCountEqual(
1170 Company.objects.filter(
1171 num_employees__in=([F("num_chairs") - 10, F("num_chairs") + 10])
1172 ),
1173 [self.c5040, self.c5060],
1174 )
1175 self.assertCountEqual(
1176 Company.objects.filter(
1177 num_employees__in=(
1178 [F("num_chairs") - 10, F("num_chairs"), F("num_chairs") + 10]
1179 )
1180 ),
1181 [self.c5040, self.c5050, self.c5060],
1182 )
1183
1184 def test_expressions_range_lookups_join_choice(self):
1185 midpoint = datetime.time(13, 0)
1186 t1 = Time.objects.create(time=datetime.time(12, 0))
1187 t2 = Time.objects.create(time=datetime.time(14, 0))
1188 s1 = SimulationRun.objects.create(start=t1, end=t2, midpoint=midpoint)
1189 SimulationRun.objects.create(start=t1, end=None, midpoint=midpoint)
1190 SimulationRun.objects.create(start=None, end=t2, midpoint=midpoint)
1191 SimulationRun.objects.create(start=None, end=None, midpoint=midpoint)
1192
1193 queryset = SimulationRun.objects.filter(
1194 midpoint__range=[F("start__time"), F("end__time")]
1195 )
1196 self.assertSequenceEqual(queryset, [s1])
1197 for alias in queryset.query.alias_map.values():
1198 if isinstance(alias, Join):
1199 self.assertEqual(alias.join_type, constants.INNER)
1200
1201 queryset = SimulationRun.objects.exclude(
1202 midpoint__range=[F("start__time"), F("end__time")]
1203 )
1204 self.assertQuerySetEqual(queryset, [], ordered=False)
1205 for alias in queryset.query.alias_map.values():
1206 if isinstance(alias, Join):
1207 self.assertEqual(alias.join_type, constants.LOUTER)
1208
1209 def test_range_lookup_allows_F_expressions_and_expressions_for_integers(self):
1210 # Range lookups can use F() expressions for integers.
1211 Company.objects.filter(num_employees__exact=F("num_chairs"))
1212 self.assertCountEqual(
1213 Company.objects.filter(num_employees__range=(F("num_chairs"), 100)),
1214 [self.c5020, self.c5040, self.c5050],
1215 )
1216 self.assertCountEqual(
1217 Company.objects.filter(
1218 num_employees__range=(F("num_chairs") - 10, F("num_chairs") + 10)
1219 ),
1220 [self.c5040, self.c5050, self.c5060],
1221 )
1222 self.assertCountEqual(
1223 Company.objects.filter(num_employees__range=(F("num_chairs") - 10, 100)),
1224 [self.c5020, self.c5040, self.c5050, self.c5060],
1225 )
1226 self.assertCountEqual(
1227 Company.objects.filter(num_employees__range=(1, 100)),
1228 [self.c5020, self.c5040, self.c5050, self.c5060, self.c99300],
1229 )
1230
1231 def test_range_lookup_namedtuple(self):
1232 EmployeeRange = namedtuple("EmployeeRange", ["minimum", "maximum"])
1233 qs = Company.objects.filter(
1234 num_employees__range=EmployeeRange(minimum=51, maximum=100),
1235 )
1236 self.assertSequenceEqual(qs, [self.c99300])
1237
1238 @unittest.skipUnless(
1239 connection.vendor == "sqlite",
1240 "This defensive test only works on databases that don't validate parameter "
1241 "types",
1242 )
1243 def test_expressions_not_introduce_sql_injection_via_untrusted_string_inclusion(
1244 self,
1245 ):
1246 """
1247 This tests that SQL injection isn't possible using compilation of
1248 expressions in iterable filters, as their compilation happens before
1249 the main query compilation. It's limited to SQLite, as PostgreSQL,
1250 Oracle and other vendors have defense in depth against this by type
1251 checking. Testing against SQLite (the most permissive of the built-in
1252 databases) demonstrates that the problem doesn't exist while keeping
1253 the test simple.
1254 """
1255 queryset = Company.objects.filter(name__in=[F("num_chairs") + "1)) OR ((1==1"])
1256 self.assertQuerySetEqual(queryset, [], ordered=False)
1257
1258 def test_range_lookup_allows_F_expressions_and_expressions_for_dates(self):
1259 start = datetime.datetime(2016, 2, 3, 15, 0, 0)
1260 end = datetime.datetime(2016, 2, 5, 15, 0, 0)
1261 experiment_1 = Experiment.objects.create(
1262 name="Integrity testing",
1263 assigned=start.date(),
1264 start=start,
1265 end=end,
1266 completed=end.date(),
1267 estimated_time=end - start,
1268 )
1269 experiment_2 = Experiment.objects.create(
1270 name="Taste testing",
1271 assigned=start.date(),
1272 start=start,
1273 end=end,
1274 completed=end.date(),
1275 estimated_time=end - start,
1276 )
1277 r1 = Result.objects.create(
1278 experiment=experiment_1,
1279 result_time=datetime.datetime(2016, 2, 4, 15, 0, 0),
1280 )
1281 Result.objects.create(
1282 experiment=experiment_1,
1283 result_time=datetime.datetime(2016, 3, 10, 2, 0, 0),
1284 )
1285 Result.objects.create(
1286 experiment=experiment_2,
1287 result_time=datetime.datetime(2016, 1, 8, 5, 0, 0),
1288 )
1289 tests = [
1290 # Datetimes.
1291 ([F("experiment__start"), F("experiment__end")], "result_time__range"),
1292 # Dates.
1293 (
1294 [F("experiment__start__date"), F("experiment__end__date")],
1295 "result_time__date__range",
1296 ),
1297 ]
1298 for within_experiment_time, lookup in tests:
1299 with self.subTest(lookup=lookup):
1300 queryset = Result.objects.filter(**{lookup: within_experiment_time})
1301 self.assertSequenceEqual(queryset, [r1])
1302
1303 def test_relabeled_clone_rhs(self):
1304 Number.objects.bulk_create([Number(integer=1), Number(integer=2)])
1305 self.assertIs(
1306 Number.objects.filter(
1307 # Ensure iterable of expressions are properly re-labelled on
1308 # subquery pushdown. If the inner query __range right-hand-side
1309 # members are not relabelled they will point at the outer query
1310 # alias and this test will fail.
1311 Exists(
1312 Number.objects.exclude(pk=OuterRef("pk")).filter(
1313 integer__range=(F("integer"), F("integer"))
1314 )
1315 )
1316 ).exists(),
1317 True,
1318 )
1319
1320
1321 class FTests(SimpleTestCase):
1322 def test_deepcopy(self):
1323 f = F("foo")
1324 g = deepcopy(f)
1325 self.assertEqual(f.name, g.name)
1326
1327 def test_deconstruct(self):
1328 f = F("name")
1329 path, args, kwargs = f.deconstruct()
1330 self.assertEqual(path, "django.db.models.F")
1331 self.assertEqual(args, (f.name,))
1332 self.assertEqual(kwargs, {})
1333
1334 def test_equal(self):
1335 f = F("name")
1336 same_f = F("name")
1337 other_f = F("username")
1338 self.assertEqual(f, same_f)
1339 self.assertNotEqual(f, other_f)
1340
1341 def test_hash(self):
1342 d = {F("name"): "Bob"}
1343 self.assertIn(F("name"), d)
1344 self.assertEqual(d[F("name")], "Bob")
1345
1346 def test_not_equal_Value(self):
1347 f = F("name")
1348 value = Value("name")
1349 self.assertNotEqual(f, value)
1350 self.assertNotEqual(value, f)
1351
1352 def test_contains(self):
1353 msg = "argument of type 'F' is not iterable"
1354 with self.assertRaisesMessage(TypeError, msg):
1355 "" in F("name")
1356
1357 def test_replace_expressions_transform(self):
1358 replacements = {F("timestamp"): Value(None)}
1359 transform_ref = F("timestamp__date")
1360 self.assertIs(transform_ref.replace_expressions(replacements), transform_ref)
1361 invalid_transform_ref = F("timestamp__invalid")
1362 self.assertIs(
1363 invalid_transform_ref.replace_expressions(replacements),
1364 invalid_transform_ref,
1365 )
1366 replacements = {F("timestamp"): Value(datetime.datetime(2025, 3, 1, 14, 10))}
1367 self.assertEqual(
1368 F("timestamp__date").replace_expressions(replacements),
1369 TruncDate(Value(datetime.datetime(2025, 3, 1, 14, 10))),
1370 )
1371 self.assertEqual(
1372 F("timestamp__date__day").replace_expressions(replacements),
1373 ExtractDay(TruncDate(Value(datetime.datetime(2025, 3, 1, 14, 10)))),
1374 )
1375 invalid_nested_transform_ref = F("timestamp__date__invalid")
1376 self.assertIs(
1377 invalid_nested_transform_ref.replace_expressions(replacements),
1378 invalid_nested_transform_ref,
1379 )
1380 # `replacements` is not unnecessarily looked up a second time for
1381 # transform-less field references as it's the case the vast majority of
1382 # the time.
1383 mock_replacements = mock.Mock()
1384 mock_replacements.get.return_value = None
1385 field_ref = F("name")
1386 self.assertIs(field_ref.replace_expressions(mock_replacements), field_ref)
1387 mock_replacements.get.assert_called_once_with(field_ref)
1388
1389
1390 class ExpressionsTests(TestCase):
1391 def test_F_reuse(self):
1392 f = F("id")
1393 n = Number.objects.create(integer=-1)
1394 c = Company.objects.create(
1395 name="Example Inc.",
1396 num_employees=2300,
1397 num_chairs=5,
1398 ceo=Employee.objects.create(firstname="Joe", lastname="Smith"),
1399 )
1400 c_qs = Company.objects.filter(id=f)
1401 self.assertEqual(c_qs.get(), c)
1402 # Reuse the same F-object for another queryset
1403 n_qs = Number.objects.filter(id=f)
1404 self.assertEqual(n_qs.get(), n)
1405 # The original query still works correctly
1406 self.assertEqual(c_qs.get(), c)
1407
1408 def test_patterns_escape(self):
1409 r"""
1410 Special characters (e.g. %, _ and \) stored in database are
1411 properly escaped when using a pattern lookup with an expression
1412 refs #16731
1413 """
1414 Employee.objects.bulk_create(
1415 [
1416 Employee(firstname="Johnny", lastname="%John"),
1417 Employee(firstname="Jean-Claude", lastname="Claud_"),
1418 Employee(firstname="Jean-Claude", lastname="Claude%"),
1419 Employee(firstname="Johnny", lastname="Joh\\n"),
1420 Employee(firstname="Johnny", lastname="_ohn"),
1421 ]
1422 )
1423 claude = Employee.objects.create(firstname="Jean-Claude", lastname="Claude")
1424 john = Employee.objects.create(firstname="Johnny", lastname="John")
1425 john_sign = Employee.objects.create(firstname="%Joh\\nny", lastname="%Joh\\n")
1426
1427 self.assertCountEqual(
1428 Employee.objects.filter(firstname__contains=F("lastname")),
1429 [john_sign, john, claude],
1430 )
1431 self.assertCountEqual(
1432 Employee.objects.filter(firstname__startswith=F("lastname")),
1433 [john_sign, john],
1434 )
1435 self.assertSequenceEqual(
1436 Employee.objects.filter(firstname__endswith=F("lastname")),
1437 [claude],
1438 )
1439
1440 def test_insensitive_patterns_escape(self):
1441 r"""
1442 Special characters (e.g. %, _ and \) stored in database are
1443 properly escaped when using a case insensitive pattern lookup with an
1444 expression -- refs #16731
1445 """
1446 Employee.objects.bulk_create(
1447 [
1448 Employee(firstname="Johnny", lastname="%john"),
1449 Employee(firstname="Jean-Claude", lastname="claud_"),
1450 Employee(firstname="Jean-Claude", lastname="claude%"),
1451 Employee(firstname="Johnny", lastname="joh\\n"),
1452 Employee(firstname="Johnny", lastname="_ohn"),
1453 ]
1454 )
1455 claude = Employee.objects.create(firstname="Jean-Claude", lastname="claude")
1456 john = Employee.objects.create(firstname="Johnny", lastname="john")
1457 john_sign = Employee.objects.create(firstname="%Joh\\nny", lastname="%joh\\n")
1458
1459 self.assertCountEqual(
1460 Employee.objects.filter(firstname__icontains=F("lastname")),
1461 [john_sign, john, claude],
1462 )
1463 self.assertCountEqual(
1464 Employee.objects.filter(firstname__istartswith=F("lastname")),
1465 [john_sign, john],
1466 )
1467 self.assertSequenceEqual(
1468 Employee.objects.filter(firstname__iendswith=F("lastname")),
1469 [claude],
1470 )
1471
1472
1473 @isolate_apps("expressions")
1474 class SimpleExpressionTests(SimpleTestCase):
1475 def test_equal(self):
1476 self.assertEqual(Expression(), Expression())
1477 self.assertEqual(
1478 Expression(IntegerField()), Expression(output_field=IntegerField())
1479 )
1480 self.assertEqual(Expression(IntegerField()), mock.ANY)
1481 self.assertNotEqual(Expression(IntegerField()), Expression(CharField()))
1482
1483 class TestModel(Model):
1484 field = IntegerField()
1485 other_field = IntegerField()
1486
1487 self.assertNotEqual(
1488 Expression(TestModel._meta.get_field("field")),
1489 Expression(TestModel._meta.get_field("other_field")),
1490 )
1491
1492 class InitCaptureExpression(Expression):
1493 def __init__(self, *args, **kwargs):
1494 super().__init__(*args, **kwargs)
1495
1496 # The identity of expressions that obscure their __init__() signature
1497 # with *args and **kwargs cannot be determined when bound with
1498 # different combinations or *args and **kwargs.
1499 self.assertNotEqual(
1500 InitCaptureExpression(IntegerField()),
1501 InitCaptureExpression(output_field=IntegerField()),
1502 )
1503
1504 # However, they should be considered equal when their bindings are
1505 # equal.
1506 self.assertEqual(
1507 InitCaptureExpression(IntegerField()),
1508 InitCaptureExpression(IntegerField()),
1509 )
1510 self.assertEqual(
1511 InitCaptureExpression(output_field=IntegerField()),
1512 InitCaptureExpression(output_field=IntegerField()),
1513 )
1514
1515 def test_hash(self):
1516 self.assertEqual(hash(Expression()), hash(Expression()))
1517 self.assertEqual(
1518 hash(Expression(IntegerField())),
1519 hash(Expression(output_field=IntegerField())),
1520 )
1521 self.assertNotEqual(
1522 hash(Expression(IntegerField())),
1523 hash(Expression(CharField())),
1524 )
1525
1526 class TestModel(Model):
1527 field = IntegerField()
1528 other_field = IntegerField()
1529
1530 self.assertNotEqual(
1531 hash(Expression(TestModel._meta.get_field("field"))),
1532 hash(Expression(TestModel._meta.get_field("other_field"))),
1533 )
1534
1535 def test_get_expression_for_validation_only_one_source_expression(self):
1536 expression = Expression()
1537 expression.constraint_validation_compatible = False
1538 msg = (
1539 "Expressions with constraint_validation_compatible set to False must have "
1540 "only one source expression."
1541 )
1542 with self.assertRaisesMessage(ValueError, msg):
1543 expression.get_expression_for_validation()
1544
1545 def test_replace_expressions_falsey(self):
1546 class AssignableExpression(Expression):
1547 def __init__(self, *source_expressions):
1548 super().__init__()
1549 self.set_source_expressions(list(source_expressions))
1550
1551 def get_source_expressions(self):
1552 return self.source_expressions
1553
1554 def set_source_expressions(self, exprs):
1555 self.source_expressions = exprs
1556
1557 expression = AssignableExpression()
1558 falsey = Q()
1559 expression.set_source_expressions([falsey])
1560 replaced = expression.replace_expressions({"replacement": Expression()})
1561 self.assertEqual(replaced.get_source_expressions(), [falsey])
1562
1563 @unittest.skipUnless(PY314, "Deferred annotations are Python 3.14+ only")
1564 def test_expression_signature_uses_deferred_annotations(self):
1565 class AnnotatedExpression(Expression):
1566 def __init__(self, *args, my_kw: AnnotatedKwarg, **kwargs):
1567 super().__init__(*args, **kwargs)
1568
1569 self.assertEqual(AnnotatedExpression(my_kw=""), AnnotatedExpression(my_kw=""))
1570
1571
1572 class ExpressionsNumericTests(TestCase):
1573 @classmethod
1574 def setUpTestData(cls):
1575 Number(integer=-1).save()
1576 Number(integer=42).save()
1577 Number(integer=1337).save()
1578 Number.objects.update(float=F("integer"))
1579
1580 def test_fill_with_value_from_same_object(self):
1581 """
1582 We can fill a value in all objects with an other value of the
1583 same object.
1584 """
1585 self.assertQuerySetEqual(
1586 Number.objects.all(),
1587 [(-1, -1), (42, 42), (1337, 1337)],
1588 lambda n: (n.integer, round(n.float)),
1589 ordered=False,
1590 )
1591
1592 def test_increment_value(self):
1593 """
1594 We can increment a value of all objects in a query set.
1595 """
1596 self.assertEqual(
1597 Number.objects.filter(integer__gt=0).update(integer=F("integer") + 1), 2
1598 )
1599 self.assertQuerySetEqual(
1600 Number.objects.all(),
1601 [(-1, -1), (43, 42), (1338, 1337)],
1602 lambda n: (n.integer, round(n.float)),
1603 ordered=False,
1604 )
1605
1606 def test_filter_not_equals_other_field(self):
1607 """
1608 We can filter for objects, where a value is not equals the value
1609 of an other field.
1610 """
1611 self.assertEqual(
1612 Number.objects.filter(integer__gt=0).update(integer=F("integer") + 1), 2
1613 )
1614 self.assertQuerySetEqual(
1615 Number.objects.exclude(float=F("integer")),
1616 [(43, 42), (1338, 1337)],
1617 lambda n: (n.integer, round(n.float)),
1618 ordered=False,
1619 )
1620
1621 def test_filter_decimal_expression(self):
1622 obj = Number.objects.create(integer=0, float=1, decimal_value=Decimal("1"))
1623 qs = Number.objects.annotate(
1624 x=ExpressionWrapper(Value(1), output_field=DecimalField()),
1625 ).filter(Q(x=1, integer=0) & Q(x=Decimal("1")))
1626 self.assertSequenceEqual(qs, [obj])
1627
1628 def test_complex_expressions(self):
1629 """
1630 Complex expressions of different connection types are possible.
1631 """
1632 n = Number.objects.create(integer=10, float=123.45)
1633 self.assertEqual(
1634 Number.objects.filter(pk=n.pk).update(float=F("integer") + F("float") * 2),
1635 1,
1636 )
1637
1638 self.assertEqual(Number.objects.get(pk=n.pk).integer, 10)
1639 self.assertEqual(
1640 Number.objects.get(pk=n.pk).float, Approximate(256.900, places=3)
1641 )
1642
1643 def test_decimal_expression(self):
1644 n = Number.objects.create(integer=1, decimal_value=Decimal("0.5"))
1645 n.decimal_value = F("decimal_value") - Decimal("0.4")
1646 n.save()
1647 n.refresh_from_db()
1648 self.assertEqual(n.decimal_value, Decimal("0.1"))
1649
1650
1651 class ExpressionOperatorTests(TestCase):
1652 @classmethod
1653 def setUpTestData(cls):
1654 cls.n = Number.objects.create(integer=42, float=15.5)
1655 cls.n1 = Number.objects.create(integer=-42, float=-15.5)
1656
1657 def test_lefthand_addition(self):
1658 # LH Addition of floats and integers
1659 Number.objects.filter(pk=self.n.pk).update(
1660 integer=F("integer") + 15, float=F("float") + 42.7
1661 )
1662
1663 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 57)
1664 self.assertEqual(
1665 Number.objects.get(pk=self.n.pk).float, Approximate(58.200, places=3)
1666 )
1667
1668 def test_lefthand_subtraction(self):
1669 # LH Subtraction of floats and integers
1670 Number.objects.filter(pk=self.n.pk).update(
1671 integer=F("integer") - 15, float=F("float") - 42.7
1672 )
1673
1674 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 27)
1675 self.assertEqual(
1676 Number.objects.get(pk=self.n.pk).float, Approximate(-27.200, places=3)
1677 )
1678
1679 def test_lefthand_multiplication(self):
1680 # Multiplication of floats and integers
1681 Number.objects.filter(pk=self.n.pk).update(
1682 integer=F("integer") * 15, float=F("float") * 42.7
1683 )
1684
1685 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 630)
1686 self.assertEqual(
1687 Number.objects.get(pk=self.n.pk).float, Approximate(661.850, places=3)
1688 )
1689
1690 def test_lefthand_division(self):
1691 # LH Division of floats and integers
1692 Number.objects.filter(pk=self.n.pk).update(
1693 integer=F("integer") / 2, float=F("float") / 42.7
1694 )
1695
1696 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 21)
1697 self.assertEqual(
1698 Number.objects.get(pk=self.n.pk).float, Approximate(0.363, places=3)
1699 )
1700
1701 def test_lefthand_modulo(self):
1702 # LH Modulo arithmetic on integers
1703 Number.objects.filter(pk=self.n.pk).update(integer=F("integer") % 20)
1704 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 2)
1705
1706 def test_lefthand_modulo_null(self):
1707 # LH Modulo arithmetic on integers.
1708 Employee.objects.create(firstname="John", lastname="Doe", salary=None)
1709 qs = Employee.objects.annotate(modsalary=F("salary") % 20)
1710 self.assertIsNone(qs.get().salary)
1711
1712 def test_lefthand_bitwise_and(self):
1713 # LH Bitwise ands on integers
1714 Number.objects.filter(pk=self.n.pk).update(integer=F("integer").bitand(56))
1715 Number.objects.filter(pk=self.n1.pk).update(integer=F("integer").bitand(-56))
1716
1717 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 40)
1718 self.assertEqual(Number.objects.get(pk=self.n1.pk).integer, -64)
1719
1720 def test_lefthand_bitwise_left_shift_operator(self):
1721 Number.objects.update(integer=F("integer").bitleftshift(2))
1722 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 168)
1723 self.assertEqual(Number.objects.get(pk=self.n1.pk).integer, -168)
1724
1725 def test_lefthand_bitwise_right_shift_operator(self):
1726 Number.objects.update(integer=F("integer").bitrightshift(2))
1727 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 10)
1728 self.assertEqual(Number.objects.get(pk=self.n1.pk).integer, -11)
1729
1730 def test_lefthand_bitwise_or(self):
1731 # LH Bitwise or on integers
1732 Number.objects.update(integer=F("integer").bitor(48))
1733
1734 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 58)
1735 self.assertEqual(Number.objects.get(pk=self.n1.pk).integer, -10)
1736
1737 def test_lefthand_transformed_field_bitwise_or(self):
1738 Employee.objects.create(firstname="Max", lastname="Mustermann")
1739 with register_lookup(CharField, Length):
1740 qs = Employee.objects.annotate(bitor=F("lastname__length").bitor(48))
1741 self.assertEqual(qs.get().bitor, 58)
1742
1743 def test_lefthand_power(self):
1744 # LH Power arithmetic operation on floats and integers
1745 Number.objects.filter(pk=self.n.pk).update(
1746 integer=F("integer") ** 2, float=F("float") ** 1.5
1747 )
1748 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 1764)
1749 self.assertEqual(
1750 Number.objects.get(pk=self.n.pk).float, Approximate(61.02, places=2)
1751 )
1752
1753 def test_lefthand_bitwise_xor(self):
1754 Number.objects.update(integer=F("integer").bitxor(48))
1755 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 26)
1756 self.assertEqual(Number.objects.get(pk=self.n1.pk).integer, -26)
1757
1758 def test_lefthand_bitwise_xor_null(self):
1759 employee = Employee.objects.create(firstname="John", lastname="Doe")
1760 Employee.objects.update(salary=F("salary").bitxor(48))
1761 employee.refresh_from_db()
1762 self.assertIsNone(employee.salary)
1763
1764 def test_lefthand_bitwise_xor_right_null(self):
1765 employee = Employee.objects.create(firstname="John", lastname="Doe", salary=48)
1766 Employee.objects.update(salary=F("salary").bitxor(None))
1767 employee.refresh_from_db()
1768 self.assertIsNone(employee.salary)
1769
1770 @unittest.skipUnless(
1771 connection.vendor == "oracle", "Oracle doesn't support bitwise XOR."
1772 )
1773 def test_lefthand_bitwise_xor_not_supported(self):
1774 msg = "Bitwise XOR is not supported in Oracle."
1775 with self.assertRaisesMessage(NotSupportedError, msg):
1776 Number.objects.update(integer=F("integer").bitxor(48))
1777
1778 def test_right_hand_addition(self):
1779 # Right hand operators
1780 Number.objects.filter(pk=self.n.pk).update(
1781 integer=15 + F("integer"), float=42.7 + F("float")
1782 )
1783
1784 # RH Addition of floats and integers
1785 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 57)
1786 self.assertEqual(
1787 Number.objects.get(pk=self.n.pk).float, Approximate(58.200, places=3)
1788 )
1789
1790 def test_right_hand_subtraction(self):
1791 Number.objects.filter(pk=self.n.pk).update(
1792 integer=15 - F("integer"), float=42.7 - F("float")
1793 )
1794
1795 # RH Subtraction of floats and integers
1796 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, -27)
1797 self.assertEqual(
1798 Number.objects.get(pk=self.n.pk).float, Approximate(27.200, places=3)
1799 )
1800
1801 def test_right_hand_multiplication(self):
1802 # RH Multiplication of floats and integers
1803 Number.objects.filter(pk=self.n.pk).update(
1804 integer=15 * F("integer"), float=42.7 * F("float")
1805 )
1806
1807 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 630)
1808 self.assertEqual(
1809 Number.objects.get(pk=self.n.pk).float, Approximate(661.850, places=3)
1810 )
1811
1812 def test_right_hand_division(self):
1813 # RH Division of floats and integers
1814 Number.objects.filter(pk=self.n.pk).update(
1815 integer=640 / F("integer"), float=42.7 / F("float")
1816 )
1817
1818 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 15)
1819 self.assertEqual(
1820 Number.objects.get(pk=self.n.pk).float, Approximate(2.755, places=3)
1821 )
1822
1823 def test_right_hand_modulo(self):
1824 # RH Modulo arithmetic on integers
1825 Number.objects.filter(pk=self.n.pk).update(integer=69 % F("integer"))
1826
1827 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 27)
1828
1829 def test_righthand_power(self):
1830 # RH Power arithmetic operation on floats and integers
1831 Number.objects.filter(pk=self.n.pk).update(
1832 integer=2 ** F("integer"), float=1.5 ** F("float")
1833 )
1834 self.assertEqual(Number.objects.get(pk=self.n.pk).integer, 4398046511104)
1835 self.assertEqual(
1836 Number.objects.get(pk=self.n.pk).float, Approximate(536.308, places=3)
1837 )
1838
1839
1840 class FTimeDeltaTests(TestCase):
1841 @classmethod
1842 def setUpTestData(cls):
1843 cls.sday = sday = datetime.date(2010, 6, 25)
1844 cls.stime = stime = datetime.datetime(2010, 6, 25, 12, 15, 30, 747000)
1845 midnight = datetime.time(0)
1846
1847 delta0 = datetime.timedelta(0)
1848 delta1 = datetime.timedelta(microseconds=253000)
1849 delta2 = datetime.timedelta(seconds=44)
1850 delta3 = datetime.timedelta(hours=21, minutes=8)
1851 delta4 = datetime.timedelta(days=10)
1852 delta5 = datetime.timedelta(days=90)
1853
1854 # Test data is set so that deltas and delays will be
1855 # strictly increasing.
1856 cls.deltas = []
1857 cls.delays = []
1858 cls.days_long = []
1859
1860 # e0: started same day as assigned, zero duration
1861 end = stime + delta0
1862 cls.e0 = Experiment.objects.create(
1863 name="e0",
1864 assigned=sday,
1865 start=stime,
1866 end=end,
1867 completed=end.date(),
1868 estimated_time=delta0,
1869 )
1870 cls.deltas.append(delta0)
1871 cls.delays.append(
1872 cls.e0.start - datetime.datetime.combine(cls.e0.assigned, midnight)
1873 )
1874 cls.days_long.append(cls.e0.completed - cls.e0.assigned)
1875
1876 # e1: started one day after assigned, tiny duration, data
1877 # set so that end time has no fractional seconds, which
1878 # tests an edge case on sqlite.
1879 delay = datetime.timedelta(1)
1880 end = stime + delay + delta1
1881 e1 = Experiment.objects.create(
1882 name="e1",
1883 assigned=sday,
1884 start=stime + delay,
1885 end=end,
1886 completed=end.date(),
1887 estimated_time=delta1,
1888 )
1889 cls.deltas.append(delta1)
1890 cls.delays.append(e1.start - datetime.datetime.combine(e1.assigned, midnight))
1891 cls.days_long.append(e1.completed - e1.assigned)
1892
1893 # e2: started three days after assigned, small duration
1894 end = stime + delta2
1895 e2 = Experiment.objects.create(
1896 name="e2",
1897 assigned=sday - datetime.timedelta(3),
1898 start=stime,
1899 end=end,
1900 completed=end.date(),
1901 estimated_time=datetime.timedelta(hours=1),
1902 )
1903 cls.deltas.append(delta2)
1904 cls.delays.append(e2.start - datetime.datetime.combine(e2.assigned, midnight))
1905 cls.days_long.append(e2.completed - e2.assigned)
1906
1907 # e3: started four days after assigned, medium duration
1908 delay = datetime.timedelta(4)
1909 end = stime + delay + delta3
1910 e3 = Experiment.objects.create(
1911 name="e3",
1912 assigned=sday,
1913 start=stime + delay,
1914 end=end,
1915 completed=end.date(),
1916 estimated_time=delta3,
1917 )
1918 cls.deltas.append(delta3)
1919 cls.delays.append(e3.start - datetime.datetime.combine(e3.assigned, midnight))
1920 cls.days_long.append(e3.completed - e3.assigned)
1921
1922 # e4: started 10 days after assignment, long duration
1923 end = stime + delta4
1924 e4 = Experiment.objects.create(
1925 name="e4",
1926 assigned=sday - datetime.timedelta(10),
1927 start=stime,
1928 end=end,
1929 completed=end.date(),
1930 estimated_time=delta4 - datetime.timedelta(1),
1931 )
1932 cls.deltas.append(delta4)
1933 cls.delays.append(e4.start - datetime.datetime.combine(e4.assigned, midnight))
1934 cls.days_long.append(e4.completed - e4.assigned)
1935
1936 # e5: started a month after assignment, very long duration
1937 delay = datetime.timedelta(30)
1938 end = stime + delay + delta5
1939 e5 = Experiment.objects.create(
1940 name="e5",
1941 assigned=sday,
1942 start=stime + delay,
1943 end=end,
1944 completed=end.date(),
1945 estimated_time=delta5,
1946 )
1947 cls.deltas.append(delta5)
1948 cls.delays.append(e5.start - datetime.datetime.combine(e5.assigned, midnight))
1949 cls.days_long.append(e5.completed - e5.assigned)
1950
1951 cls.expnames = [e.name for e in Experiment.objects.all()]
1952
1953 def test_multiple_query_compilation(self):
1954 # Ticket #21643
1955 queryset = Experiment.objects.filter(
1956 end__lt=F("start") + datetime.timedelta(hours=1)
1957 )
1958 q1 = str(queryset.query)
1959 q2 = str(queryset.query)
1960 self.assertEqual(q1, q2)
1961
1962 def test_query_clone(self):
1963 # Ticket #21643 - Crash when compiling query more than once
1964 qs = Experiment.objects.filter(end__lt=F("start") + datetime.timedelta(hours=1))
1965 qs2 = qs.all()
1966 list(qs)
1967 list(qs2)
1968 # Intentionally no assert
1969
1970 def test_delta_add(self):
1971 for i, delta in enumerate(self.deltas):
1972 test_set = [
1973 e.name for e in Experiment.objects.filter(end__lt=F("start") + delta)
1974 ]
1975 self.assertEqual(test_set, self.expnames[:i])
1976
1977 test_set = [
1978 e.name for e in Experiment.objects.filter(end__lt=delta + F("start"))
1979 ]
1980 self.assertEqual(test_set, self.expnames[:i])
1981
1982 test_set = [
1983 e.name for e in Experiment.objects.filter(end__lte=F("start") + delta)
1984 ]
1985 self.assertEqual(test_set, self.expnames[: i + 1])
1986
1987 def test_delta_subtract(self):
1988 for i, delta in enumerate(self.deltas):
1989 test_set = [
1990 e.name for e in Experiment.objects.filter(start__gt=F("end") - delta)
1991 ]
1992 self.assertEqual(test_set, self.expnames[:i])
1993
1994 test_set = [
1995 e.name for e in Experiment.objects.filter(start__gte=F("end") - delta)
1996 ]
1997 self.assertEqual(test_set, self.expnames[: i + 1])
1998
1999 def test_exclude(self):
2000 for i, delta in enumerate(self.deltas):
2001 test_set = [
2002 e.name for e in Experiment.objects.exclude(end__lt=F("start") + delta)
2003 ]
2004 self.assertEqual(test_set, self.expnames[i:])
2005
2006 test_set = [
2007 e.name for e in Experiment.objects.exclude(end__lte=F("start") + delta)
2008 ]
2009 self.assertEqual(test_set, self.expnames[i + 1 :])
2010
2011 def test_date_comparison(self):
2012 for i, days in enumerate(self.days_long):
2013 test_set = [
2014 e.name
2015 for e in Experiment.objects.filter(completed__lt=F("assigned") + days)
2016 ]
2017 self.assertEqual(test_set, self.expnames[:i])
2018
2019 test_set = [
2020 e.name
2021 for e in Experiment.objects.filter(completed__lte=F("assigned") + days)
2022 ]
2023 self.assertEqual(test_set, self.expnames[: i + 1])
2024
2025 def test_datetime_and_durationfield_addition_with_filter(self):
2026 test_set = Experiment.objects.filter(end=F("start") + F("estimated_time"))
2027 self.assertGreater(test_set.count(), 0)
2028 self.assertEqual(
2029 [e.name for e in test_set],
2030 [
2031 e.name
2032 for e in Experiment.objects.all()
2033 if e.end == e.start + e.estimated_time
2034 ],
2035 )
2036
2037 def test_datetime_and_duration_field_addition_with_annotate_and_no_output_field(
2038 self,
2039 ):
2040 test_set = Experiment.objects.annotate(
2041 estimated_end=F("start") + F("estimated_time")
2042 )
2043 self.assertEqual(
2044 [e.estimated_end for e in test_set],
2045 [e.start + e.estimated_time for e in test_set],
2046 )
2047
2048 @skipUnlessDBFeature("supports_temporal_subtraction")
2049 def test_datetime_subtraction_with_annotate_and_no_output_field(self):
2050 test_set = Experiment.objects.annotate(
2051 calculated_duration=F("end") - F("start")
2052 )
2053 self.assertEqual(
2054 [e.calculated_duration for e in test_set],
2055 [e.end - e.start for e in test_set],
2056 )
2057
2058 def test_mixed_comparisons1(self):
2059 for i, delay in enumerate(self.delays):
2060 test_set = [
2061 e.name
2062 for e in Experiment.objects.filter(assigned__gt=F("start") - delay)
2063 ]
2064 self.assertEqual(test_set, self.expnames[:i])
2065
2066 test_set = [
2067 e.name
2068 for e in Experiment.objects.filter(assigned__gte=F("start") - delay)
2069 ]
2070 self.assertEqual(test_set, self.expnames[: i + 1])
2071
2072 def test_mixed_comparisons2(self):
2073 for i, delay in enumerate(self.delays):
2074 delay = datetime.timedelta(delay.days)
2075 test_set = [
2076 e.name
2077 for e in Experiment.objects.filter(start__lt=F("assigned") + delay)
2078 ]
2079 self.assertEqual(test_set, self.expnames[:i])
2080
2081 test_set = [
2082 e.name
2083 for e in Experiment.objects.filter(
2084 start__lte=F("assigned") + delay + datetime.timedelta(1)
2085 )
2086 ]
2087 self.assertEqual(test_set, self.expnames[: i + 1])
2088
2089 def test_delta_update(self):
2090 for delta in self.deltas:
2091 exps = Experiment.objects.all()
2092 expected_durations = [e.duration() for e in exps]
2093 expected_starts = [e.start + delta for e in exps]
2094 expected_ends = [e.end + delta for e in exps]
2095
2096 Experiment.objects.update(start=F("start") + delta, end=F("end") + delta)
2097 exps = Experiment.objects.all()
2098 new_starts = [e.start for e in exps]
2099 new_ends = [e.end for e in exps]
2100 new_durations = [e.duration() for e in exps]
2101 self.assertEqual(expected_starts, new_starts)
2102 self.assertEqual(expected_ends, new_ends)
2103 self.assertEqual(expected_durations, new_durations)
2104
2105 def test_invalid_operator(self):
2106 with self.assertRaises(DatabaseError):
2107 list(Experiment.objects.filter(start=F("start") * datetime.timedelta(0)))
2108
2109 def test_durationfield_add(self):
2110 zeros = [
2111 e.name
2112 for e in Experiment.objects.filter(start=F("start") + F("estimated_time"))
2113 ]
2114 self.assertEqual(zeros, ["e0"])
2115
2116 end_less = [
2117 e.name
2118 for e in Experiment.objects.filter(end__lt=F("start") + F("estimated_time"))
2119 ]
2120 self.assertEqual(end_less, ["e2"])
2121
2122 delta_math = [
2123 e.name
2124 for e in Experiment.objects.filter(
2125 end__gte=F("start") + F("estimated_time") + datetime.timedelta(hours=1)
2126 )
2127 ]
2128 self.assertEqual(delta_math, ["e4"])
2129
2130 queryset = Experiment.objects.annotate(
2131 shifted=ExpressionWrapper(
2132 F("start") + Value(None, output_field=DurationField()),
2133 output_field=DateTimeField(),
2134 )
2135 )
2136 self.assertIsNone(queryset.first().shifted)
2137
2138 def test_durationfield_multiply_divide(self):
2139 Experiment.objects.update(scalar=2)
2140 tests = [
2141 (Decimal("2"), 2),
2142 (F("scalar"), 2),
2143 (2, 2),
2144 (3.2, 3.2),
2145 ]
2146 for expr, scalar in tests:
2147 with self.subTest(expr=expr):
2148 qs = Experiment.objects.annotate(
2149 multiplied=ExpressionWrapper(
2150 expr * F("estimated_time"),
2151 output_field=DurationField(),
2152 ),
2153 divided=ExpressionWrapper(
2154 F("estimated_time") / expr,
2155 output_field=DurationField(),
2156 ),
2157 )
2158 for experiment in qs:
2159 self.assertEqual(
2160 experiment.multiplied,
2161 experiment.estimated_time * scalar,
2162 )
2163 self.assertEqual(
2164 experiment.divided,
2165 experiment.estimated_time / scalar,
2166 )
2167
2168 def test_duration_expressions(self):
2169 for delta in self.deltas:
2170 qs = Experiment.objects.annotate(duration=F("estimated_time") + delta)
2171 for obj in qs:
2172 self.assertEqual(obj.duration, obj.estimated_time + delta)
2173
2174 @skipUnlessDBFeature("supports_temporal_subtraction")
2175 def test_date_subtraction(self):
2176 queryset = Experiment.objects.annotate(
2177 completion_duration=F("completed") - F("assigned"),
2178 )
2179
2180 at_least_5_days = {
2181 e.name
2182 for e in queryset.filter(
2183 completion_duration__gte=datetime.timedelta(days=5)
2184 )
2185 }
2186 self.assertEqual(at_least_5_days, {"e3", "e4", "e5"})
2187
2188 at_least_120_days = {
2189 e.name
2190 for e in queryset.filter(
2191 completion_duration__gte=datetime.timedelta(days=120)
2192 )
2193 }
2194 self.assertEqual(at_least_120_days, {"e5"})
2195
2196 less_than_5_days = {
2197 e.name
2198 for e in queryset.filter(completion_duration__lt=datetime.timedelta(days=5))
2199 }
2200 self.assertEqual(less_than_5_days, {"e0", "e1", "e2"})
2201
2202 queryset = Experiment.objects.annotate(
2203 difference=F("completed") - Value(None, output_field=DateField()),
2204 )
2205 self.assertIsNone(queryset.first().difference)
2206
2207 queryset = Experiment.objects.annotate(
2208 shifted=ExpressionWrapper(
2209 F("completed") - Value(None, output_field=DurationField()),
2210 output_field=DateField(),
2211 )
2212 )
2213 self.assertIsNone(queryset.first().shifted)
2214
2215 @skipUnlessDBFeature("supports_temporal_subtraction")
2216 def test_date_subquery_subtraction(self):
2217 subquery = Experiment.objects.filter(pk=OuterRef("pk")).values("completed")
2218 queryset = Experiment.objects.annotate(
2219 difference=subquery - F("completed"),
2220 ).filter(difference=datetime.timedelta())
2221 self.assertTrue(queryset.exists())
2222
2223 @skipUnlessDBFeature("supports_temporal_subtraction")
2224 def test_date_case_subtraction(self):
2225 queryset = Experiment.objects.annotate(
2226 date_case=Case(
2227 When(Q(name="e0"), then=F("completed")),
2228 output_field=DateField(),
2229 ),
2230 completed_value=Value(
2231 self.e0.completed,
2232 output_field=DateField(),
2233 ),
2234 difference=F("date_case") - F("completed_value"),
2235 ).filter(difference=datetime.timedelta())
2236 self.assertEqual(queryset.get(), self.e0)
2237
2238 @skipUnlessDBFeature("supports_temporal_subtraction")
2239 def test_time_subtraction(self):
2240 Time.objects.create(time=datetime.time(12, 30, 15, 2345))
2241 queryset = Time.objects.annotate(
2242 difference=F("time") - Value(datetime.time(11, 15, 0)),
2243 )
2244 self.assertEqual(
2245 queryset.get().difference,
2246 datetime.timedelta(hours=1, minutes=15, seconds=15, microseconds=2345),
2247 )
2248
2249 queryset = Time.objects.annotate(
2250 difference=F("time") - Value(None, output_field=TimeField()),
2251 )
2252 self.assertIsNone(queryset.first().difference)
2253
2254 queryset = Time.objects.annotate(
2255 shifted=ExpressionWrapper(
2256 F("time") - Value(None, output_field=DurationField()),
2257 output_field=TimeField(),
2258 )
2259 )
2260 self.assertIsNone(queryset.first().shifted)
2261
2262 @skipUnlessDBFeature("supports_temporal_subtraction")
2263 def test_time_subquery_subtraction(self):
2264 Time.objects.create(time=datetime.time(12, 30, 15, 2345))
2265 subquery = Time.objects.filter(pk=OuterRef("pk")).values("time")
2266 queryset = Time.objects.annotate(
2267 difference=subquery - F("time"),
2268 ).filter(difference=datetime.timedelta())
2269 self.assertTrue(queryset.exists())
2270
2271 @skipUnlessDBFeature("supports_temporal_subtraction")
2272 def test_datetime_subtraction(self):
2273 under_estimate = [
2274 e.name
2275 for e in Experiment.objects.filter(estimated_time__gt=F("end") - F("start"))
2276 ]
2277 self.assertEqual(under_estimate, ["e2"])
2278
2279 over_estimate = [
2280 e.name
2281 for e in Experiment.objects.filter(estimated_time__lt=F("end") - F("start"))
2282 ]
2283 self.assertEqual(over_estimate, ["e4"])
2284
2285 queryset = Experiment.objects.annotate(
2286 difference=F("start") - Value(None, output_field=DateTimeField()),
2287 )
2288 self.assertIsNone(queryset.first().difference)
2289
2290 queryset = Experiment.objects.annotate(
2291 shifted=ExpressionWrapper(
2292 F("start") - Value(None, output_field=DurationField()),
2293 output_field=DateTimeField(),
2294 )
2295 )
2296 self.assertIsNone(queryset.first().shifted)
2297
2298 @skipUnlessDBFeature("supports_temporal_subtraction")
2299 def test_datetime_subquery_subtraction(self):
2300 subquery = Experiment.objects.filter(pk=OuterRef("pk")).values("start")
2301 queryset = Experiment.objects.annotate(
2302 difference=subquery - F("start"),
2303 ).filter(difference=datetime.timedelta())
2304 self.assertTrue(queryset.exists())
2305
2306 @skipUnlessDBFeature("supports_temporal_subtraction")
2307 def test_datetime_subtraction_microseconds(self):
2308 delta = datetime.timedelta(microseconds=8999999999999999)
2309 Experiment.objects.update(end=F("start") + delta)
2310 qs = Experiment.objects.annotate(delta=F("end") - F("start"))
2311 for e in qs:
2312 self.assertEqual(e.delta, delta)
2313
2314 def test_duration_with_datetime(self):
2315 # Exclude e1 which has very high precision so we can test this on all
2316 # backends regardless of whether or not it supports
2317 # microsecond_precision.
2318 over_estimate = (
2319 Experiment.objects.exclude(name="e1")
2320 .filter(
2321 completed__gt=self.stime + F("estimated_time"),
2322 )
2323 .order_by("name")
2324 )
2325 self.assertQuerySetEqual(over_estimate, ["e3", "e4", "e5"], lambda e: e.name)
2326
2327 def test_duration_with_datetime_microseconds(self):
2328 delta = datetime.timedelta(microseconds=8999999999999999)
2329 qs = Experiment.objects.annotate(
2330 dt=ExpressionWrapper(
2331 F("start") + delta,
2332 output_field=DateTimeField(),
2333 )
2334 )
2335 for e in qs:
2336 self.assertEqual(e.dt, e.start + delta)
2337
2338 def test_date_minus_duration(self):
2339 more_than_4_days = Experiment.objects.filter(
2340 assigned__lt=F("completed") - Value(datetime.timedelta(days=4))
2341 )
2342 self.assertQuerySetEqual(more_than_4_days, ["e3", "e4", "e5"], lambda e: e.name)
2343
2344 def test_negative_timedelta_update(self):
2345 # subtract 30 seconds, 30 minutes, 2 hours and 2 days
2346 experiments = (
2347 Experiment.objects.filter(name="e0")
2348 .annotate(
2349 start_sub_seconds=F("start") + datetime.timedelta(seconds=-30),
2350 )
2351 .annotate(
2352 start_sub_minutes=F("start_sub_seconds")
2353 + datetime.timedelta(minutes=-30),
2354 )
2355 .annotate(
2356 start_sub_hours=F("start_sub_minutes") + datetime.timedelta(hours=-2),
2357 )
2358 .annotate(
2359 new_start=F("start_sub_hours") + datetime.timedelta(days=-2),
2360 )
2361 )
2362 expected_start = datetime.datetime(2010, 6, 23, 9, 45, 0)
2363 # subtract 30 microseconds
2364 experiments = experiments.annotate(
2365 new_start=F("new_start") + datetime.timedelta(microseconds=-30)
2366 )
2367 expected_start += datetime.timedelta(microseconds=+746970)
2368 experiments.update(start=F("new_start"))
2369 e0 = Experiment.objects.get(name="e0")
2370 self.assertEqual(e0.start, expected_start)
2371
2372
2373 class ValueTests(TestCase):
2374 def test_update_TimeField_using_Value(self):
2375 Time.objects.create()
2376 Time.objects.update(time=Value(datetime.time(1), output_field=TimeField()))
2377 self.assertEqual(Time.objects.get().time, datetime.time(1))
2378
2379 def test_update_UUIDField_using_Value(self):
2380 UUID.objects.create()
2381 UUID.objects.update(
2382 uuid=Value(
2383 uuid.UUID("12345678901234567890123456789012"), output_field=UUIDField()
2384 )
2385 )
2386 self.assertEqual(
2387 UUID.objects.get().uuid, uuid.UUID("12345678901234567890123456789012")
2388 )
2389
2390 def test_deconstruct(self):
2391 value = Value("name")
2392 path, args, kwargs = value.deconstruct()
2393 self.assertEqual(path, "django.db.models.Value")
2394 self.assertEqual(args, (value.value,))
2395 self.assertEqual(kwargs, {})
2396
2397 def test_deconstruct_output_field(self):
2398 value = Value("name", output_field=CharField())
2399 path, args, kwargs = value.deconstruct()
2400 self.assertEqual(path, "django.db.models.Value")
2401 self.assertEqual(args, (value.value,))
2402 self.assertEqual(len(kwargs), 1)
2403 self.assertEqual(
2404 kwargs["output_field"].deconstruct(), CharField().deconstruct()
2405 )
2406
2407 def test_repr(self):
2408 tests = [
2409 (None, "Value(None)"),
2410 ("str", "Value('str')"),
2411 (True, "Value(True)"),
2412 (42, "Value(42)"),
2413 (
2414 datetime.datetime(2019, 5, 15),
2415 "Value(datetime.datetime(2019, 5, 15, 0, 0))",
2416 ),
2417 (Decimal("3.14"), "Value(Decimal('3.14'))"),
2418 ]
2419 for value, expected in tests:
2420 with self.subTest(value=value):
2421 self.assertEqual(repr(Value(value)), expected)
2422
2423 def test_equal(self):
2424 value = Value("name")
2425 self.assertEqual(value, Value("name"))
2426 self.assertNotEqual(value, Value("username"))
2427
2428 def test_hash(self):
2429 d = {Value("name"): "Bob"}
2430 self.assertIn(Value("name"), d)
2431 self.assertEqual(d[Value("name")], "Bob")
2432
2433 def test_equal_output_field(self):
2434 value = Value("name", output_field=CharField())
2435 same_value = Value("name", output_field=CharField())
2436 other_value = Value("name", output_field=TimeField())
2437 no_output_field = Value("name")
2438 self.assertEqual(value, same_value)
2439 self.assertNotEqual(value, other_value)
2440 self.assertNotEqual(value, no_output_field)
2441
2442 def test_compile_unresolved(self):
2443 # This test might need to be revisited later on if #25425 is enforced.
2444 compiler = Time.objects.all().query.get_compiler(connection=connection)
2445 value = Value("foo")
2446 self.assertEqual(value.as_sql(compiler, connection), ("%s", ["foo"]))
2447 value = Value("foo", output_field=CharField())
2448 self.assertEqual(value.as_sql(compiler, connection), ("%s", ["foo"]))
2449
2450 def test_output_field_decimalfield(self):
2451 Time.objects.create()
2452 time = Time.objects.annotate(one=Value(1, output_field=DecimalField())).first()
2453 self.assertEqual(time.one, 1)
2454
2455 def test_output_field_is_none_error(self):
2456 with self.assertRaises(OutputFieldIsNoneError):
2457 Employee.objects.annotate(custom_expression=Value(None)).first()
2458
2459 def test_output_field_or_none_property_not_cached(self):
2460 expression = Value(None, output_field=None)
2461 self.assertIsNone(expression._output_field_or_none)
2462 expression.output_field = BooleanField()
2463 self.assertIsInstance(expression._output_field_or_none, BooleanField)
2464
2465 def test_resolve_output_field(self):
2466 value_types = [
2467 ("str", CharField),
2468 (True, BooleanField),
2469 (42, IntegerField),
2470 (3.14, FloatField),
2471 (datetime.date(2019, 5, 15), DateField),
2472 (datetime.datetime(2019, 5, 15), DateTimeField),
2473 (datetime.time(3, 16), TimeField),
2474 (datetime.timedelta(1), DurationField),
2475 (Decimal("3.14"), DecimalField),
2476 (b"", BinaryField),
2477 (uuid.uuid4(), UUIDField),
2478 ]
2479 for value, output_field_type in value_types:
2480 with self.subTest(type=type(value)):
2481 expr = Value(value)
2482 self.assertIsInstance(expr.output_field, output_field_type)
2483
2484 def test_resolve_output_field_failure(self):
2485 msg = "Cannot resolve expression type, unknown output_field"
2486 with self.assertRaisesMessage(FieldError, msg):
2487 Value(object()).output_field
2488
2489 def test_output_field_does_not_create_broken_validators(self):
2490 """
2491 The output field for a given Value doesn't get cleaned & validated,
2492 however validators may still be instantiated for a given field type
2493 and this demonstrates that they don't throw an exception.
2494 """
2495 value_types = [
2496 "str",
2497 True,
2498 42,
2499 3.14,
2500 datetime.date(2019, 5, 15),
2501 datetime.datetime(2019, 5, 15),
2502 datetime.time(3, 16),
2503 datetime.timedelta(1),
2504 Decimal("3.14"),
2505 b"",
2506 uuid.uuid4(),
2507 ]
2508 for value in value_types:
2509 with self.subTest(type=type(value)):
2510 field = Value(value)._resolve_output_field()
2511 field.clean(value, model_instance=None)
2512
2513
2514 class ExistsTests(TestCase):
2515 def test_optimizations(self):
2516 with CaptureQueriesContext(connection) as context:
2517 list(
2518 Experiment.objects.values(
2519 exists=Exists(
2520 Experiment.objects.order_by("pk"),
2521 )
2522 ).order_by()
2523 )
2524 captured_queries = context.captured_queries
2525 self.assertEqual(len(captured_queries), 1)
2526 captured_sql = captured_queries[0]["sql"]
2527 self.assertNotIn(
2528 connection.ops.quote_name(Experiment._meta.pk.column),
2529 captured_sql,
2530 )
2531 self.assertIn(
2532 connection.ops.limit_offset_sql(None, 1),
2533 captured_sql,
2534 )
2535 self.assertNotIn("ORDER BY", captured_sql)
2536
2537 def test_negated_empty_exists(self):
2538 manager = Manager.objects.create()
2539 qs = Manager.objects.filter(~Exists(Manager.objects.none()) & Q(pk=manager.pk))
2540 self.assertSequenceEqual(qs, [manager])
2541
2542 def test_select_negated_empty_exists(self):
2543 manager = Manager.objects.create()
2544 qs = Manager.objects.annotate(
2545 not_exists=~Exists(Manager.objects.none())
2546 ).filter(pk=manager.pk)
2547 self.assertSequenceEqual(qs, [manager])
2548 self.assertIs(qs.get().not_exists, True)
2549
2550 def test_filter_by_empty_exists(self):
2551 manager = Manager.objects.create()
2552 qs = Manager.objects.annotate(exists=Exists(Manager.objects.none())).filter(
2553 pk=manager.pk, exists=False
2554 )
2555 self.assertSequenceEqual(qs, [manager])
2556 self.assertIs(qs.get().exists, False)
2557
2558
2559 class FieldTransformTests(TestCase):
2560 @classmethod
2561 def setUpTestData(cls):
2562 cls.sday = sday = datetime.date(2010, 6, 25)
2563 cls.stime = stime = datetime.datetime(2010, 6, 25, 12, 15, 30, 747000)
2564 cls.ex1 = Experiment.objects.create(
2565 name="Experiment 1",
2566 assigned=sday,
2567 completed=sday + datetime.timedelta(2),
2568 estimated_time=datetime.timedelta(2),
2569 start=stime,
2570 end=stime + datetime.timedelta(2),
2571 )
2572
2573 def test_month_aggregation(self):
2574 self.assertEqual(
2575 Experiment.objects.aggregate(month_count=Count("assigned__month")),
2576 {"month_count": 1},
2577 )
2578
2579 def test_transform_in_values(self):
2580 self.assertSequenceEqual(
2581 Experiment.objects.values("assigned__month"),
2582 [{"assigned__month": 6}],
2583 )
2584
2585 def test_multiple_transforms_in_values(self):
2586 self.assertSequenceEqual(
2587 Experiment.objects.values("end__date__month"),
2588 [{"end__date__month": 6}],
2589 )
2590
2591
2592 class ReprTests(SimpleTestCase):
2593 def test_expressions(self):
2594 self.assertEqual(
2595 repr(Case(When(a=1))),
2596 "<Case: CASE WHEN <Q: (AND: ('a', 1))> THEN Value(None), ELSE Value(None)>",
2597 )
2598 self.assertEqual(
2599 repr(When(Q(age__gte=18), then=Value("legal"))),
2600 "<When: WHEN <Q: (AND: ('age__gte', 18))> THEN Value('legal')>",
2601 )
2602 self.assertEqual(repr(Col("alias", "field")), "Col(alias, field)")
2603 self.assertEqual(
2604 repr(ColPairs("alias", ["t1", "t2"], ["s1", "s2"], "f")),
2605 "ColPairs('alias', ['t1', 't2'], ['s1', 's2'], 'f')",
2606 )
2607 self.assertEqual(repr(F("published")), "F(published)")
2608 self.assertEqual(
2609 repr(F("cost") + F("tax")), "<CombinedExpression: F(cost) + F(tax)>"
2610 )
2611 self.assertEqual(
2612 repr(ExpressionWrapper(F("cost") + F("tax"), IntegerField())),
2613 "ExpressionWrapper(F(cost) + F(tax))",
2614 )
2615 self.assertEqual(
2616 repr(Func("published", function="TO_CHAR")),
2617 "Func(F(published), function=TO_CHAR)",
2618 )
2619 self.assertEqual(
2620 repr(F("published")[0:2]), "Sliced(F(published), slice(0, 2, None))"
2621 )
2622 self.assertEqual(
2623 repr(OuterRef("name")[1:5]), "Sliced(OuterRef(name), slice(1, 5, None))"
2624 )
2625 self.assertEqual(repr(OrderBy(Value(1))), "OrderBy(Value(1), descending=False)")
2626 self.assertEqual(repr(RawSQL("table.col", [])), "RawSQL(table.col, [])")
2627 self.assertEqual(
2628 repr(Ref("sum_cost", Sum("cost"))), "Ref(sum_cost, Sum(F(cost)))"
2629 )
2630 self.assertEqual(repr(Value(1)), "Value(1)")
2631 self.assertEqual(
2632 repr(ExpressionList(F("col"), F("anothercol"))),
2633 "ExpressionList(F(col), F(anothercol))",
2634 )
2635 self.assertEqual(
2636 repr(ExpressionList(OrderBy(F("col"), descending=False))),
2637 "ExpressionList(OrderBy(F(col), descending=False))",
2638 )
2639
2640 def test_functions(self):
2641 self.assertEqual(repr(Coalesce("a", "b")), "Coalesce(F(a), F(b))")
2642 self.assertEqual(repr(Concat("a", "b")), "Concat(ConcatPair(F(a), F(b)))")
2643 self.assertEqual(repr(Length("a")), "Length(F(a))")
2644 self.assertEqual(repr(Lower("a")), "Lower(F(a))")
2645 self.assertEqual(repr(Substr("a", 1, 3)), "Substr(F(a), Value(1), Value(3))")
2646 self.assertEqual(repr(Upper("a")), "Upper(F(a))")
2647
2648 def test_aggregates(self):
2649 self.assertEqual(repr(Avg("a")), "Avg(F(a))")
2650 self.assertEqual(repr(Count("a")), "Count(F(a))")
2651 self.assertEqual(repr(Count("*")), "Count('*')")
2652 self.assertEqual(repr(Max("a")), "Max(F(a))")
2653 self.assertEqual(repr(Min("a")), "Min(F(a))")
2654 self.assertEqual(repr(StdDev("a")), "StdDev(F(a), sample=False)")
2655 self.assertEqual(repr(Sum("a")), "Sum(F(a))")
2656 self.assertEqual(
2657 repr(Variance("a", sample=True)), "Variance(F(a), sample=True)"
2658 )
2659
2660 def test_distinct_aggregates(self):
2661 self.assertEqual(repr(Count("a", distinct=True)), "Count(F(a), distinct=True)")
2662 self.assertEqual(repr(Count("*", distinct=True)), "Count('*', distinct=True)")
2663
2664 def test_filtered_aggregates(self):
2665 filter = Q(a=1)
2666 self.assertEqual(
2667 repr(Avg("a", filter=filter)), "Avg(F(a), filter=(AND: ('a', 1)))"
2668 )
2669 self.assertEqual(
2670 repr(Count("a", filter=filter)), "Count(F(a), filter=(AND: ('a', 1)))"
2671 )
2672 self.assertEqual(
2673 repr(Max("a", filter=filter)), "Max(F(a), filter=(AND: ('a', 1)))"
2674 )
2675 self.assertEqual(
2676 repr(Min("a", filter=filter)), "Min(F(a), filter=(AND: ('a', 1)))"
2677 )
2678 self.assertEqual(
2679 repr(StdDev("a", filter=filter)),
2680 "StdDev(F(a), filter=(AND: ('a', 1)), sample=False)",
2681 )
2682 self.assertEqual(
2683 repr(Sum("a", filter=filter)), "Sum(F(a), filter=(AND: ('a', 1)))"
2684 )
2685 self.assertEqual(
2686 repr(Variance("a", sample=True, filter=filter)),
2687 "Variance(F(a), filter=(AND: ('a', 1)), sample=True)",
2688 )
2689 self.assertEqual(
2690 repr(Count("a", filter=filter, distinct=True)),
2691 "Count(F(a), distinct=True, filter=(AND: ('a', 1)))",
2692 )
2693
2694
2695 class CombinableTests(SimpleTestCase):
2696 bitwise_msg = (
2697 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
2698 )
2699
2700 def test_negation(self):
2701 c = Combinable()
2702 self.assertEqual(-c, c * -1)
2703
2704 def test_and(self):
2705 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2706 Combinable() & Combinable()
2707
2708 def test_or(self):
2709 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2710 Combinable() | Combinable()
2711
2712 def test_xor(self):
2713 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2714 Combinable() ^ Combinable()
2715
2716 def test_reversed_and(self):
2717 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2718 object() & Combinable()
2719
2720 def test_reversed_or(self):
2721 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2722 object() | Combinable()
2723
2724 def test_reversed_xor(self):
2725 with self.assertRaisesMessage(NotImplementedError, self.bitwise_msg):
2726 object() ^ Combinable()
2727
2728
2729 class CombinedExpressionTests(SimpleTestCase):
2730 def test_resolve_output_field_positive_integer(self):
2731 connectors = [
2732 Combinable.ADD,
2733 Combinable.MUL,
2734 Combinable.DIV,
2735 Combinable.MOD,
2736 Combinable.POW,
2737 ]
2738 for connector in connectors:
2739 with self.subTest(connector=connector):
2740 expr = CombinedExpression(
2741 Expression(PositiveIntegerField()),
2742 connector,
2743 Expression(PositiveIntegerField()),
2744 )
2745 self.assertIsInstance(expr.output_field, PositiveIntegerField)
2746
2747 def test_resolve_output_field_number(self):
2748 tests = [
2749 (IntegerField, AutoField, IntegerField),
2750 (AutoField, IntegerField, IntegerField),
2751 (IntegerField, DecimalField, DecimalField),
2752 (DecimalField, IntegerField, DecimalField),
2753 (IntegerField, FloatField, FloatField),
2754 (FloatField, IntegerField, FloatField),
2755 ]
2756 connectors = [
2757 Combinable.ADD,
2758 Combinable.SUB,
2759 Combinable.MUL,
2760 Combinable.DIV,
2761 Combinable.MOD,
2762 ]
2763 for lhs, rhs, combined in tests:
2764 for connector in connectors:
2765 with self.subTest(
2766 lhs=lhs, connector=connector, rhs=rhs, combined=combined
2767 ):
2768 expr = CombinedExpression(
2769 Expression(lhs()),
2770 connector,
2771 Expression(rhs()),
2772 )
2773 self.assertIsInstance(expr.output_field, combined)
2774
2775 def test_resolve_output_field_with_null(self):
2776 def null():
2777 return Value(None)
2778
2779 tests = [
2780 # Numbers.
2781 (AutoField, Combinable.ADD, null),
2782 (DecimalField, Combinable.ADD, null),
2783 (FloatField, Combinable.ADD, null),
2784 (IntegerField, Combinable.ADD, null),
2785 (IntegerField, Combinable.SUB, null),
2786 (null, Combinable.ADD, IntegerField),
2787 # Dates.
2788 (DateField, Combinable.ADD, null),
2789 (DateTimeField, Combinable.ADD, null),
2790 (DurationField, Combinable.ADD, null),
2791 (TimeField, Combinable.ADD, null),
2792 (TimeField, Combinable.SUB, null),
2793 (null, Combinable.ADD, DateTimeField),
2794 (DateField, Combinable.SUB, null),
2795 ]
2796 for lhs, connector, rhs in tests:
2797 msg = (
2798 f"Cannot infer type of {connector!r} expression involving these types: "
2799 )
2800 with self.subTest(lhs=lhs, connector=connector, rhs=rhs):
2801 expr = CombinedExpression(
2802 Expression(lhs()),
2803 connector,
2804 Expression(rhs()),
2805 )
2806 with self.assertRaisesMessage(FieldError, msg):
2807 expr.output_field
2808
2809 def test_resolve_output_field_numbers_with_null(self):
2810 test_values = [
2811 (3.14159, None, FloatField),
2812 (None, 3.14159, FloatField),
2813 (None, 42, IntegerField),
2814 (42, None, IntegerField),
2815 (None, Decimal("3.14"), DecimalField),
2816 (Decimal("3.14"), None, DecimalField),
2817 ]
2818 connectors = [
2819 Combinable.ADD,
2820 Combinable.SUB,
2821 Combinable.MUL,
2822 Combinable.DIV,
2823 Combinable.MOD,
2824 Combinable.POW,
2825 ]
2826 for lhs, rhs, expected_output_field in test_values:
2827 for connector in connectors:
2828 with self.subTest(lhs=lhs, connector=connector, rhs=rhs):
2829 expr = CombinedExpression(Value(lhs), connector, Value(rhs))
2830 self.assertIsInstance(expr.output_field, expected_output_field)
2831
2832 def test_resolve_output_field_dates(self):
2833 tests = [
2834 # Add - same type.
2835 (DateField, Combinable.ADD, DateField, FieldError),
2836 (DateTimeField, Combinable.ADD, DateTimeField, FieldError),
2837 (TimeField, Combinable.ADD, TimeField, FieldError),
2838 (DurationField, Combinable.ADD, DurationField, DurationField),
2839 # Add - different type.
2840 (DateField, Combinable.ADD, DurationField, DateTimeField),
2841 (DateTimeField, Combinable.ADD, DurationField, DateTimeField),
2842 (TimeField, Combinable.ADD, DurationField, TimeField),
2843 (DurationField, Combinable.ADD, DateField, DateTimeField),
2844 (DurationField, Combinable.ADD, DateTimeField, DateTimeField),
2845 (DurationField, Combinable.ADD, TimeField, TimeField),
2846 # Subtract - same type.
2847 (DateField, Combinable.SUB, DateField, DurationField),
2848 (DateTimeField, Combinable.SUB, DateTimeField, DurationField),
2849 (TimeField, Combinable.SUB, TimeField, DurationField),
2850 (DurationField, Combinable.SUB, DurationField, DurationField),
2851 # Subtract - different type.
2852 (DateField, Combinable.SUB, DurationField, DateTimeField),
2853 (DateTimeField, Combinable.SUB, DurationField, DateTimeField),
2854 (TimeField, Combinable.SUB, DurationField, TimeField),
2855 (DurationField, Combinable.SUB, DateField, FieldError),
2856 (DurationField, Combinable.SUB, DateTimeField, FieldError),
2857 (DurationField, Combinable.SUB, DateTimeField, FieldError),
2858 ]
2859 for lhs, connector, rhs, combined in tests:
2860 msg = (
2861 f"Cannot infer type of {connector!r} expression involving these types: "
2862 )
2863 with self.subTest(lhs=lhs, connector=connector, rhs=rhs, combined=combined):
2864 expr = CombinedExpression(
2865 Expression(lhs()),
2866 connector,
2867 Expression(rhs()),
2868 )
2869 if issubclass(combined, Exception):
2870 with self.assertRaisesMessage(combined, msg):
2871 expr.output_field
2872 else:
2873 self.assertIsInstance(expr.output_field, combined)
2874
2875 def test_mixed_char_date_with_annotate(self):
2876 queryset = Experiment.objects.annotate(nonsense=F("name") + F("assigned"))
2877 msg = (
2878 "Cannot infer type of '+' expression involving these types: CharField, "
2879 "DateField. You must set output_field."
2880 )
2881 with self.assertRaisesMessage(FieldError, msg):
2882 list(queryset)
2883
2884
2885 class ExpressionWrapperTests(SimpleTestCase):
2886 def test_empty_group_by(self):
2887 expr = ExpressionWrapper(Value(3), output_field=IntegerField())
2888 self.assertEqual(expr.get_group_by_cols(), [])
2889
2890 def test_non_empty_group_by(self):
2891 value = Value("f")
2892 value.output_field = None
2893 expr = ExpressionWrapper(Lower(value), output_field=IntegerField())
2894 group_by_cols = expr.get_group_by_cols()
2895 self.assertEqual(group_by_cols, [expr.expression])
2896 self.assertEqual(group_by_cols[0].output_field, expr.output_field)
2897
2898
2899 class NegatedExpressionTests(TestCase):
2900 @classmethod
2901 def setUpTestData(cls):
2902 ceo = Employee.objects.create(firstname="Joe", lastname="Smith", salary=10)
2903 cls.eu_company = Company.objects.create(
2904 name="Example Inc.",
2905 num_employees=2300,
2906 num_chairs=5,
2907 ceo=ceo,
2908 based_in_eu=True,
2909 )
2910 cls.non_eu_company = Company.objects.create(
2911 name="Foobar Ltd.",
2912 num_employees=3,
2913 num_chairs=4,
2914 ceo=ceo,
2915 based_in_eu=False,
2916 )
2917
2918 def test_invert(self):
2919 f = F("field")
2920 self.assertEqual(~f, NegatedExpression(f))
2921 self.assertIsNot(~~f, f)
2922 self.assertEqual(~~f, f)
2923
2924 def test_filter(self):
2925 self.assertSequenceEqual(
2926 Company.objects.filter(~F("based_in_eu")),
2927 [self.non_eu_company],
2928 )
2929
2930 qs = Company.objects.annotate(eu_required=~Value(False))
2931 self.assertSequenceEqual(
2932 qs.filter(based_in_eu=F("eu_required")).order_by("eu_required"),
2933 [self.eu_company],
2934 )
2935 self.assertSequenceEqual(
2936 qs.filter(based_in_eu=~~F("eu_required")),
2937 [self.eu_company],
2938 )
2939 self.assertSequenceEqual(
2940 qs.filter(based_in_eu=~F("eu_required")),
2941 [self.non_eu_company],
2942 )
2943 self.assertSequenceEqual(qs.filter(based_in_eu=~F("based_in_eu")), [])
2944
2945 def test_values(self):
2946 self.assertSequenceEqual(
2947 Company.objects.annotate(negated=~F("based_in_eu"))
2948 .values_list("name", "negated")
2949 .order_by("name"),
2950 [("Example Inc.", False), ("Foobar Ltd.", True)],
2951 )
2952
2953
2954 class OrderByTests(SimpleTestCase):
2955 def test_equal(self):
2956 self.assertEqual(
2957 OrderBy(F("field"), nulls_last=True),
2958 OrderBy(F("field"), nulls_last=True),
2959 )
2960 self.assertNotEqual(
2961 OrderBy(F("field"), nulls_last=True),
2962 OrderBy(F("field")),
2963 )
2964
2965 def test_hash(self):
2966 self.assertEqual(
2967 hash(OrderBy(F("field"), nulls_last=True)),
2968 hash(OrderBy(F("field"), nulls_last=True)),
2969 )
2970 self.assertNotEqual(
2971 hash(OrderBy(F("field"), nulls_last=True)),
2972 hash(OrderBy(F("field"))),
2973 )
2974
2975 def test_nulls_false(self):
2976 msg = "nulls_first and nulls_last values must be True or None."
2977 with self.assertRaisesMessage(ValueError, msg):
2978 OrderBy(F("field"), nulls_first=False)
2979 with self.assertRaisesMessage(ValueError, msg):
2980 OrderBy(F("field"), nulls_last=False)
2981 with self.assertRaisesMessage(ValueError, msg):
2982 F("field").asc(nulls_first=False)
2983 with self.assertRaisesMessage(ValueError, msg):
2984 F("field").desc(nulls_last=False)