Django-ORM: distinct is needed. Why?

Question

I am playing around with django ORM

import django
django.setup()
from django.contrib.auth.models import User, Group
from django.db.models import Count

# All users
print(User.objects.all().count())
# --> 742

# Should be: All users which are in a group.
# But the result is different. I don't understand this.
print(User.objects.filter(groups__in=Group.objects.all()).count())
# --> 1731

# All users which are in a group.
# distinct needed
print(User.objects.filter(groups__in=Group.objects.all()).distinct().count())
# --> 543

# All users which are in a group. Without distinct, annotate seems to do this.
print(User.objects.filter(groups__in=Group.objects.all()).annotate(Count('pk')).count())
# --> 543

# All users which are in no group
print(User.objects.filter(groups__isnull=True).count())
# --> 199

# 199 + 543 = 742  (nice)

I don't understand the second query which returns 1731.

I know that I can use distinct().

Nevertheless 1731 looks like a bug to me.

What is the intention why below query is not distinct/unique?

User.objects.filter(groups__in=Group.objects.all())

Answer 1

Raw MySQL query looks like this:

SELECT user.id, group.id FROM user LEFT JOIN group ON user.group_id = group.id

The result will contain all possible combinations of users and groups and I guess some users belong to more than one group.

Answer 2

You are trying to fetch all users from all groups, but a user can present in multiple groups that's why distinct is required. if you want users ina specific group instead of doing an all try a filter query.