#31312: Wrong MySQL query generation after version 3.0.3.
-------------------------------------+-------------------------------------
Reporter: rick2ricks | Owner: nobody
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
-------------------------------------+-------------------------------------
Changes (by rick2ricks):
* keywords: mysql ORM DateTimeField DurationField => mysql ORM
DateTimeField DurationField regression
Old description:
> 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`
> }}}
New description:
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`
}}}
=== 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 <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/068.944353c7ae474de78c0d33ecc330bdbb%40djangoproject.com.
