#26434: Inconsistent results of QuerySet.count() when ordering is not a subset
of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michal Mládek):
Replying to [comment:15 Simon Charette]:
Using `order_by('...')` on a column that is also used implicitly for
grouping in an aggregation queryset is a misuse of the ORM AFAIK. So what
should we do about it?
Should we implement **a patch** that triggers a **warning** if
`order_by('...')` includes a column that ends up in the `annotate(...)`, I
mean `GROUP BY ...` clause? Or should we instead modify the behavior of
`queryset.count()` to preserve ordering in such edge cases?
In case of implementing a warning, this doesn't look like a bug - it would
be better treated as a feature request, I believe.
Also, the documentation already warns about this interaction quite
clearly:
https://docs.djangoproject.com/en/5.2/topics/db/aggregation/#interaction-
with-order-by — and that warning has been present since the LTS version
Django 3.2.
> Hello Simon,
>
> Thank you very much for clarifying the bug — especially for pointing out
that it’s not Postgres-specific, and also for the hint about a subset. I
think everything will go much more smoothly and quickly now.
>
> Replying to [comment:15 Simon Charette]:
> > FWIW the issue is not Postgres specific (I reproduced against SQLite).
> >
> > The problem appears to be caused by `sql.Query.get_aggregation`'s
[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L528-L531
call] to `clear_ordering(force=False)`. It seems like
[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L2313-L2316
the later should skip clearing] when `isinstance(self.group_by, tuple)`
and `self.order_by + self.extra_order_by` is not a subset of
`self.group_by` as `force=False` must only clear if doing so preserves the
semantic of the query which clearly doesn't if the ordering includes
members missing from the group by
([https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/compiler.py#L164-L169
as they would normally be added at a later stage]).
> >
> > I adjusted the ticket summary accordingly.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:17>
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/01070196d9ed9f5c-84d39206-e87a-4518-8a1a-ab6babf66ca2-000000%40eu-central-1.amazonses.com.
