[Django] #36707: Prefetch related query throwing "Expression tree is too large" error after updating to Django 5.2 when using sqlite3 DB

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
After updating to version 5.2.7 from 4.2.15 of Django some queries with
prefetch related that used to work with the sqlite3 engine no longer work
and are throwing the "Expression tree is too large" error.

-----

Example working with 4.2:

{{{
In [1]: import django

In [2]: django.__version__
Out[2]: '4.2.15'

In [3]: from dv.models import Organisation

In [4]: x =
list(Organisation.objects.all().prefetch_related("roles__project"))

In [5]: len(x)
Out[5]: 22321
}}}

----

Example working in 5.2 **without** prefetch

{{{
In [1]: import django

In [2]: django.__version__
Out[2]: '5.2.7'

In [3]: from dv.models import Organisation

In [4]: x = list(Organisation.objects.all())

In [5]: len(x)
Out[5]: 22321
}}}

---

Example of it not working with prefetch:

{{{
In [1]: import django

In [2]: django.__version__
Out[2]: '5.2.7'

In [3]: from dv.models import Organisation

In [4]: x =
list(Organisation.objects.all().prefetch_related("roles__project"))
---------------------------------------------------------------------------
OperationalError Traceback (most recent call
last)
File ~/PycharmProjects/dataviz/.venv/lib/python3.12/site-
packages/django/db/backends/utils.py:105, in CursorWrapper._execute(self,
sql, params, *ignored_wrapper_args)
104 else:
--> 105 return self.cursor.execute(sql, params)

File ~/PycharmProjects/dataviz/.venv/lib/python3.12/site-
packages/django/db/backends/sqlite3/base.py:360, in
SQLiteCursorWrapper.execute(self, query, params)
359 query = self.convert_query(query, param_names=param_names)
--> 360 return super().execute(query, params)

OperationalError: Expression tree is too large (maximum depth 1000)

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

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------

Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Alexandru Chirila):

* Attachment "full-trace.txt" added.

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Alexandru Chirila):

* Attachment "full-trace.2.txt" added.

Full stack trace of the error

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Alexandru Chirila):

* Attachment "full-trace.2.txt" removed.

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)

Type: Bug | Status: closed

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

Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* resolution: => duplicate
* status: new => closed

Comment:

Thanks for the report. Duplicate of #27833. You may want to update your
sqlite version to take advantage of higher limits as of 3.32.0
--
Ticket URL: <https://code.djangoproject.com/ticket/36707#comment:1>

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Alexandru Chirila):

Replying to [comment:1 Jacob Walls]:

> Thanks for the report. Duplicate of #27833. You may want to update your
sqlite version to take advantage of higher limits as of 3.32.0

Thanks for the reply! I'm already on 3.37, so that doesn't help.

But it does looks like the issue is separate from #27833 because:

a. that issues seems be from 1.10 while this used to work ok, and has been
broken somewhere between 4.2> and <=5.2
b. that issue looks to be about "SQLITE_MAX_VARIABLE_NUMBER", while the
error here is about "SQLITE_MAX_EXPR_DEPTH" which defaults to 1000
--
Ticket URL: <https://code.djangoproject.com/ticket/36707#comment:2>

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2

(models, ORM) | Resolution:
Severity: Normal | worksforme

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* resolution: duplicate => worksforme

Comment:

I saw that you mentioned suspecting a regression, but you haven't provided
information to reproduce. If you could git bisect that would help advance
the triage.

I put together a test, and it works for me. Could you provide your models?

{{{#!py
from django.contrib.auth.models import *


def run():
perm = Permission.objects.first()
group = Group.objects.create(name="New Group")
group.permissions.add(perm)
users = [User(pk=i, username=str(i)) for i in range(22321)]
users = User.objects.bulk_create(users, batch_size=5000)
group.user_set.add(*users)
print(User.objects.prefetch_related("groups__permissions"))
}}}
{{{#!py
In [3]: run()
<QuerySet [<User: 0>, <User: 1>, <User: 2>, <User: 3>, <User: 4>, <User:
5>, <User: 6>, <User: 7>, <User: 8>, <User: 9>, <User: 10>, <User: 11>,
<User: 12>, <User: 13>, <User: 14>, <User: 15>, <User: 16>, <User: 17>,
<User: 18>, <User: 19>, '...(remaining elements truncated)...']>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36707#comment:3>

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Alexandru Chirila):

FWIW, I've tested the issue with the lastest development HEAD and the
6.0b1 release and it is fixed now. So this appears out-of-date, and
nothing to do here IMO, I don't think this is an issue that deserves to
have a fix backported to previous version.

I've also done the bisection and identified the following:

-
https://github.com/django/django/commit/9861e8654701bf8b95b2b533034e78058eee71b4
first commit where the issue start occuring
-
https://github.com/django/django/commit/4a3ad9eebbc16ce80b348644b557c84ecc741be7
where the issue no longer occurs
--
Ticket URL: <https://code.djangoproject.com/ticket/36707#comment:4>

[Django]

#36707: Prefetch related query throwing "Expression tree is too large" error after
updating to Django 5.2 when using sqlite3 DB
-------------------------------------+-------------------------------------
Reporter: Alexandru Chirila | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

Thanks for sharing the bisect, that's very helpful!
--
Ticket URL: <https://code.djangoproject.com/ticket/36707#comment:5>