[Django] #32573: Query optimization in YearLookup breaks filtering by "__iso_year"

[Django]

#32573: Query optimization in YearLookup breaks filtering by "__iso_year"
-------------------------------------+-------------------------------------
               Reporter:  Florian    |          Owner:  nobody
  Demmer                             |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  2.2
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 The optimization to use BETWEEN instead of the EXTRACT operation in
 [https://github.com/django/django/blob/main/django/db/models/lookups.py#L540
 YearLookup] is also registered for the
 
[https://github.com/django/django/blob/main/django/db/models/functions/datetime.py#L167
 "__iso_year" lookup], which breaks the functionality provided by
 [https://docs.djangoproject.com/en/2.2/ref/models/database-
 functions/#django.db.models.functions.ExtractIsoYear ExtractIsoYear] when
 used via the lookup.

 This has unfortunately been broken ever since ExtractIsoYear was
 introduced in Django 2.2 via #28649 and wasn't easy to track down since
 ExtractIsoYear when used by itself eg. in an annotation works perfectly
 fine. Just when using the lookup in a filter, the optimization is used
 (even when explicitly using an annotation):

 {{{
 #!python
 # annotation works
 >>> qs =
 DTModel.objects.annotate(extracted=ExtractIsoYear('start_date')).only('id')
 >>> print(qs.query)
 SELECT "db_functions_dtmodel"."id", EXTRACT('isoyear' FROM
 "db_functions_dtmodel"."start_date") AS "extracted" FROM
 "db_functions_dtmodel"

 # explicit annotation used in filter does not use "extracted" and adds
 BETWEEN
 >>> print(qs.filter(extracted=2020).query)
 SELECT "db_functions_dtmodel"."id", EXTRACT('isoyear' FROM
 "db_functions_dtmodel"."start_date") AS "extracted" FROM
 "db_functions_dtmodel" WHERE "db_functions_dtmodel"."start_date" BETWEEN
 2020-01-01 AND 2020-12-31

 # implicit lookup uses BETWEEN
 print(DTModel.objects.filter(start_date__iso_year=2020).only('id').query)
 SELECT "db_functions_dtmodel"."id" FROM "db_functions_dtmodel" WHERE
 "db_functions_dtmodel"."start_date" BETWEEN 2020-01-01 AND 2020-12-31
 }}}

 This results in the wrong data being returned by filters using iso_year.

-- 
Ticket URL: <https://code.djangoproject.com/ticket/32573>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/050.e6fd204e0378d4841d3faab4dddd0c6c%40djangoproject.com.