#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.944353c7ae474de78c0d33ecc330bdbb%40djangoproject.com.
