[Django] #25546: Duplicate queries on nested Prefetch objects with custom queries
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) | Keywords: prefetch,
Severity: Normal | prefetch_related
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25546>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: piquadrat | Owner: nobody
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: prefetch, prefetch_related | Triage Stage: Unreviewed
Has patch: 0 | Easy pickings: 0
UI/UX: 0 |
Changes (by piquadrat):
* Attachment "prefetch_test.tar.gz" added.
test project
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage:
prefetch_related | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage: Accepted
prefetch_related |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
Accepting for further investigation.
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner: nobody
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage: Accepted
prefetch_related |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: bux.patryk@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner:
Cleanup/optimization | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage: Accepted
prefetch_related |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => francoisfreitag
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner:
Cleanup/optimization | Status: assigned
(models, ORM) |
Keywords: prefetch, | Triage Stage: Accepted
prefetch_related |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/6002 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner:
Cleanup/optimization | Status: assigned
(models, ORM) |
Keywords: prefetch, | Triage Stage: Ready for
prefetch_related | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
Comment:
Patch LGTM.
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: piquadrat | Owner:
Cleanup/optimization | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage: Ready for
prefetch_related | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"bdbe50a491ca41e7d4ebace47bfe8abe50a58211" bdbe50a4]:
{{{
#!CommitTicketReference repository=""
revision="bdbe50a491ca41e7d4ebace47bfe8abe50a58211"
Fixed #25546 -- Prevented duplicate queries with nested
prefetch_related().
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:7>
Django
-------------------------------------+-------------------------------------
Type: | Freitag
Cleanup/optimization | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: prefetch, | Triage Stage: Ready for
prefetch_related | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"ec05ff086c0349e50c71b63c940a00a1d3f0385a" ec05ff08]:
{{{
#!CommitTicketReference repository=""
revision="ec05ff086c0349e50c71b63c940a00a1d3f0385a"
Refs #25546 -- Added detailed comments for prefetch_related test.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25546#comment:8>