Opened 4 years ago

Closed 4 years ago

#32126 closed Bug (fixed)

Empty Case() annotation crashes when used with GROUP BY

Reported by: Hannes Ljungberg Owned by: Hannes Ljungberg
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Annotating a queryset with a Case without any cases will generate an invalid query when used with a GROUP BY.

Example:

qs = (            
    CaseTestModel.objects.values('string')
    .annotate(test=Case(output_field=IntegerField()), integer_sum=Sum('integer'))
    .order_by('string')
)

Executing this queryset generates the following SQL:

SELECT "expressions_case_casetestmodel"."string", NULL AS "test", SUM("expressions_case_casetestmodel"."integer") AS "integer_sum" FROM "expressions_case_casetestmodel" GROUP BY "expressions_case_casetestmodel"."string", NULL ORDER BY "expressions_case_casetestmodel"."string" ASC

Causing the following error:

django.db.utils.ProgrammingError: non-integer constant in GROUP BY
LINE 1: ...OUP BY "expressions_case_casetestmodel"."string", NULL ORDER...

The Case annotation should be excluded from the GROUP BY if it contains no cases.

Change History (4)

comment:1 by Hannes Ljungberg, 4 years ago

Has patch: set

PR: ​https://github.com/django/django/pull/13576

comment:2 by Mariusz Felisiak, 4 years ago

Triage Stage: Unreviewed → Accepted

comment:3 by Simon Charette, 4 years ago

Triage Stage: Accepted → Ready for checkin

Patch LGTM assuming it passes the suite on Oracle.

comment:4 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: → fixed
Status: assigned → closed

In 0e7a45fc:

Fixed #32126 -- Fixed grouping by Case() annotation without cases.

Co-authored-by: Simon Charette <charettes@…>

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