#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.