annotation with Q object filters results

[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" )'

[Shai Berger]

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

[Tim Graham <timograham@…>]

In be9d645:

Fixed #24766 -- Added join promotion for Case expressions

[Tim Graham <timograham@…>]

In 056a91db:

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

Backport of be9d645346a20a6c394bf70d47b1b1d5c81ff530 from master

[Grafumbly]

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.

[Tim Graham]

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").