#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.e6fd204e0378d4841d3faab4dddd0c6c%40djangoproject.com.
