#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
-------------------------------------+-------------------------------------
Changes (by Florian Demmer):
* owner: nobody => Florian Demmer
* status: new => assigned
* has_patch: 0 => 1
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.
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 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:1>
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/065.7d95b1269b67334374e684d2c82ee505%40djangoproject.com.
