[Django] #34717: Cannot use aggregate over window functions since 4.2
Django
-----------------------------------------+------------------------
Reporter: younes-chaoui | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
After upgrading to Django 4.2, I encountered an exception when executing
ORM queries that involve aggregates over Window functions. The specific
error was `psycopg2.errors.GroupingError: aggregate function calls cannot
contain window function calls`
Dependencies :
psycopg2 version: 2.9.3
django version: 4.1.9
PostgreSQL version: 13.4
Example Code:
{{{
queryset = queryset.annotate(
cumul_DJR=Coalesce(Window(Sum("DJR"), order_by=F("date").asc()), 0.0)
)
aggregate = queryset.aggregate(
DJR_total=Sum("DJR"),
cumul_DJR_total=Sum("cumul_DJR")
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34717>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: younes-chaoui | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.2
Severity: Normal | Resolution:
Keywords: aggregation, window | Triage Stage:
functions, psycopg | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by younes-chaoui):
* keywords: => aggregation, window functions, psycopg
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: nobody
Component: Uncategorized | Version: 4.2
Severity: Normal | Resolution: needsinfo
Keywords: aggregation, window | Triage Stage:
functions, psycopg | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Simon Charette (added)
* status: new => closed
* resolution: => needsinfo
Comment:
Hello! Could you please provide a minimal Django test project with models
to reproduce this issue? Or a regression test that would pass on Django
4.1 but fail in 4.2? Thank you!
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: aggregation, window | Triage Stage: Accepted
functions, psycopg |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* severity: Normal => Release blocker
* component: Uncategorized => Database layer (models, ORM)
* has_patch: 0 => 1
* resolution: needsinfo =>
* stage: Unreviewed => Accepted
Comment:
Confirmed this is a regression similar to #34551 but for window
expressions.
@younes-chaoui could you confirm the following patch addresses our issue
[PR](https://github.com/django/django/pull/17084)
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:3>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: aggregation, window | Triage Stage: Accepted
functions, psycopg |
Has patch: 1 | 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/34717#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
functions, psycopg | 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/34717#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: aggregation, window | Triage Stage: Ready for
functions, psycopg | 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:"68912e4f6f84f21322f92a2c7b6c77f68f91b9c9" 68912e4]:
{{{
#!CommitTicketReference repository=""
revision="68912e4f6f84f21322f92a2c7b6c77f68f91b9c9"
Fixed #34717 -- Fixed QuerySet.aggregate() crash when referencing window
functions.
Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Refs #28477.
Thanks younes-chaoui for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregation, window | Triage Stage: Ready for
functions, psycopg | 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:"7a67b065d7e5653f3af1cbd28882d33d2a088b02" 7a67b065]:
{{{
#!CommitTicketReference repository=""
revision="7a67b065d7e5653f3af1cbd28882d33d2a088b02"
[4.2.x] Fixed #34717 -- Fixed QuerySet.aggregate() crash when referencing
window functions.
Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Refs #28477.
Thanks younes-chaoui for the report.
Backport of 68912e4f6f84f21322f92a2c7b6c77f68f91b9c9 from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregation, window | Triage Stage: Ready for
functions, psycopg | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> After upgrading to Django 4.2, I encountered an exception when executing
> ORM queries that involve aggregates over Window functions. The specific
> error was `psycopg2.errors.GroupingError: aggregate function calls cannot
> contain window function calls`
>
> Dependencies :
>
> psycopg2 version: 2.9.3
> django version: 4.1.9
> PostgreSQL version: 13.4
>
> Example Code:
>
>
> {{{
> queryset = queryset.annotate(
> cumul_DJR=Coalesce(Window(Sum("DJR"), order_by=F("date").asc()), 0.0)
> )
>
> aggregate = queryset.aggregate(
> DJR_total=Sum("DJR"),
> cumul_DJR_total=Sum("cumul_DJR")
> )
> }}}
New description:
After upgrading to Django 4.2, I encountered an exception when executing
ORM queries that involve aggregates over Window functions. The specific
error was `psycopg2.errors.GroupingError: aggregate function calls cannot
contain window function calls`
Dependencies :
psycopg2 version: 2.9.3
django version: 4.2.3
PostgreSQL version: 13.4
Example Code:
{{{
queryset = queryset.annotate(
cumul_DJR=Coalesce(Window(Sum("DJR"), order_by=F("date").asc()), 0.0)
)
aggregate = queryset.aggregate(
DJR_total=Sum("DJR"),
cumul_DJR_total=Sum("cumul_DJR")
)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregation, window | Triage Stage: Ready for
functions, psycopg | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Younes, it is expected that the issue isn't fixed in Django 4.2.3 (it was
released on July 3rd which is 14 days before your report).
The issue will be fixed in the upcoming 4.2.4 release
[https://docs.djangoproject.com/en/dev/releases/4.2.4/ which is scheduled
for August 1st].
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: younes-chaoui | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: aggregation, window | Triage Stage: Ready for
functions, psycopg | 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:"d7a9f006ed6d6323d3d66e75354202f4d1174ea0" d7a9f006]:
{{{
#!CommitTicketReference repository=""
revision="d7a9f006ed6d6323d3d66e75354202f4d1174ea0"
Refs #34717 -- Avoided computing aggregate refs twice.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34717#comment:10>