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

[Alexandru Chirila]

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)

[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

[Alexandru Chirila]

Replying to 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:

1. that issues seems be from 1.10 while this used to work ok, and has been broken somewhere between 4.2> and <=5.2
2. that issue looks to be about "SQLITE_MAX_VARIABLE_NUMBER", while the error here is about "SQLITE_MAX_EXPR_DEPTH" which defaults to 1000

[Jacob Walls]

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?

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(len(list(User.objects.prefetch_related("groups__permissions")))

In [3]: run()
In [15]: run()
22321