#34551 closed Bug (fixed)
Case-When aggregation over aggregated fields doesn't work since 4.2
Reported by: | Denis Roldán | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Release blocker | Keywords: | aggregate subquery annotation |
Cc: | Simon Charette | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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!
Change History (18)
follow-up: 2 comment:1 by , 17 months ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
comment:2 by , 17 months ago
Replying to Mariusz Felisiak:
Can you reproduce your issue with Django 4.2.1 (see 511dc3db539122577aaba71f5a24d65d5adab092)? If yes, please share your models.
Correct. It doesn't work on Django 4.2.1 neither.
I can reproduce the issue with a test on aggregation/tests.py
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)
comment:3 by , 17 months ago
Description: | modified (diff) |
---|
comment:4 by , 17 months ago
You can find the test here: https://github.com/django/django/pull/16838
comment:5 by , 17 months ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
comment:6 by , 17 months ago
Cc: | added |
---|---|
Severity: | Normal → Release blocker |
Triage Stage: | Unreviewed → Accepted |
Thanks for the report.
Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.
Reproduced at 59262c294d26d2aa9346284519545c0f988bf353.
comment:7 by , 17 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 17 months ago
Has patch: | set |
---|---|
Keywords: | subquery annotation added; orm case when field error bug removed |
comment:9 by , 17 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:14 by , 15 months ago
Good day. Just a heads up. Some aggregations still break on Django 4.2.2 in my company's codebase. I am trying to create a reproducible example into a new ticket, but wanted to drop word already.
comment:15 by , 15 months ago
Cross referencing just in case: https://code.djangoproject.com/ticket/34706
Can you reproduce your issue with Django 4.2.1 (see 511dc3db539122577aaba71f5a24d65d5adab092)? If yes, please share your models.