id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29095	QuerySet.count() with Sum window function generates invalid SQL on PostgreSQL	Shadi Akiki	nobody	"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
    ;
}}}


"	Bug	closed	Database layer (models, ORM)	3.0	Normal	fixed	window		Unreviewed	0	0	0	0	0	0