Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#32200 closed Bug (fixed)

Aggregating when grouping on an ExpressionWrapper omits the expression from the group by

Reported by: Gordon Wrigley Owned by: Hasan Ramezani
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: gordon.wrigley@…, Simon Charette, Thodoris Sotiropoulos 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

I ran into this with Postgres on Django 3.1.3, I'm not sure what other versions it exists on.

print(
    Fred.objects.annotate(
        bob_id__is_null=ExpressionWrapper(
            Q(bob_id=None), 
            output_field=BooleanField()
        )
    ).values(
        "bob_id__is_null"
    ).annotate(
        id__count=Count("id", distinct=True)
    ).values(
        "bob_id__is_null", 
        "id__count"
    ).query
)
SELECT 
    "main_fred"."bob_id" IS NULL AS "bob_id__is_null", 
    COUNT(DISTINCT "main_fred"."id") AS "id__count" 
FROM "main_fred"
GROUP BY "main_fred"."bob_id"

On the last line there the group by has dropped the "IS NULL"

Change History (11)

comment:1 by Gordon Wrigley, 3 years ago

For anyone else who ends up here you can Subquery your way out of this, although I've no idea what the performance implications of that are.

comment:2 by Gordon Wrigley, 3 years ago

I did some further digging and this only occurs on 3.1, it works fine on 2.0, 2.1, 2.2 and 3.0

comment:3 by Gordon Wrigley, 3 years ago

I think this is related to https://code.djangoproject.com/ticket/32007

comment:4 by Gordon Wrigley, 3 years ago

To test this, given the implied model above, you can create 3 Fred objects, 2 with one value for bob_id and the third with a different value.
When you do the select on that you should see [{"bob_id__is_null": False, "id_count": 3}]
But instead you will get [{"bob_id__is_null": False, "id_count": 1}, {"bob_id__is_null": False, "id_count": 2}]

comment:5 by Mariusz Felisiak, 3 years ago

Component: UncategorizedDatabase layer (models, ORM)
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:6 by Mariusz Felisiak, 3 years ago

Cc: Simon Charette Thodoris Sotiropoulos added

comment:7 by Hasan Ramezani, 3 years ago

Has patch: set
Owner: changed from nobody to Hasan Ramezani
Status: newassigned

comment:8 by Mariusz Felisiak, 3 years ago

Needs documentation: set
Patch needs improvement: set

comment:9 by Mariusz Felisiak, 3 years ago

Needs documentation: unset
Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

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

Resolution: fixed
Status: assignedclosed

In fe9c7ded:

Fixed #32200 -- Fixed grouping by ExpressionWrapper() with Q objects.

Thanks Gordon Wrigley for the report.

Regression in df32fd42b84cc6dbba173201f244491b0d154a63.

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

In 166c0d24:

[3.1.x] Fixed #32200 -- Fixed grouping by ExpressionWrapper() with Q objects.

Thanks Gordon Wrigley for the report.

Regression in df32fd42b84cc6dbba173201f244491b0d154a63.

Backport of fe9c7ded2996364f853c524b4421274717d89d5f from master

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