[Django] #31312: Wrong MySQL query generation after version 3.0.3.
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
rick2ricks |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) | Keywords: mysql ORM
Severity: Normal | DateTimeField DurationField
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
After updating from 3.0.2 to 3.0.3, Django is generating a wrong MySQL
query for the queryset method ''with_duration'' in the following example:
{{{
#!python
#models.py:
from datetime import datetime
import pytz
from django.db import models
from django.db.models.functions import Cast
class PhaseQueryset(models.QuerySet):
def with_duration(self,):
base_date = datetime(2000, 1, 3, 0, tzinfo=pytz.utc)
# When I use base_date to do the end_total_time math in 3.0.3
together
# with ended_at annotated, it creates a wrong query
qs = self.annotate(
ended_at=models.Case(
models.When(
models.Q(type='TYPEONE'),
then=models.functions.Now()
),
default=models.F('started_at'),
output_field=models.DateTimeField(),
),
base_date=models.functions.Cast(
models.Value(base_date),
output_field=models.DateTimeField()
),
end_total_time=models.ExpressionWrapper(
models.F('ended_at') - models.F('base_date'),
output_field=models.fields.DurationField()
)
)
print(qs.values('end_total_time').query)
return qs
# Create your models here.
class Phase(models.Model):
objects = PhaseQueryset().as_manager()
started_at = models.DateTimeField()
type = models.CharField(max_length=40)
}}}
== Result query on 3.0.3:
{{{
SELECT TIMESTAMPDIFF(MICROSECOND, CAST(TYPEONE AS datetime(6)),
CASE WHEN `daterror_phase`.`type` = 2000-01-03 00:00:00+00:00
THEN CURRENT_TIMESTAMP
ELSE `daterror_phase`.`started_at` END) AS `end_total_time`
FROM `daterror_phase`
}}}
== Result query on 3.0.2:
{{{
SELECT TIMESTAMPDIFF(MICROSECOND, CAST(2000-01-03 00:00:00+00:00 AS
datetime(6)),
CASE WHEN `daterror_phase`.`type` = TYPEONE
THEN CURRENT_TIMESTAMP
ELSE `daterror_phase`.`started_at` END) AS `end_total_time`
FROM `daterror_phase`
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31312>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql ORM | Triage Stage:
DateTimeField DurationField | Unreviewed
regression |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: mysql ORM DateTimeField DurationField => mysql ORM
DateTimeField DurationField regression
Old description:
New description:
{{{
#!python
#models.py:
class PhaseQueryset(models.QuerySet):
}}}
=== This commit might be the origin of the issue:
https://github.com/django/django/commit/02cda09b13e677db01863fa0a7112dba631b9c5c
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:1>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Keywords: mysql ORM | Triage Stage:
DateTimeField DurationField | Unreviewed
regression |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
* severity: Normal => Release blocker
Comment:
Looks like I inverted `lhs_params` and `rhs_params`
[https://github.com/django/django/commit/02cda09b13e677db01863fa0a7112dba631b9c5c
#diff-a7516854a0eab5c74e491a6dc8249c2dR290 on this line].
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:2>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
DateTimeField DurationField |
regression |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* stage: Unreviewed => Accepted
Comment:
https://github.com/django/django/pull/12500
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:3>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
DateTimeField DurationField | checkin
regression |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:4>
Django
-------------------------------------+-------------------------------------
| Charette
Component: Database layer | Version: 3.0
(models, ORM) |
Keywords: mysql ORM | Triage Stage: Ready for
DateTimeField DurationField | checkin
regression |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"41ebe60728a15aa273f4d70de92f5246a89c3d4e" 41ebe607]:
{{{
#!CommitTicketReference repository=""
revision="41ebe60728a15aa273f4d70de92f5246a89c3d4e"
Fixed #31312 -- Properly ordered temporal subtraction params on MySQL.
Regression in 9bcbcd599abac91ea853b2fe10b784ba32df043e.
Thanks rick2ricks for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:5>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: mysql ORM | Triage Stage: Ready for
DateTimeField DurationField | checkin
regression |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"16cacdcb3f7856df5454b648503374de150fa245" 16cacdc]:
{{{
#!CommitTicketReference repository=""
revision="16cacdcb3f7856df5454b648503374de150fa245"
[3.0.x] Fixed #31312 -- Properly ordered temporal subtraction params on
MySQL.
Regression in 9bcbcd599abac91ea853b2fe10b784ba32df043e.
Thanks rick2ricks for the report.
Backport of 41ebe60728a15aa273f4d70de92f5246a89c3d4e from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:6>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: mysql ORM | Triage Stage: Ready for
DateTimeField DurationField | checkin
regression |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"94e192a580374259d9e52432f007fd2b49a8672d" 94e192a5]:
{{{
#!CommitTicketReference repository=""
revision="94e192a580374259d9e52432f007fd2b49a8672d"
[3.0.x] Refs #31312 -- Fixed FTimeDeltaTests.test_date_case_subtraction()
test.
Follow up to 16cacdcb3f7856df5454b648503374de150fa245.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31312#comment:7>