Context Navigation

← Previous Ticket
Next Ticket →

#31312 closed Bug (fixed)

Wrong MySQL query generation after version 3.0.3.

Reported by:	Ricardo Helou	Owned by:	Simon Charette
Component:	Database layer (models, ORM)	Version:	3.0
Severity:	Release blocker	Keywords:	mysql ORM DateTimeField DurationField regression
Cc:		Triage Stage:	Ready for checkin
Has patch:	yes	Needs documentation:	no
Needs tests:	no	Patch needs improvement:	no
Easy pickings:	no	UI/UX:	no

Description (last modified by Ricardo Helou)

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:

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