prefetch_related return mistaken result

[mostafa]

I have model like this :

class GlobalTeam(models.Model):
    team_name = models.CharField(max_length=100)
    user_profiles = models.ManyToManyField(UserProfile, related_name='user_teams')
    team_admin = models.ForeignKey(UserProfile, related_name='head_teams')

and this

class UserProfile(models.Model):
    user = models.OneToOneField(User, related_name='profile')

now, I have a queryset like this:

my_teams =  GlobalTeam.objects.filter(Q(team_admin__user=user) | Q(user_profiles__user=user)).select_related(
    'team_admin',
    'team_admin__user'
).prefetch_related(
    'user_profiles',
    'user_profiles__user',
).annotate(
    user_cnt=(
        Count('user_profiles', distinct=True) +
        1
    ),
).distinct()

I don't know what but I get this outputs:

>>> my_teams[0].user_profiles.all()
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>]
>>> my_teams[1].user_profiles.all()
[<UserProfile: d>]
>>> for team in my_teams:
...  print(team.user_profiles.all())
... 
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>, <UserProfile: d>]
[]
>>> my_teams[0].user_profiles.all()
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>, <UserProfile: d>]

my psql version is: psql (PostgreSQL) 9.3.13
my django version is: 1.9.9

[Tim Graham]

What's the expected result? Can you provide a failing test case including the data?

[mostafa]

If you see outputs, for first item the output is:

>>> my_teams[0].user_profiles.all()
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>]

that's true output for this item and second item output is:

>>> my_teams[1].user_profiles.all()
[<UserProfile: d>]

that's true output for this item, now I run a for loop on the list the output is:

>>> for team in my_teams:
...  print(team.user_profiles.all())
... 
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>, <UserProfile: d>]
[]

as you see the output is wrong and <UserProfile: d> must be in the second item but after for loop it's in the first item!

>>> my_teams[0].user_profiles.all()
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>, <UserProfile: d>]

[Baptiste Mispelon]

Hi,

I can reproduce the issue on master (with postgres, not with sqlite) with the attached models and test:

# models.py
from django.db import models


class User(models.Model):
    name = models.CharField(max_length=20, unique=True)


class UserProfile(models.Model):
    user = models.OneToOneField('User', related_name='profile', on_delete=models.CASCADE)

    def __str__(self):
        return self.user.name


class GlobalTeam(models.Model):
    team_name = models.CharField(max_length=100)
    user_profiles = models.ManyToManyField('UserProfile', related_name='user_teams')
    team_admin = models.ForeignKey('UserProfile', related_name='head_teams', on_delete=models.CASCADE)


# tests.py
from django.db.models import Count, Q
from django.template import Template, Context
from django.test import TestCase

from .models import User, UserProfile, GlobalTeam


class ReproTestCase(TestCase):
    @classmethod
    def setUpTestData(cls):
        users = [User.objects.create(name=c) for c in 'abcd']
        profiles = [UserProfile.objects.create(user=user) for user in users]

        team1 = GlobalTeam.objects.create(team_name='Team 1', team_admin=profiles[0])
        team2 = GlobalTeam.objects.create(team_name='Team 2', team_admin=profiles[0])

        team1.user_profiles.add(*profiles[:3])
        team2.user_profiles.add(profiles[3])


    def test_reproduction(self):
        my_teams =  GlobalTeam.objects.prefetch_related(
            'user_profiles',
        ).annotate(
            user_cnt=(
                Count('user_profiles', distinct=True)
            ),
        ).distinct()

        self.assertEqual(len(my_teams), 2)


        self.assertQuerysetEqual(
            my_teams[0].user_profiles.all(),
            ['<UserProfile: a>', '<UserProfile: b>', '<UserProfile: c>'],
            ordered=False,
        )
        self.assertQuerysetEqual(
            my_teams[1].user_profiles.all(),
            ['<UserProfile: d>'],
            ordered=False,
        )

        evaluated = list(my_teams)
        self.assertQuerysetEqual(
            evaluated[0].user_profiles.all(),
            ['<UserProfile: a>', '<UserProfile: b>', '<UserProfile: c>'],
            ordered=False,
        )
        self.assertQuerysetEqual(
            evaluated[1].user_profiles.all(),
            ['<UserProfile: d>'],
            ordered=False,
        )

Interestingly, removing the +1 after the Count(...) makes the test pass. The same happens when removing the .distinct() call at the end or the .annotate(...).

I don't really understand what is happening and I'm not sure if this is a bug in Django or a misuse of the ORM but I'll assume the former and move the ticket forward.

Thanks.

[François Freitag]

Hi,

I fail to reproduce this issue. The code given by Baptiste leads to a test fail, but it's only a matter of ordering.
The test below passes (I've commented the changes):

# tests.py
    def test_reproduction(self):
        my_teams =  GlobalTeam.objects.prefetch_related(
            'user_profiles',
        ).annotate(
            user_cnt=(
                Count('user_profiles', distinct=True) + 1  # Added the "+ 1"
            ),
        ).distinct()

        self.assertEqual(len(my_teams), 2)

        self.assertQuerysetEqual(
            my_teams[1].user_profiles.all(),  # Changed my_teams[0] to my_teams[1]
            ['<UserProfile: a>', '<UserProfile: b>', '<UserProfile: c>'],
            ordered=False,
        )
        self.assertQuerysetEqual(
            my_teams[0].user_profiles.all(),   # Change my_teams[1] to my_teams[0]
            ['<UserProfile: d>'],
            ordered=False,
        )

        evaluated = list(my_teams)
        self.assertQuerysetEqual(
            evaluated[1].user_profiles.all(),   # Changed evaluated[0] to evaluated[1]
            ['<UserProfile: a>', '<UserProfile: b>', '<UserProfile: c>'],
            ordered=False,
        )
        self.assertQuerysetEqual(
            evaluated[0].user_profiles.all(),  # Changed evaluated[1] to evaluated[0]
            ['<UserProfile: d>'],
            ordered=False,
        )

I cannot reproduce the original issue:

my_teams =  GlobalTeam.objects.filter(Q(team_admin__user=user) | Q(user_profiles__user=user)).select_related(
    'team_admin',
    'team_admin__user'
).prefetch_related(
    'user_profiles',
    'user_profiles__user',
).annotate(
    user_cnt=(
        Count('user_profiles', distinct=True) +
        1
    ),
).distinct()
# Breakpoint...
(Pdb) print(my_teams[0].user_profiles.all())
[<UserProfile: d>]
(Pdb) print(my_teams[1].user_profiles.all())
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>]
(Pdb) for team in my_teams: print(team.user_profiles.all())
[<UserProfile: d>]
[<UserProfile: a>, <UserProfile: b>, <UserProfile: c>]
(Pdb) my_teams[0].user_profiles.all()
[<UserProfile: d>]

I'm using django 1.9.9 and postgresql 9.3.14.
I could not reproduce it on master as well (with postgresql 9.5.4).

Have I missed something?

[Tim Graham]

Agreed, it seems we need additional information (i.e. a complete test case) from the reporter.