#30027: SQLite (pre 3.25.0) does not support window functions, raises
OperationalError
-------------------------------------+-------------------------------------
Reporter: Scott | Owner: nobody
Stevens |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) | Keywords: window functions,
Severity: Normal | database, sqlite
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Window functions are supported in SQLite 3.25.0 and newer, but Python
3.6.7 and 3.7.1 only ships with SQLite 3.21.0. Window function syntax is
invalid for older versions.
As per the title, window functions therefore aren't supported, but Django
doesn't check the SQLite version or availability of window functions.
Instead, when the generated SQL is executed, the `sqlite3` Python library
raises the SQLite syntax error as `sqlite3.OperationalError`, which in
turn is reraised as `django.db.utils.OperationalError`.
I believe this is not intended behaviour, as it is incredibly confusing,
and not documented. Typically, if a database feature is not supported,
Django will explicitly raise an error when attempting it, rather than
allowing the SQL execution to fail. It is also normally documented.
The following code raises an exception (it should work for any model):
{{{
from django.db.models import F, Window
from django.db.models.functions.window import RowNumber
# import the model
MyModel.objects.annotate(rn=Window(expression=RowNumber(),
order_by=[F('pk')]))
}}}
Basic Python code that will also raise `sqlite3.OperationalError`:
{{{
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT)")
c.execute("INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb')")
c.execute("SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM
t0 ORDER BY x")
}}}
Tested on master branch (commit c5568340a525ab9c6898ed02c257394cc47285d7)
with Python 3.6.6 64-bit (Windows 10 x64). This likely also affects 2.0
and 2.1 branches.
--
Ticket URL: <https://code.djangoproject.com/ticket/30027>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.9dafd27597ee5c91b458e0664f817fb2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
