Duplicate queries on nested Prefetch objects with custom queries

[Benjamin Wohlwend]

Assuming these models

from django.db import models

class FastfoodChain(models.Model):
    name = models.CharField(primary_key=True, max_length=100)


class Restaurant(models.Model):
    name = models.CharField(primary_key=True, max_length=100)
    chain = models.ForeignKey(FastfoodChain)


class RestaurantProfile(models.Model):
    user = models.ForeignKey('auth.User')
    restaurant = models.ForeignKey(Restaurant)

    is_favorite = models.BooleanField(default=False)

the following test fails

from django.contrib.auth.models import User
from django.db.models.query import Prefetch
from django.test import TestCase

from fastfood import models

class PrefetchTestCase(TestCase):
    def test_prefetch(self):
        user = User.objects.create(username='test')
        chain1, chain2, chain3, chain4 = [
            models.FastfoodChain.objects.create(name='chain1'),
            models.FastfoodChain.objects.create(name='chain2'),
            models.FastfoodChain.objects.create(name='chain3'),
            models.FastfoodChain.objects.create(name='chain4'),
        ]
        restaurant11, restaurant12, restaurant21, restaurant31, restaurant32, restaurant41 = [
            models.Restaurant.objects.create(chain=chain1, name='restaurant11'),
            models.Restaurant.objects.create(chain=chain1, name='restaurant12'),
            models.Restaurant.objects.create(chain=chain2, name='restaurant21'),
            models.Restaurant.objects.create(chain=chain3, name='restaurant31'),
            models.Restaurant.objects.create(chain=chain3, name='restaurant32'),
            models.Restaurant.objects.create(chain=chain4, name='restaurant41'),
        ]
        p1, p2, p3, p4 = [
            models.RestaurantProfile.objects.create(restaurant=restaurant11, user=user, is_favorite=True),
            models.RestaurantProfile.objects.create(restaurant=restaurant12, user=user, is_favorite=False),
            models.RestaurantProfile.objects.create(restaurant=restaurant21, user=user, is_favorite=True),
            models.RestaurantProfile.objects.create(restaurant=restaurant31, user=user, is_favorite=True),
        ]
        with self.assertNumQueries(3):
            chains = models.FastfoodChain.objects.filter(
                name__in=['chain1', 'chain2', 'chain4']
            ).prefetch_related(
                Prefetch(
                    'restaurant_set',
                    models.Restaurant.objects.prefetch_related(
                        Prefetch(
                            'restaurantprofile_set',
                            models.RestaurantProfile.objects.filter(
                                user=user,
                                is_favorite=True,
                            ),
                            to_attr='profiles',
                        )
                    ),
                    to_attr='restaurants',
                ),
            )
            result_chain_1, result_chain_2, result_chain_4 = list(chains)
            self.assertListEqual(result_chain_1.restaurants, [restaurant11, restaurant12])
            self.assertListEqual(result_chain_2.restaurants, [restaurant21])
            self.assertListEqual(result_chain_4.restaurants, [restaurant41])

            self.assertListEqual(result_chain_1.restaurants[0].profiles, [p1])
            self.assertListEqual(result_chain_1.restaurants[1].profiles, [])
            self.assertListEqual(result_chain_2.restaurants[0].profiles, [p3])
            self.assertListEqual(result_chain_4.restaurants[0].profiles, [])

With this output

======================================================================
FAIL: test_prefetch (fastfood.tests.PrefetchTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/benjaminwohlwend/projects/prefetch_test/fastfood/tests.py", line 57, in test_prefetch
    self.assertListEqual(result_chain_4.restaurants[0].profiles, [])
  File "/Users/benjaminwohlwend/.virtualenvs/prefetch-test/lib/python2.7/site-packages/django/test/testcases.py", line 93, in __exit__
    query['sql'] for query in self.captured_queries
AssertionError: 4 queries executed, 3 expected
Captured queries were:
SELECT "fastfood_fastfoodchain"."name" FROM "fastfood_fastfoodchain" WHERE "fastfood_fastfoodchain"."name" IN ('chain1', 'chain2', 'chain4')
SELECT "fastfood_restaurant"."name", "fastfood_restaurant"."chain_id" FROM "fastfood_restaurant" WHERE "fastfood_restaurant"."chain_id" IN ('chain1', 'chain2', 'chain4')
SELECT "fastfood_restaurantprofile"."id", "fastfood_restaurantprofile"."user_id", "fastfood_restaurantprofile"."restaurant_id", "fastfood_restaurantprofile"."is_favorite" FROM "fastfood_restaurantprofile" WHERE ("fastfood_restaurantprofile"."is_favorite" = 1 AND "fastfood_restaurantprofile"."user_id" = 1 AND "fastfood_restaurantprofile"."restaurant_id" IN ('restaurant11', 'restaurant12', 'restaurant21', 'restaurant41'))
SELECT "fastfood_restaurantprofile"."id", "fastfood_restaurantprofile"."user_id", "fastfood_restaurantprofile"."restaurant_id", "fastfood_restaurantprofile"."is_favorite" FROM "fastfood_restaurantprofile" WHERE ("fastfood_restaurantprofile"."is_favorite" = 1 AND "fastfood_restaurantprofile"."user_id" = 1 AND "fastfood_restaurantprofile"."restaurant_id" IN ('restaurant11', 'restaurant12', 'restaurant21', 'restaurant41'))

----------------------------------------------------------------------
Ran 1 test in 0.011s

The inner "prefetch" query is issued twice.

Note: I only used primary_key=True for nicer test output. The problem happens either way. I tried this with Django 1.8.5 and 1.9a1. A complete test project with the above code is attached.

[Benjamin Wohlwend]

test project

[Tim Graham]

Any chance you can suggest a fix (or at least provide a minimal test that reuses models in Django's test suite)? Otherwise, it might take me a while to triage the issue to determine if it's valid and something we can fix.

[Tim Graham]

Accepting for further investigation.

[Simon Charette]

​PR

[Simon Charette]

Patch LGTM.

[Tim Graham <timograham@…>]

In bdbe50a4:

Fixed #25546 -- Prevented duplicate queries with nested prefetch_related().

[Tim Graham <timograham@…>]

In ec05ff08:

Refs #25546 -- Added detailed comments for prefetch_related test.