Opened 5 years ago

Last modified 5 years ago

#31312 closed Bug

Wrong MySQL query generation after version 3.0.3. — at Initial Version

Reported by: Ricardo Helou Owned by: nobody
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

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`

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top