[Django] #34551: Case-When aggregation over aggregated fields doesn't work since 4.2
Django
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
* 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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
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
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: orm, aggregate, | Triage Stage: Accepted
case, when, field error, bug |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* 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
-------------------------------------+-------------------------------------
| 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
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
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
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
annotation | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/34551#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
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
-------------------------------------+-------------------------------------
* 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
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
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
-------------------------------------+-------------------------------------
Reporter: Denis Roldán | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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>