#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: orm, aggregate, | Triage Stage:
case, when, field error, bug | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Denis Roldán:
Old description:
> This same QuerySet was working on 4.1.X and prior versions and stopped
> working on 4.2.X:
>
> {{{
> annotated_users = users_qs.annotate(
> total_things=Subquery(
> OtherModel.objects.filter(user_id=OuterRef("pk")).annotate(
> total_objs=F("total")
> ).values("total_objs")
> )
> )
>
> annotated_users.aggregate(
> sum_total_objs=Sum("total_things"),
> avg_conversion_rate=Case(
> When(
> sum_total_objs=0,
> then=0,
> ),
> default=Round(
> (Sum("sum_total_confirmed_objs") /
> Sum("sum_total_objs")) * 100, 2
> ),
> output_field=FloatField(),
> )
> )
> }}}
>
> As you can see `sum_total_objs` is an aggregated field that is also used
> on a second field to calculate the conversion rate. To avoid a zero
> division problem, we were using a Case-When clause over that field. It
> works well on any 4.1 and prior versions but stopped working since 4.2,
> raising a `FieldError` like:
>
> `Cannot resolve keyword 'sum_total_objs' into field`
>
> Thanks for the support!
New description:
This same QuerySet was working on 4.1.X and prior versions and stopped
working on 4.2.X:
{{{
annotated_users = users_qs.annotate(
total_things=Subquery(
OtherModel.objects.filter(user_id=OuterRef("pk")).annotate(
total_objs=F("total")
).values("total_objs")
)
)
annotated_users.aggregate(
sum_total_objs=Sum("total_things"),
avg_conversion_rate=Case(
When(
sum_total_objs=0,
then=0,
),
default=Round(
(Sum("sum_total_confirmed_objs") /
Sum("sum_total_objs")) * 100, 2
),
output_field=FloatField(),
)
)
}}}
As you can see `sum_total_objs` is an aggregated field that is also used
on a second field to calculate the conversion rate. To avoid a zero
division problem, we were using a Case-When clause over that field. It
works well on any 4.1 and prior versions but stopped working since 4.2,
raising a `FieldError` like:
`Cannot resolve keyword 'sum_total_objs' into field`
The bug is reproducible with an extra test on the django aggregation test
suite:
{{{
def test_referenced_group_by_aggregation_over_annotation(self):
total_books_qs = (
Book.objects.filter(authors__pk=OuterRef("pk"))
.order_by()
.values("pk")
.annotate(total=Count("pk"))
.values("total")
)
annotated_authors = Author.objects.annotate(
total_books=Subquery(total_books_qs.annotate(
total_books=F("total")
).values("total_books")),
total_books_a=Subquery(total_books_qs.filter(
name__istartswith="a"
).annotate(
total_books_a=F("total")
).values("total_books_a")),
).values(
"pk",
"total_books",
"total_books_a",
).order_by("-total_books")
totals = annotated_authors.aggregate(
sum_total_books=Sum("total_books"),
sum_total_books_a=Sum("total_books_a"),
a_over_total_rate=Case(
When(
sum_total_books=0,
then=0,
),
default=Round(
(Sum("total_books_a") / Sum("total_books")) * 100, 2
),
output_field=FloatField(),
),
)
self.assertEqual(totals['sum_total_books'], 3)
self.assertEqual(totals['sum_total_books_a'], 0)
self.assertEqual(totals['a_over_total_rate'], 0)
}}}
Thanks for the support!
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:3>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/01070188001728b6-9bcb5d7e-5cd9-4c1f-ad98-1ff866a01c23-000000%40eu-central-1.amazonses.com.
