Opened 9 months ago

Last modified 5 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

Description

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(
        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
    ;

Change History (2)

comment:1 Changed 9 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 5 months ago 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?

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