[Django] #31312: Wrong MySQL query generation after version 3.0.3.

[Django]

#31312: Wrong MySQL query generation after version 3.0.3.
-------------------------------------+-------------------------------------
               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.

-- 
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/053.52f9d876b7a1e21782ff659f066afddf%40djangoproject.com.