[Django] #26045: Annotate with conditional aggregation includes non-relevant fields in GROUP BY clause
Django
BY clause
----------------------------------------------+----------------------------
Reporter: mark88 | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: orm, sql,
| annotation
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
I'm using Django 1.8.6 and conditional aggregates.
{{{
objects = self.get_queryset().annotate(sent_count=
Sum(
Case(When(messages__date_sent__gte=date_from, then=1)),
output_field=IntegerField()
),
error_count=
Sum(
Case(When(messages__status__iexact='error', then=1)),
output_field=IntegerField()
),
sent_yesterday=
Sum(
Case(When(messages__date_sent__gte=yesterday, then=1)),
output_field=IntegerField()
)
)
}}}
And when viewing SQL query for this annotation, I see every single field
in this model is included in GROUP BY clause, making query very slow.
There's also one field from related model included in GROUP BY. I've run
the query manually, removing all unnecessary fields from the clause and it
runs fine
--
Ticket URL: <https://code.djangoproject.com/ticket/26045>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
BY clause
Reporter: mark88 | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, sql, | Triage Stage:
annotation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Hi mark88,
I suspect this is related to #19259. Are you using PostgreSQL? Can you
also reproduce with Django 1.9+?
--
Ticket URL: <https://code.djangoproject.com/ticket/26045#comment:1>
Django
BY clause
Reporter: mark88 | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, sql, | Triage Stage:
annotation | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
MySQL and Postgres will optimise by grouping by all primary keys of each
joined table, but this optimisation may not exist for queries that include
"complex" annotations. If you don't need all of the fields from `self`,
then prepending a `values()` clause before the annotate is how you
restrict the group by.
`self.get_queryset().values('pk').annotate(...)` should do what you want,
again, if you don't need the other fields from the self model.
--
Ticket URL: <https://code.djangoproject.com/ticket/26045#comment:2>
Django
BY clause
Reporter: mark88 | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: orm, sql, | Triage Stage:
annotation | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* status: new => closed
* resolution: => needsinfo
Comment:
Mark,
I tried a building a queryset similar to yours locally and unrelated
fields were excluded from the `GROUP BY` clause on Django 1.9+ using
PostgreSQL.
If it's not the case for your query please reopen this ticket so we can
investigate what can be done.
Resolving as ''needsinfo'' until feedback is provided.
--
Ticket URL: <https://code.djangoproject.com/ticket/26045#comment:3>