#32573: Query optimization in YearLookup breaks filtering by "__iso_year"
-------------------------------------+-------------------------------------
Reporter: Florian Demmer | Owner: Florian
| Demmer
Type: Bug | Status: assigned
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Florian Demmer:
Old description:
> 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.
>
> This PR fixes the behaviour, reverts the invalid changes to the tests and
> extends one test to catch this problem:
> https://github.com/django/django/pull/14157
New description:
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 [https://docs.djangoproject.com/en/2.2/ref/models/querysets
/#iso-year 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.
This PR fixes the behaviour, reverts the invalid changes to the tests and
extends one test to catch this problem:
https://github.com/django/django/pull/14157
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32573#comment:2>
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/065.6d1fd26161ed03d3f18bdc280291e65f%40djangoproject.com.
