Aggregates with filter using OuterRef raise FieldError

[Adam Johnson]

While upgrading a client project to Django 5.2, I encountered some FieldErrors like:

FieldError: Cannot resolve keyword 'isbn' into field. Choices are: book, book_id, id, isbn_ref

The issue is reproduced ​in this example project, with models:

from django.db import models


class Book(models.Model):
    isbn = models.IntegerField()


class Chapter(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE)
    isbn_ref = models.IntegerField()

And QuerySet:

from django.db.models import Count, OuterRef, Q, Subquery

from example.models import Book, Chapter

qs = Book.objects.annotate(
    point_count=Subquery(
        Chapter.objects.annotate(
            count=Count(
                "id",
                filter=Q(isbn_ref=OuterRef("isbn")),
            )
        ).values("count")
    )
)

Full traceback:

$ python t.py
Traceback (most recent call last):
    File "/.../t.py", line 14, in <module>
    Chapter.objects.annotate(
    ~~~~~~~~~~~~~~~~~~~~~~~~^
        count=Count(
        ^^^^^^^^^^^^
    ...<2 lines>...
        )
        ^
    ).values("count")
    ^
    File "/.../django/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/query.py", line 1643, in annotate
    return self._annotate(args, kwargs, select=True)
            ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/query.py", line 1695, in _annotate
    clone.query.add_annotation(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        annotation,
        ^^^^^^^^^^^
        alias,
        ^^^^^^
        select=select,
        ^^^^^^^^^^^^^^
    )
    ^
    File "/.../django/django/db/models/sql/query.py", line 1218, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None)
    File "/.../django/django/db/models/aggregates.py", line 271, in resolve_expression
    result = super().resolve_expression(*args, **kwargs)
    File "/.../django/django/db/models/aggregates.py", line 124, in resolve_expression
    c.filter.resolve_expression(query, allow_joins, reuse, summarize)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/expressions.py", line 300, in resolve_expression
    expr.resolve_expression(query, allow_joins, reuse, summarize)
    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/sql/where.py", line 288, in resolve_expression
    clone._resolve_node(clone, *args, **kwargs)
    ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/sql/where.py", line 280, in _resolve_node
    cls._resolve_node(child, query, *args, **kwargs)
    ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/sql/where.py", line 284, in _resolve_node
    node.rhs = cls._resolve_leaf(node.rhs, query, *args, **kwargs)
                ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/sql/where.py", line 273, in _resolve_leaf
    expr = expr.resolve_expression(query, *args, **kwargs)
    File "/.../django/django/db/models/expressions.py", line 958, in resolve_expression
    col = super().resolve_expression(*args, **kwargs)
    File "/.../django/django/db/models/expressions.py", line 902, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
            ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File "/.../django/django/db/models/sql/query.py", line 2049, in resolve_ref
    join_info = self.setup_joins(
        field_list, self.get_meta(), self.get_initial_alias(), can_reuse=reuse
    )
    File "/.../django/django/db/models/sql/query.py", line 1900, in setup_joins
    path, final_field, targets, rest = self.names_to_path(
                                        ~~~~~~~~~~~~~~~~~~^
        names[:pivot],
        ^^^^^^^^^^^^^^
    ...<2 lines>...
        fail_on_missing=True,
        ^^^^^^^^^^^^^^^^^^^^^
    )
    ^
    File "/.../django/django/db/models/sql/query.py", line 1805, in names_to_path
    raise FieldError(
    ...<2 lines>...
    )
django.core.exceptions.FieldError: Cannot resolve keyword 'isbn' into field. Choices are: book, book_id, id, isbn_ref

The error occurs because the isbn field is looked up against the inner model, Chapter, rather than the outer one, Book.

I bisected the error to e306687a3a5507d59365ba9bf545010e5fd4b2a8. That commit referenced #36042 and was part of a PR for #36117. The simplification left a duplicated call to resolve_expression() for Aggregate.filter, leading to “over resolution” and triggering the FieldError. The issue is similar to #36117, which removed some duplicate resolve_expression() methods from Case and When.

While fixing this issue, I spotted the same mistake had been made for the newly added Aggregate.order_by from #35444, which the PR associated to this ticket fixes in a second commit. The first commit should be backported to 5.2, the second left only on main.

[Sarah Boyce]

Thank you I've replicated

While fixing this issue, I spotted the same mistake had been made for the newly added Aggregate.order_by from #35444

For admin purposes, I think it might make sense to open a separate ticket for this as a release blocker for "dev" with a different regression commit etc
The one for 5.2 would require a release note

[Adam Johnson]

Alright, I made a separate ticket for order_by in #36405.

[Sarah Boyce <42296566+sarahboyce@…>]

In b8e5a8a:

Fixed #36404 -- Fixed Aggregate.filter using OuterRef.

Regression in a76035e925ff4e6d8676c65cb135c74b993b1039.
Thank you to Simon Charette for the review.

co-authored-by: Simon Charette <charette.s@…>

[Sarah Boyce <42296566+sarahboyce@…>]

In c29e3092:

[5.2.x] Fixed #36404 -- Fixed Aggregate.filter using OuterRef.

Regression in a76035e925ff4e6d8676c65cb135c74b993b1039.
Thank you to Simon Charette for the review.

co-authored-by: Simon Charette <charette.s@…>

Backport of b8e5a8a9a2a767f584cbe89a878a42363706f939 from main.