Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

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

This commit might be the origin of the issue:

https://github.com/django/django/commit/02cda09b13e677db01863fa0a7112dba631b9c5c

Change History (7)

comment:1 by Ricardo Helou, 5 years ago

Description: modified (diff)
Keywords: regression added

comment:2 by Simon Charette, 5 years ago

Owner: changed from nobody to Simon Charette
Severity: NormalRelease blocker
Status: newassigned

Looks like I inverted lhs_params and rhs_params on this line.

comment:3 by Simon Charette, 5 years ago

Has patch: set
Triage Stage: UnreviewedAccepted

comment:4 by Mariusz Felisiak, 5 years ago

Triage Stage: AcceptedReady for checkin

comment:5 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In 41ebe607:

Fixed #31312 -- Properly ordered temporal subtraction params on MySQL.

Regression in 9bcbcd599abac91ea853b2fe10b784ba32df043e.

Thanks rick2ricks for the report.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 16cacdc:

[3.0.x] Fixed #31312 -- Properly ordered temporal subtraction params on MySQL.

Regression in 9bcbcd599abac91ea853b2fe10b784ba32df043e.

Thanks rick2ricks for the report.

Backport of 41ebe60728a15aa273f4d70de92f5246a89c3d4e from master

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 94e192a5:

[3.0.x] Refs #31312 -- Fixed FTimeDeltaTests.test_date_case_subtraction() test.

Follow up to 16cacdcb3f7856df5454b648503374de150fa245.

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