Opened 6 years ago

Closed 6 years ago

#30027 closed Bug (fixed)

SQLite (pre 3.25.0) does not support window functions, raises OperationalError

Reported by: Scott Stevens Owned by: nobody
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords: window functions, database, sqlite
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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.

Change History (5)

comment:1 by Simon Charette, 6 years ago

Triage Stage: UnreviewedAccepted
Version: master2.0

It looks like the supports_over_clause feature flag is only checked by connection.ops.window_frame_rows_start_end which is not exercised when no frame expressions are exercised.

Window.as_sql should simply check supports_over_clause and raise NotSupported if it isn't.

comment:2 by Simon Charette, 6 years ago

Has patch: set
Version 0, edited 6 years ago by Simon Charette (next)

comment:3 by Tim Graham, 6 years ago

Patch needs improvement: set

Currently blocked on SQLite crashing in a test. If the next version of SQLite doesn't fix it, we could consider skipping that test for now.

comment:4 by Tim Graham <timograham@…>, 6 years ago

In ebd2fe18:

Refs #30027 -- Enabled window function tests on SQLite 3.25+.

comment:5 by Tim Graham <timograham@…>, 6 years ago

Resolution: fixed
Status: newclosed

In 64d5baf:

Fixed #30027 -- Errored out on Window function usage if unsupported.

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