ORM syntax error in django3.2.8 but correct in 2.2

Using Django
1

Hi,

I upgraded Django 2.2 to 3.2.8 and the following problems occurred.

python 3.6.9
mysql 5.7.36 (Time zone table loaded)
ubuntu 18

models:

class A(models.Model):
    sot = models.IntegerField() # hour
    start_time = models.DateTimeField()
    end_time = models.DateTimeField()

ORM:

import A
from django.db.models import Q
from django.db.models.functions import TruncDate

query = Q(sot__gte=((TruncDate('end_time') - TruncDate('start_time')) / (3600 * 1000 * 1000)))
qs = A.objects.filter(query)

It was OK in 2.2, but it was wrong in 3.2.8.

ProgrammingError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ 3600) * 1000) * 1000)

I checked that the syntax of SQL in 3.2.8 queryset is indeed wrong, but I don’t know how to solve it.

2

Thanks for the report!

It’s a regression in Fixed #31755 -- Made temporal subtraction resolve output field. · django/django@9d519d3 · GitHub.

Can you create a new ticket?

3

On reconsideration, it looks like in Django < 3.2 it worked by luck. Intervals cannot be divided by integers on MySQL, so you should change the output_field, e.g.:

query = Q(sot__gte=ExpressionWrapper(TruncDate('end_time') - TruncDate('start_time'), output_field=IntegerField()) / (3600 * 1000 * 1000))
qs = A.objects.filter(query)
4

Yes this is what I was looking for! Thank you for your answer sincerely.