Opened 5 years ago

Last modified 6 months ago

#25287 new New feature

Multiplying and dividing connectors for datetime expressions are not supported by sqlite backed.

Reported by: Ahmet DAL Owned by: nobody
Component: Database layer (models, ORM) Version: 3.0
Severity: Normal Keywords: sqlite3, combine_duration_expression, F expressions,
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (6)

comment:1 Changed 5 years ago by Ahmet DAL

Description: modified (diff)

comment:2 Changed 5 years ago by Tim Graham

Summary: Multiplying and dividing connectors are not supported by sqlite backed.Multiplying and dividing connectors for datetime expressions are not supported by sqlite backed.
Triage Stage: UnreviewedAccepted
Type: BugNew feature

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

comment:4 Changed 5 years ago by 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 ).

comment:5 Changed 5 years ago by 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()
       )
)

comment:6 Changed 5 years ago by 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.

comment:7 Changed 6 months ago by Baptiste Mispelon

Version: 1.83.0

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

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