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 should be fixed simply.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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 should be fixed simply.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:1>
* type: Bug => New feature
* stage: Unreviewed => Accepted
Comment:
Feel free to submit a tested patch if you are able.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:2>
* status: new => assigned
* owner: nobody => caioariede
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:3>
Comment (by jarshwah):
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 )`.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:4>
Comment (by jarshwah):
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()
)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:5>
Comment (by caioariede):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:6>
* owner: Caio Ariede => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:7>
* version: 1.8 => 3.0
Comment:
I was able to reproduce the reported error on the latest master
(a5855c8f0fe17b7e888bd8137874ef78012a7294) by using the following models:
{{{
#!python
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:
{{{
#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:7>
* owner: nobody => Sergey Fedoseev
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:8>
Comment (by 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
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:9>
* owner: Sergey Fedoseev => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:10>
* owner: (none) => Tobias Bengfort
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:11>
* keywords: sqlite3, combine_duration_expression, F expressions, =>
sqlite3, mysql, combine_duration_expression, F expressions,
Comment:
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
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:12>
* needs_docs: 0 => 1
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:13>
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:14>
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
* needs_tests: 1 => 0
* needs_docs: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:15>
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"9e1ccd7283e8544d86cba35c820a7d741f5d2712" 9e1ccd72]:
{{{
#!CommitTicketReference repository=""
revision="9e1ccd7283e8544d86cba35c820a7d741f5d2712"
Refs #25287 -- Added _sqlite_prepare_dtdelta_param() hook.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:16>
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"54e94640ace261b14cf8cdb1fae3dc6f068a5f87" 54e94640]:
{{{
#!CommitTicketReference repository=""
revision="54e94640ace261b14cf8cdb1fae3dc6f068a5f87"
Refs #25287 -- Added support for multiplying and dividing DurationField by
scalar values on SQLite.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:17>
* stage: Ready for checkin => Accepted
Comment:
MySQL part is missing.
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:18>
* has_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:19>
* owner: Tobias Bengfort => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:20>