[Django] #29095: count on queryset with sum window function in django 2.0 + postgres throws error
Django
error
-------------------------------------+-------------------------------------
Reporter: Shadi | Owner: nobody
Akiki |
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords: window
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Using `.count()` on the queryset from `Sum` in
[https://docs.djangoproject.com/en/2.0/ref/models/expressions/#window-
functions Django 2.0 Window] function yields the error
{{{
django.db.utils.ProgrammingError: column
"cms2_balances_base_v1.initial_balance" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 1: ...T "cms2_balances_base_v1"."pub_date" AS Col1,
SUM("cms2_bala...
}}}
Here is the exact django code
{{{
from apps.cms2_balances.models import *
from django.db.models import Avg, F, Max, Min, Window, Sum, Func
t1 = obligor.basev1_set.annotate(
cumulative_obligor=Window(
expression=Sum(F('initial_balance')),
partition_by=[F('currency'), F('obligor')],
order_by=['pub_date', 'content_type_id', 'object_id']
)
)
t1.all() # <<< no error
t1.count() # <<< yields error
}}}
The query of this queryset works when I run it in postgres directly. Here
it is
{{{
SELECT
"cms2_balances_base_v1"."obligor_id",
"cms2_balances_base_v1"."originator_id",
"cms2_balances_base_v1"."currency_id",
"cms2_balances_base_v1"."content_type_id",
"cms2_balances_base_v1"."object_id",
"cms2_balances_base_v1"."pub_date",
"cms2_balances_base_v1"."initial_balance",
"cms2_balances_base_v1"."principal_balance",
"cms2_balances_base_v1"."interest_balance",
SUM("cms2_balances_base_v1"."initial_balance")
OVER (
PARTITION BY "cms2_balances_base_v1"."currency_id",
"cms2_balances_base_v1"."obligor_id"
ORDER BY "cms2_balances_base_v1"."pub_date",
"cms2_balances_base_v1"."content_type_id",
"cms2_balances_base_v1"."object_id"
) AS "cumulative_obligor"
FROM "cms2_balances_base_v1"
WHERE "cms2_balances_base_v1"."obligor_id" = 2164
;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29095>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
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
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:1>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: window | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by holvianssi):
I can't reproduce this. I'm using Django tests expressions_window app
model, and trying with query:
{{{
Employee.objects.annotate(
cumulative_obligor=Window(
expression=Sum(F('salary')),
partition_by=[F('department')],
order_by=['hire_date']
)
).filter(pk__gte=1).count()
}}}
What's the missing ingredient to make this crash? Or could this have been
fixed subsequently?
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:2>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: window | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Closing as ''needsinfo'' as I couldn't reproduce either. Feel free to
reopen if you can provide more details.
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:3>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: window | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* resolution: needsinfo => duplicate
Comment:
Duplicate of #14357 which started deprecating `Meta.ordering` usage in
such queries in 1b1f64ee5a78cc217fead52cbae23114502cf564.
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:4>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* 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
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: window | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Unreviewed
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:5>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: window | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* version: 2.0 => 3.0
* resolution: => fixed
Comment:
Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5. Christopher, thanks for
tests.
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:6>
Django
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Shadi Akiki | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: window | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"33ec01caaf57271d1820dc0ea90c5360287e305c" 33ec01ca]:
{{{
#!CommitTicketReference repository=""
revision="33ec01caaf57271d1820dc0ea90c5360287e305c"
Refs #29095 -- Added test for using QuerySet.count() with window
expressions ordered by related fields.
Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29095#comment:7>