[Django] #34551: Case-When aggregation over aggregated fields doesn't work since 4.2

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis | Owner: nobody
Roldán |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: orm, aggregate,
Severity: Normal | case, when, field error, bug
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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!

--
Ticket URL: <https://code.djangoproject.com/ticket/34551>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#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
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => needsinfo

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:1>

[Django]

#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
-------------------------------------+-------------------------------------

Comment (by Denis Roldán):

Replying to [comment:1 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)

}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:2>

[Django]

#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:

}}}

Thanks for the support!

--

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:3>

[Django]

#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
-------------------------------------+-------------------------------------

Comment (by Denis Roldán):

You can find the test here: https://github.com/django/django/pull/16838

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:4>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: nobody

Type: Bug | Status: new

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

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
-------------------------------------+-------------------------------------

Changes (by Denis Roldán):

* status: closed => new
* resolution: needsinfo =>

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:5>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Release blocker | Resolution:
Keywords: orm, aggregate, | Triage Stage: Accepted

case, when, field error, bug |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: Simon Charette (added)
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

Comment:

Thanks for the report.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.
Reproduced at 59262c294d26d2aa9346284519545c0f988bf353.

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:6>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------

Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: orm, aggregate, | Triage Stage: Accepted
case, when, field error, bug |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: nobody => Simon Charette
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:7>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: aggregate subquery | Triage Stage: Accepted
annotation |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* keywords: orm, aggregate, case, when, field error, bug => aggregate
subquery annotation
* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:8>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:9>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4" e5c844d6]:
{{{
#!CommitTicketReference repository=""
revision="e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4"
Fixed #34551 -- Fixed QuerySet.aggregate() crash when referencing
subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Denis Roldán and Mariusz for the test.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:11>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette

Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"2ee01747c32a7275a7a1a5f7862acba7db764921" 2ee0174]:
{{{
#!CommitTicketReference repository=""
revision="2ee01747c32a7275a7a1a5f7862acba7db764921"
Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation
reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:10>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"c78a4421de0fc3240b91d59e8f9028331777c624" c78a442]:
{{{
#!CommitTicketReference repository=""
revision="c78a4421de0fc3240b91d59e8f9028331777c624"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:13>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"57f499e412c7c28b4a1f1b740468bf6eabbdb695" 57f499e4]:
{{{
#!CommitTicketReference repository=""
revision="57f499e412c7c28b4a1f1b740468bf6eabbdb695"
[4.2.x] Refs #34551 -- Fixed QuerySet.aggregate() crash on precending
aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

Backport of 2ee01747c32a7275a7a1a5f7862acba7db764921 from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:12>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Lorenzo Peña):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:14>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Lorenzo Peña):

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

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:15>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"3b4a571275d967512866012955eb0b3ae486d63c" 3b4a5712]:
{{{
#!CommitTicketReference repository=""
revision="3b4a571275d967512866012955eb0b3ae486d63c"
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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:16>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"4ccca9eedc2f453602a20f562399a835a24817c1" 4ccca9ee]:
{{{
#!CommitTicketReference repository=""
revision="4ccca9eedc2f453602a20f562399a835a24817c1"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:17>

[Django]

#34551: Case-When aggregation over aggregated fields doesn't work since 4.2
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregate subquery | Triage Stage: Ready for
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"803caec60bed3b282b9f9961860a467160c0c8f1" 803caec]:
{{{
#!CommitTicketReference repository=""
revision="803caec60bed3b282b9f9961860a467160c0c8f1"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:18>