Opened 2 years ago

Last modified 2 years ago

#25287 assigned New feature

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

Reported by: Ahmet DAL Owned by: Caio Ariede
Component: Database layer (models, ORM) Version: 1.8
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 2 years ago by Ahmet DAL

Description: modified (diff)

comment:2 Changed 2 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:3 Changed 2 years ago by Caio Ariede

Owner: changed from nobody to Caio Ariede
Status: newassigned

comment:4 Changed 2 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 2 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 2 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.

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