id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36408	PostgreSQL aggregates with order_by using OuterRef raise FieldError	Adam Johnson	Adam Johnson	"Like #36404, I have also encountered some `FieldError`s for a PostgreSQL `ArrayAgg` that uses an `OuterRef` in its `order_by` clause.

The issue is reproduced ​in [https://github.com/adamchainz/django-ticket-36408 this example project], with models:

{{{#!python
from django.db import models


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


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

And `QuerySet`:

{{{#!python
from django.contrib.postgres.aggregates import ArrayAgg
from django.db.models import OuterRef, Subquery

from example.models import Book, Chapter

Book.objects.annotate(
    chapter_ids=Subquery(
        Chapter.objects.annotate(
            ids=ArrayAgg(
                ""id"",
                order_by=[OuterRef(""position"")],
            )
        ).values(""ids"")[:1]
    )
)
}}}

This error occurs:

{{{
$ python t.py
Traceback (most recent call last):
    File ""/.../t.py"", line 15, in <module>
    Chapter.objects.annotate(
    ~~~~~~~~~~~~~~~~~~~~~~~~^
        ids=ArrayAgg(
        ^^^^^^^^^^^^^
    ...<2 lines>...
        )
        ^
    ).values(""ids"")[:1]
    ^
    File ""/.../django/db/models/manager.py"", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
    File ""/.../django/db/models/query.py"", line 1647, in annotate
    return self._annotate(args, kwargs, select=True)
            ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File ""/.../django/db/models/query.py"", line 1699, in _annotate
    clone.query.add_annotation(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        annotation,
        ^^^^^^^^^^^
        alias,
        ^^^^^^
        select=select,
        ^^^^^^^^^^^^^^
    )
    ^
    File ""/.../django/db/models/sql/query.py"", line 1218, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None)
    File ""/.../django/contrib/postgres/aggregates/mixins.py"", line 33, in resolve_expression
    return super().resolve_expression(*args, **kwargs)
            ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^
    File ""/.../django/db/models/aggregates.py"", line 63, in resolve_expression
    c = super().resolve_expression(query, allow_joins, reuse, summarize)
    File ""/.../django/db/models/expressions.py"", line 300, in resolve_expression
    expr.resolve_expression(query, allow_joins, reuse, summarize)
    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File ""/.../django/db/models/expressions.py"", line 300, in resolve_expression
    expr.resolve_expression(query, allow_joins, reuse, summarize)
    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File ""/.../django/db/models/expressions.py"", line 941, in resolve_expression
    col = super().resolve_expression(*args, **kwargs)
    File ""/.../django/db/models/expressions.py"", line 902, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize)
            ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File ""/.../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/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/db/models/sql/query.py"", line 1805, in names_to_path
    raise FieldError(
    ...<2 lines>...
    )
django.core.exceptions.FieldError: Cannot resolve keyword 'position' into field. Choices are: book, book_id, id
}}}

This error again bisects to e306687a3a5507d59365ba9bf545010e5fd4b2a8. The cause is similar: duplicate `OuterRef` resolution occurs due to a clause left in `OrderableAggMixin.resolve_expression()`.
"	Bug	closed	Database layer (models, ORM)	5.2	Release blocker	duplicate			Unreviewed	0	0	0	0	0	0