Opened 12 months ago

Last modified 8 months ago

#29095 new Bug

QuerySet.count() with Sum window function generates invalid SQL on PostgreSQL

Reported by: Shadi Akiki Owned by: nobody
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords: window
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Using .count() on the queryset from Sum in 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(
            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

            OVER (
                PARTITION BY "cms2_balances_base_v1"."currency_id",
                ORDER BY "cms2_balances_base_v1"."pub_date",
            ) AS "cumulative_obligor"
    FROM "cms2_balances_base_v1"
    WHERE "cms2_balances_base_v1"."obligor_id" = 2164

Change History (2)

comment:1 Changed 12 months ago by Tim Graham

Summary: count on queryset with sum window function in django 2.0 + postgres throws errorQuerySet.count() with Sum window function generates invalid SQL on PostgreSQL
Triage Stage: UnreviewedAccepted

comment:2 Changed 8 months ago by holvianssi

I can't reproduce this. I'm using Django tests expressions_window app model, and trying with query:


What's the missing ingredient to make this crash? Or could this have been fixed subsequently?

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