Opened 4 years ago
Last modified 4 years ago
#32463 closed Bug
'Sum' aggregate function not working with Window function for sqlite3 version 3.27.2 — at Initial Version
| Reported by: | blackhorsesacher | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 3.1 |
| Severity: | Normal | Keywords: | |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
'Sum' aggregate function not working with Window function for sqlite3 version 3.27.2
Produces these errors:
sqlite3.OperationalError: near "OVER": syntax error
django.db.utils.OperationalError: near "OVER": syntax error
This is similar to the fixed issue #30027 "SQLite (pre 3.25.0) does not support window functions, raises OperationalError", but I am using SQLite 3.27.2 and the native SQL query is working from sqlite3 command line and also when using Model.objects.raw(), which I'm having to use as a workaround.
It should be reproducible as follows, assuming model "myapp.mymodel" has a field "amount":
from django.db.models import Sum, Window
from myapp.models import mymodel
mymodel.objects.all().annotate(cumsum=Window(expression=Sum('amount')))
The raw query works OK:
rawqs=mymodel.objects.raw("SELECT *, SUM (amount) OVER (ORDER BY id) AS cumsum FROM myapp_mymodel")