#36408: PostgreSQL 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:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Adam Johnson:
Old description:
> Like #36404, I have also encountered some `FieldError`s for a PostgreSQL
> `ArrayAgg` that uses an `OuterRef` in its `order_by` clause.
>
> For example, 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()`.
New description:
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()`.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36408#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070196f79cc49f-ffd8d99c-e5ad-48cd-b1e4-6cbea0e07aa0-000000%40eu-central-1.amazonses.com.
