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 Changed 3 years 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 3 years 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 3 years ago by Gordon Wrigley

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

comment:4 Changed 3 years 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 3 years ago by Mariusz Felisiak

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

comment:6 Changed 3 years ago by Mariusz Felisiak

Cc: Simon Charette Thodoris Sotiropoulos added

comment:7 Changed 3 years ago by Hasan Ramezani

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

comment:8 Changed 3 years ago by Mariusz Felisiak

Needs documentation: set
Patch needs improvement: set

comment:9 Changed 3 years ago by Mariusz Felisiak

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

comment:10 Changed 3 years 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 3 years 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