#29095: QuerySet.count() with Sum window function generates invalid SQL on
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: window | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by clickonchris):
* status: closed => new
* resolution: needsinfo =>
Comment:
Hi There! I ran across this old bug and have some additional information
to share. In my case the "missing ingredient" is when you join to
another table and then include a column from the join table in the window
function's ORDER BY clause.
I created a test to show the problem here:
https://github.com/clickonchris/django/commit/29feb51531ac4d1a65a40003d609295b3d88c001
The .count() function generates SQL as below. I've included a code
comment above the problematic GROUP BY at the end
{{{
SELECT COUNT(*) FROM (
SELECT
"expressions_window_employee"."id" AS Col1,
SUM("expressions_window_employee"."salary")
OVER (PARTITION BY "expressions_window_employee"."department" ORDER BY
"expressions_window_employee"."hire_date",
"expressions_window_employeeclassification"."description") AS
"department_sum"
FROM "expressions_window_employee" INNER JOIN
"expressions_window_employeeclassification" ON
("expressions_window_employee"."classification_id" =
"expressions_window_employeeclassification"."code")
-- This GROUP BY seems unnecessary
GROUP BY "expressions_window_employee"."id"
) subquery
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/072.9e96c4d5384a52c5bc10810548946339%40djangoproject.com.
