Opened 10 months ago

Closed 10 months ago

Last modified 10 months 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 Changed 10 months ago by Gordon Wrigley

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 Changed 10 months ago by Gordon Wrigley

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 Changed 10 months ago by Gordon Wrigley

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

comment:4 Changed 10 months ago by Gordon Wrigley

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 Changed 10 months ago by Mariusz Felisiak

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

comment:6 Changed 10 months ago by Mariusz Felisiak

Cc: Simon Charette Thodoris Sotiropoulos added

comment:7 Changed 10 months ago by Hasan Ramezani

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

comment:8 Changed 10 months ago by Mariusz Felisiak

Needs documentation: set
Patch needs improvement: set

comment:9 Changed 10 months ago by Mariusz Felisiak

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

comment:10 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

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 Changed 10 months ago by Mariusz Felisiak <felisiak.mariusz@…>

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