Opened 4 years ago

Closed 4 years ago

#24766 closed Bug (fixed)

annotation with Q object filters results

Reported by: karyon Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords:
Cc: josh.smeaton@…, python@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by karyon)

I tried to annotate a query of UserProfiles with a boolean indicating whether each user is in a specific group or not. Besides the fact that i didn't find any obvious or easy to find solution for that, i encountered the following oddity:

Any of these:

UserProfile.objects.annotate(is_member=Case(When(groups__name="Group name", then=Value(True)), default=Value(False), output_field=BooleanField()))
UserProfile.objects.annotate(is_member=ExpressionWrapper(Q(groups__name="Group name"), output_field=BooleanField()))

returns only users that are in some group. Users without groups are not in the result. This was unexpected since i didn't request any filtering.

The issue might be that both queries use inner joins. here's the sql:

'SELECT <snip>, CASE WHEN "auth_group"."name" = Group name THEN True ELSE False END AS "is_member" FROM "evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" = "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group" ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'
'SELECT <snip>, "auth_group"."name" = Group name AS "is_member" FROM "evaluation_userprofile" INNER JOIN "evaluation_userprofile_groups" ON ( "evaluation_userprofile"."id" = "evaluation_userprofile_groups"."userprofile_id" ) INNER JOIN "auth_group" ON ( "evaluation_userprofile_groups"."group_id" = "auth_group"."id" )'

Change History (8)

comment:1 Changed 4 years ago by karyon

Description: modified (diff)

comment:2 Changed 4 years ago by Josh Smeaton

Cc: josh.smeaton@… added
Triage Stage: UnreviewedAccepted

comment:3 Changed 4 years ago by Shai Berger

It seems #24753 is closely related; the difference is that the annotations there are aggregates.

comment:4 Changed 4 years ago by Ian Foote

Cc: python@… added

comment:5 Changed 4 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In be9d645:

Fixed #24766 -- Added join promotion for Case expressions

comment:6 Changed 4 years ago by Tim Graham <timograham@…>

In 056a91db:

[1.8.x] Fixed #24766 -- Added join promotion for Case expressions

Backport of be9d645346a20a6c394bf70d47b1b1d5c81ff530 from master

comment:7 Changed 4 years ago by Grafumbly

Resolution: fixed
Status: closednew

This bug still occurs if you've got more than one annotation on the same table.
Say you've got 3 Pizza objects having an m2m with Toppings. One pizza has pepperoni, one mushrooms, and one is just plain cheese with no toppings.

Single annotation:

$> annotations = {
      'pepperoni_ct': Count(Case(When(toppings__name='pepperoni', then='toppings')))
   }
$> Pizza.objects.annotate(**annotations).count()
3

We correctly promote the join to OUTER and get all three Pizza objects.

But now with two annotations:

$> annotations = {
      'pepperoni_ct': Count(Case(When(toppings__name='pepperoni', then='toppings')))
      'mushroom_ct': Count(Case(When(toppings__name='mushrooms', then='toppings')))
   }
$> Pizza.objects.annotate(**annotations).count()
2

The join is not promoted and we only get two Pizza objects.

comment:8 Changed 4 years ago by Tim Graham

Resolution: fixed
Status: newclosed

As the fix for this ticket has already been released, please open a new ticket for further problems. In this case, I wonder if the issue you describe might be a symptom of #10060 ("Multiple table annotation failure").

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