[Django] #36405: Aggregates with order_by using OuterRef raise FieldError

Django

unread,

May 21, 2025, 9:49:42 AM5/21/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------
Reporter: Adam Johnson | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Release
| blocker
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
While fixing #36404, I spotted the same mistake had been made for the
newly added `Aggregate.order_by` from #35444.
--
Ticket URL: <https://code.djangoproject.com/ticket/36405>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

May 21, 2025, 10:13:54 AM5/21/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------

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

Changes (by Sarah Boyce):

* cc: Chris M, Simon Charette (added)
* owner: (none) => Adam Johnson
* stage: Unreviewed => Accepted
* status: new => assigned

Comment:

Refs #35444, 4b977a5d7283e7ca51288cc0ed0860e0004653ca
--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:1>

Django

unread,

May 22, 2025, 4:42:29 AM5/22/25

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:2>

Django

unread,

May 22, 2025, 7:01:56 AM5/22/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------
Reporter: Adam Johnson | Owner: Adam
| Johnson
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2

Changes (by Adam Johnson):

* version: dev => 5.2

Old description:

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

New description:

While fixing #36404, I spotted the same mistake had been made for

`Aggregate.order_by` (on `main`, from #35444), which was previously the
PostgreSQL-specific `OrderableAggMixin.order_by` (on 5.2).

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()`.

--
--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:3>

Django

unread,

May 23, 2025, 9:37:36 AM5/23/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------
Reporter: Adam Johnson | Owner: Adam
| Johnson
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:4>

Django

unread,

May 23, 2025, 10:16:09 AM5/23/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------
Reporter: Adam Johnson | Owner: Adam
| Johnson

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"c2615a050036eda0bca090c707191076220cee9f" c2615a0]:
{{{#!CommitTicketReference repository=""
revision="c2615a050036eda0bca090c707191076220cee9f"
Fixed #36405 -- Fixed Aggregate.order_by using OuterRef.

co-authored-by: Simon Charette <chare...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:5>

Django

unread,

May 23, 2025, 10:24:08 AM5/23/25

to django-...@googlegroups.com

#36405: Aggregates with order_by using OuterRef raise FieldError
-------------------------------------+-------------------------------------
Reporter: Adam Johnson | Owner: Adam
| Johnson
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"7e4b371eb070d892e9c27eacbe140c78b7d34059" 7e4b371]:
{{{#!CommitTicketReference repository=""
revision="7e4b371eb070d892e9c27eacbe140c78b7d34059"
[5.2.x] Fixed #36405 -- Fixed OrderableAggMixin.order_by using OuterRef.

co-authored-by: Simon Charette <chare...@gmail.com>

Backport of c2615a050036eda0bca090c707191076220cee9f from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36405#comment:6>

Reply all

Reply to author

Forward