Problem with ExtractYear()+1 in queries

[Alexey Chernov]

It looks like django doesn't create a correct MySQL query when having a simple arithmetics with ExtractYear().

I have a model with two data fields:

class Event(models.Model):
	start_date = models.DateField()
	finish_date = models.DateField()

If I want to find all rows such that the years of start_date and finish_date are equal, I can do it so:

from django.db.models import F
from django.db.models.functions import ExtractYear
>>> print Event.objects.annotate(year=ExtractYear(F('start_date'))).filter(finish_date__year=F('year')).only('pk').query
SELECT `event`.`id`, EXTRACT(YEAR FROM `event`.`start_date`) AS `year` FROM `event` WHERE EXTRACT(YEAR FROM `event`.`finish_date`) = (EXTRACT(YEAR FROM `event`.`start_date`))

But if I want to find events that start and finish in consequent years, I try the following filter, and it gives me an incorrect MySQL query:

>>> print Event.objects.annotate(year=ExtractYear(F('start_date'))).filter(finish_date__year=F('year')+1).only('pk').query
SELECT `event`.`id`, EXTRACT(YEAR FROM `event`.`start_date`) AS `year` FROM `event` WHERE `event`.`finish_date` BETWEEN 0001-01-01 AND 0001-12-31

I tried to replace F('year')+1 with F('year')+Value(1) but it didn't help.
Am I wrong somewhere, or does it look like a bug?

[Simon Charette]

I confirmed this is still an issue at 1d0bab0bfd77edcf1228d45bf654457a8ff1890d.

The issue likely lies in YearExact.as_sql ​https://github.com/django/django/blob/1d0bab0bfd77edcf1228d45bf654457a8ff1890d/django/db/models/lookups.py#L507-L525

[Simon Charette]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 4d142094:

Fixed #30494 -- Disabled year lookup optimization for indirect values.

The previous heuristics were naively enabling the BETWEEN optimization on
successful cast of the first rhs SQL params to an integer while it was
not appropriate for a lot of database resolved expressions.

Thanks Alexey Chernov for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 514104cf:

Refs #29396, #30494 -- Reduced code duplication in year lookups.