Django queryset result and mysql query are wrong after updating from 3.0.2 to 3.0.3

[Ricardo H]

Hello,  after I updated django from 3.0.2 to 3.0.3 the result of the following code has changed when using a MySQL database. It produces a wrong sql query and also the wrong result for "end_total_time" attribute.

Is this a bug ? also is there any other way to get the wright result using 3.0.3 ?

Thank you.

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.BigIntegerField()
            )
        )


        return qs


# Create your models here.
class Phase(models.Model):
    objects = PhaseQueryset().as_manager()
    started_at = models.DateTimeField()
    type = models.CharField(max_length=40)

tests.py

from datetime import datetime, timedelta
import pytz


from django.test import TestCase
from daterror.models import Phase
# Create your tests here.


class TestDateProblem(TestCase):


    def setUp(self,):
        past = datetime.now(tz=pytz.UTC) - timedelta(days=30)
        Phase.objects.create(started_at=past, type='TYPEONE')
        past = datetime.now(tz=pytz.UTC) - timedelta(days=33)
        Phase.objects.create(started_at=past, type='TYPETWO')
        past = datetime.now(tz=pytz.UTC) - timedelta(days=34)
        Phase.objects.create(started_at=past, type='TYPETHREE')




    def test_timedifference_not_none(self,):
        phases = Phase.objects.all().with_duration()
        print(phases[0].end_total_time)
        print(phases[1].end_total_time)
        print(phases[2].end_total_time)
        self.assertNotEqual(None, phases[0].end_total_time)
        self.assertNotEqual(None, phases[1].end_total_time)
        self.assertNotEqual(None, phases[2].end_total_time)

[Simon Charette]

Hello Ricardo,

This could be a regression caused by a patch addressing a regression in 3.0.3[0].

Did you also notice this behaviour on Django 2.2.x or was this project started from Django 3.0?

From what I can see though I find it strange that the ExpressionWrapper of end_total_time

have an output_field of BigIntegerField instead of DurationField. That could be the origin

of your issue.

Cheers,

Simon

[0] https://github.com/django/django/commit/02cda09b13e677db01863fa0a7112dba631b9c5c

[Ricardo H]

Hi Simon, 

It was ok on 2.2.6, only on 3.0.3 it fails.

I use BigIntegerField because I use it for some calculations after that, but even if I try with DurationField it doens't assert right.

Definitely must be caused by this patch, if I can't find another solution, where and how should I report this ?.

Thanks.

[Simon Charette]

Hello Ricardo,

I suggest you follow these docs to submit your bug report and link to this thread.

https://docs.djangoproject.com/en/3.0/internals/contributing/bugs-and-features/#reporting-bugs

I'd mention that the issue is also present if you use `output_field=DurationField` as BigIntegerField

was likely only working by change.

Cheers,

Simon

[mohamed Alisaleh501@gmail.com]