id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32573	"Query optimization in YearLookup breaks filtering by ""__iso_year"""	Florian Demmer	nobody	"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.

"	Bug	new	Database layer (models, ORM)	2.2	Normal				Unreviewed	0	0	0	0	0	0