[Django] #25287: Multiplying and dividing connectors are not supported by sqlite backed.

[Django]

#25287: Multiplying and dividing connectors are not supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) | Keywords: sqlite3,
Severity: Normal | combine_duration_expression, F
| expressions,
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/25287>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#25287: Multiplying and dividing connectors are not supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage:
combine_duration_expression, F | Unreviewed
expressions, |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by javrasya):

* 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>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner: nobody

Type: New feature | Status: new

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |

expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* 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>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner:

| caioariede
Type: New feature | Status: assigned

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by caioariede):

* status: new => assigned
* owner: nobody => caioariede

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:3>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner:

| caioariede
Type: New feature | Status: assigned

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner:

| caioariede
Type: New feature | Status: assigned

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------
Reporter: javrasya | Owner:

| caioariede
Type: New feature | Status: assigned

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: (none)
Type: New feature | Status: new

Component: Database layer | Version: 1.8
(models, ORM) |

Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Caio Ariede):

* owner: Caio Ariede => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:7>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 3.0

(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Baptiste Mispelon):

* 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.

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

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:7>

[Django]

#25287: Multiplying and dividing connectors for datetime expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: Sergey
| Fedoseev

Type: New feature | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sergey Fedoseev):

* owner: nobody => Sergey Fedoseev

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:8>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: Sergey
| Fedoseev
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: (none)

Type: New feature | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sergey Fedoseev):

* owner: Sergey Fedoseev => (none)

* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:10>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: Tobias
| Bengfort

Type: New feature | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* 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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite3, mysql, | Triage Stage: Accepted

combine_duration_expression, F |
expressions, |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tobias Bengfort):

* 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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported by sqlite backed.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |

Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_docs: 0 => 1
* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:13>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not

supported on SQLite and MySQL.

-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:14>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite3, mysql, | Triage Stage: Ready for
combine_duration_expression, F | checkin
expressions, |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* 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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Ready for
combine_duration_expression, F | checkin
expressions, |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Ready for
combine_duration_expression, F | checkin
expressions, |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: sqlite3, mysql, | Triage Stage: Accepted
combine_duration_expression, F |

expressions, |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Ready for checkin => Accepted

Comment:

MySQL part is missing.

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:18>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------
Reporter: Ahmet DAL | Owner: Tobias
| Bengfort
Type: New feature | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* has_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:19>

[Django]

#25287: Multiplying and dividing connectors for duration expressions are not
supported on SQLite and MySQL.
-------------------------------------+-------------------------------------

Reporter: Ahmet DAL | Owner: (none)
Type: New feature | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3, mysql, | Triage Stage: Accepted
combine_duration_expression, F |
expressions, |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: Tobias Bengfort => (none)

* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/25287#comment:20>