Opened 8 months ago

Closed 8 months ago

Last modified 3 months ago

#34717 closed Bug (fixed)

Cannot use aggregate over window functions since 4.2

Reported by: younes-chaoui Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: aggregation, window functions, psycopg
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 younes-chaoui)

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")
)

Change History (10)

comment:1 by younes-chaoui, 8 months ago

Keywords: aggregation window functions psycopg added
Type: UncategorizedBug

comment:2 by Natalia Bidart, 8 months ago

Cc: Simon Charette added
Resolution: needsinfo
Status: newclosed

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!

comment:3 by Simon Charette, 8 months ago

Component: UncategorizedDatabase layer (models, ORM)
Has patch: set
Resolution: needsinfo
Severity: NormalRelease blocker
Status: closednew
Triage Stage: UnreviewedAccepted

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)

comment:4 by Mariusz Felisiak, 8 months ago

Owner: changed from nobody to Simon Charette
Status: newassigned

comment:5 by Mariusz Felisiak, 8 months ago

Triage Stage: AcceptedReady for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 8 months ago

Resolution: fixed
Status: assignedclosed

In 68912e4:

Fixed #34717 -- Fixed QuerySet.aggregate() crash when referencing window functions.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks younes-chaoui for the report.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 8 months ago

In 7a67b065:

[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

comment:8 by younes-chaoui, 7 months ago

Description: modified (diff)

comment:9 by Simon Charette, 7 months ago

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 which is scheduled for August 1st.

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

In d7a9f006:

Refs #34717 -- Avoided computing aggregate refs twice.

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