Opened 15 months ago

Closed 14 months ago

Last modified 9 months ago

#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 Denis Roldán)

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)

comment:1 by Mariusz Felisiak, 15 months ago

Resolution: needsinfo
Status: newclosed

Can you reproduce your issue with Django 4.2.1 (see 511dc3db539122577aaba71f5a24d65d5adab092)? If yes, please share your models.

in reply to:  1 comment:2 by Denis Roldán, 15 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 Denis Roldán, 15 months ago

Description: modified (diff)

comment:4 by Denis Roldán, 15 months ago

comment:5 by Denis Roldán, 15 months ago

Resolution: needsinfo
Status: closednew

comment:6 by Mariusz Felisiak, 15 months ago

Cc: Simon Charette added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thanks for the report.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.
Reproduced at 59262c294d26d2aa9346284519545c0f988bf353.

comment:7 by Simon Charette, 15 months ago

Owner: changed from nobody to Simon Charette
Status: newassigned

comment:8 by Simon Charette, 14 months ago

Has patch: set
Keywords: subquery annotation added; orm case when field error bug removed

comment:9 by Mariusz Felisiak, 14 months ago

Triage Stage: AcceptedReady for checkin

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

In 2ee0174:

Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

Resolution: fixed
Status: assignedclosed

In e5c844d6:

Fixed #34551 -- Fixed QuerySet.aggregate() crash when referencing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Denis Roldán and Mariusz for the test.

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

In 57f499e4:

[4.2.x] Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

Backport of 2ee01747c32a7275a7a1a5f7862acba7db764921 from main

comment:13 by Mariusz Felisiak <felisiak.mariusz@…>, 14 months ago

In c78a442:

[4.2.x] Fixed #34551 -- Fixed QuerySet.aggregate() crash when referencing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Denis Roldán and Mariusz for the test.

Backport of e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4 from main

comment:14 by Lorenzo Peña, 13 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 Lorenzo Peña, 12 months ago

Cross referencing just in case: https://code.djangoproject.com/ticket/34706

comment:16 by Mariusz Felisiak <felisiak.mariusz@…>, 9 months ago

In 3b4a5712:

Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

comment:17 by Mariusz Felisiak <felisiak.mariusz@…>, 9 months ago

In 4ccca9ee:

[5.0.x] Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

Backport of 3b4a571275d967512866012955eb0b3ae486d63c from main

comment:18 by Mariusz Felisiak <felisiak.mariusz@…>, 9 months ago

In 803caec:

[4.2.x] Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

Backport of 3b4a571275d967512866012955eb0b3ae486d63c from main

Note: See TracTickets for help on using tickets.
Back to Top