id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31312	Wrong MySQL query generation after version 3.0.3.	Ricardo Helou	Simon Charette	"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"	Bug	closed	Database layer (models, ORM)	3.0	Release blocker	fixed	mysql ORM DateTimeField DurationField regression		Ready for checkin	1	0	0	0	0	0