Django ~Q with Joins Functioning Incorrectly. Bug?

Question

We're encountering a very strange problem regarding the negation of Q objects in Django. Let's just use Football as the example:

class Team(models.Model):
    id = UUIDField(primary_key=True)

class Player(models.Model):
    id = UUIDField(primary_key=True)
    name = models.CharField(max_length=128)
    team = models.ForeignKey(Team)
    touchdowns = models.IntegerField()

There are 10 teams.

There are 100 players, with 10 on each team. Each team has a player named "Joe". There is one "Joe" on one team who has scored 5 touchdowns. All other Joe's have scored 1 touchdown. There are 8 teams where every Player has scored only 1 touchdown.

I want to get the Teams that have a player named Joe that has scored at least 3 Touchdowns.

models.Team.objects.filter(Q(player__name="Joe", player__touchdowns__gte=3)).count()

That returns One, as it should.The negation of that should return 9 (The other 9 teams that don't have a player named Joe that has at least 3 Touchdowns):

models.Team.objects.filter(~Q(player__name="Joe", player__touchdowns__gte=3)).count()

instead returns any team where everyone on that team has less than 3 Touchdowns (8).

Where am I going wrong? Please note that our actual application of this is much more complicated, so we NEED to use Q objects with negation, we cannot use Exclude.

Answer 1

The best way to ferret out why these differences occur is to investigate the queries that are generated: django-debug-toolbar comes with a debugsqlshell command that prints the actual query sent to the database after any use of the Django queryset API. For these tests, I used the User model with a join on Group. I too noticed different counts for selected objects, so on the face it seems like a good correlation to your use-case.

User.objects.filter(~Q(username='jdoe', groups__name='Awesome Group'))

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
WHERE NOT ("auth_user"."username" = 'jdoe'
           AND "auth_user"."id" IN
             (SELECT U1."user_id"
              FROM "auth_user_groups" U1
              INNER JOIN "auth_group" U2 ON (U1."group_id" = U2."id")
              WHERE (U2."name" = 'Awesome Group'
                     AND U1."user_id" IS NOT NULL))) LIMIT 21

User.objects.exclude(Q(username='jdoe', groups__name='Awesome Group'))

SELECT "auth_user"."id",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."password",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."is_superuser",
       "auth_user"."last_login",
       "auth_user"."date_joined"
FROM "auth_user"
INNER JOIN "auth_user_groups" ON ("auth_user"."id" = "auth_user_groups"."user_id")
INNER JOIN "auth_group" ON ("auth_user_groups"."group_id" = "auth_group"."id")
WHERE NOT (("auth_user"."username" = 'jdoe'
            AND "auth_group"."name" = 'Awesome Group')) LIMIT 21

The difference here comes in where the INNER JOIN happens. The Q object causes the INNER JOIN in the first example and then the selection with the INNER JOIN is negated because of the ~. The case of exclude, the negation happens in parallel to the INNER JOIN.