[Django] #30494: Problem with ExtractYear()+1 in queries
Django
-------------------------------------+-------------------------------------
Reporter: Alexey | Owner: nobody
Chernov |
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 |
-------------------------------------+-------------------------------------
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/30494>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.2 => 1.11
--
Ticket URL: <https://code.djangoproject.com/ticket/30494#comment:1>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
* version: 1.11 => master
* stage: Unreviewed => Accepted
Comment:
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
--
Ticket URL: <https://code.djangoproject.com/ticket/30494#comment:2>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30494#comment:3>
Django
-------------------------------------+-------------------------------------
| Charette
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"4d1420947e79bc89d266229feea305294ec896ee" 4d142094]:
{{{
#!CommitTicketReference repository=""
revision="4d1420947e79bc89d266229feea305294ec896ee"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30494#comment:4>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"514104cf236c1039644b70c0c0f128cecd42b233" 514104cf]:
{{{
#!CommitTicketReference repository=""
revision="514104cf236c1039644b70c0c0f128cecd42b233"
Refs #29396, #30494 -- Reduced code duplication in year lookups.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30494#comment:5>