Multiplying and dividing connectors for duration expressions are not supported on SQLite and MySQL.

[Ahmet DAL]

I have a query using F expressions. When I run this query on PostgreSQL it works. But in tests with sqlite3, it does not work;

Here is my complex query.

expires = Expire.objects.filter(ticket__date_opened__lte=(datetime.now() - F("action__percent") * F("action__rule__duration") / 100))

Sqlite backend does not support connector *. When I looked into the code, it only supports + and - connectors.

DatabaseError: Invalid connector for timedelta: *.

    def combine_duration_expression(self, connector, sub_expressions):
        if connector not in ['+', '-']:
            raise utils.DatabaseError('Invalid connector for timedelta: %s.' % connector)
        fn_params = ["'%s'" % connector] + sub_expressions
        if len(fn_params) > 3:
            raise ValueError('Too many params for timedelta operations.')
        return "django_format_dtdelta(%s)" % ', '.join(fn_params)

I tried to add * and / operator in the list; no problem occured. I think this can be fixed simply.

[Tim Graham]

Feel free to submit a tested patch if you are able.

[Josh Smeaton]

This looks like an order of precedence issue. Durations only support + and -, but F() expressions support them all. Try rewriting your query like this please?

expires = Expire.objects.filter(
    ticket__date_opened__lte=
       datetime.now() - (
           (F("action__percent") * F("action__rule__duration")) / 100
       )
)

I've broken up the query over multiple lines to show where the brackets should be placed. The query should be datetime() - ( calculation ).

[Josh Smeaton]

You may also need an ExpressionWrapper to provide the right hand side output type:

expires = Expire.objects.filter(
    ticket__date_opened__lte=
       datetime.now() - ExpressionWrapper(
           (F("action__percent") * F("action__rule__duration")) / 100,
           output_field=FloatField()
       )
)

[Caio Ariede]

It looks like there's an issue with the user-defined function used by the SQLite backend to make arithmetic calculations:

​https://github.com/django/django/blob/master/django/db/backends/sqlite3/base.py#L416

It only expects microseconds, timedeltas and datetime objects. In the given example, it also would need to expect an integer (the percent). This works with the PostgreSQL backend but not with the SQLite backend.

[Baptiste Mispelon]

I was able to reproduce the reported error on the latest master (a5855c8f0fe17b7e888bd8137874ef78012a7294) by using the following models:

from django.db import models


class Ticket(models.Model):
    date_opened = models.DateTimeField()


class Rule(models.Model):
    duration = models.DurationField()


class Action(models.Model):
    percent = models.FloatField()
    rule = models.ForeignKey('Rule', on_delete=models.CASCADE)


class Expire(models.Model):
    ticket = models.ForeignKey('Ticket', on_delete=models.CASCADE)
    action = models.ForeignKey('Action', on_delete=models.CASCADE)

And the following testcase:

from datetime import timedelta

from django.db.models import F
from django.test import TestCase
from django.utils import timezone

from .models import Ticket, Rule, Action, Expire


class TicketTestCase(TestCase):
    def test_ticket(self):
        now = timezone.now()

        ticket1 = Ticket.objects.create(date_opened=now-timedelta(days=6))
        ticket2 = Ticket.objects.create(date_opened=now-timedelta(days=4))

        rule = Rule.objects.create(duration=timedelta(days=10))
        action = Action.objects.create(rule=rule, percent=50)

        expire = Expire.objects.create(ticket=ticket1, action=action)
        expire = Expire.objects.create(ticket=ticket2, action=action)


        qs = Expire.objects.filter(ticket__date_opened__lte=(timezone.now() - F("action__percent") * F("action__rule__duration") / 100))
        self.assertQuerysetEqual(qs, [ticket1.pk], transform=lambda x: x.ticket.pk)

As described in the original ticket, the testcase fails with sqlite (DatabaseError: Invalid connector for timedelta: *.) but passes when using postgresql.

The proposed solution of adding the new * and / operators to sqlite3.DatebaseOperations.combine_duration_expression() [1] only works in that the DatebaseError disappears but the test still fails because the returned queryset is empty.

Josh's suggestions of wrapping everything in an ExpressionWrapper don't seem to make a difference either.

[1] ​https://github.com/django/django/blob/a5855c8f0fe17b7e888bd8137874ef78012a7294/django/db/backends/sqlite3/operations.py#L315

[Tobias Bengfort]

I have created a patch that seems to work. I know far too little about databases though to test and finish it properly. So I hope it is helpful to someone else:

​https://github.com/xi/django/commit/240501051db0621a456101a9e599304e1be83faa

[Tobias Bengfort]

I took a stab at a fix at ​https://github.com/django/django/pull/14237

It seems like this is not only an issue for sqlite but also for mysql

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9e1ccd72:

Refs #25287 -- Added _sqlite_prepare_dtdelta_param() hook.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 54e94640:

Refs #25287 -- Added support for multiplying and dividing DurationField by scalar values on SQLite.

[Mariusz Felisiak]

MySQL part is missing.