Code

Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#18090 closed Bug (fixed)

`prefetch_related` selects entire table when reverse traversing a `OneToOneField`

Reported by: aaugustin Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

This is best demonstrated with an example:

myapp/models.py

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=10)

class Group(models.Model):
    users = models.ManyToManyField(User)

class GroupExtra(models.Model):
    group = models.OneToOneField(Group)

./manage.py shell

# Let's create 10 users, 5 groups + group_extras.

>>> from myapp.models import *
>>> users = [User.objects.create(name='user %d' % i) for i in range(10)]
>>> groups = [Group.objects.create() for i in range(5)]
>>> for i in range(5):
...     groups[i].users = users[i:i + 5]
... 
>>> group_extras = [GroupExtra.objects.create(group=groups[i]) for i in range(5)]

# Load an user with his groups and group_extras.

>>> from pprint import pprint
>>> from django.db import connection
>>> connection.queries = []
>>> user = User.objects.prefetch_related('group_set__groupextra').get(name='user 3')
>>> pprint(connection.queries)
[{'sql': u'SELECT "myapp_user"."id", "myapp_user"."name" FROM "myapp_user" WHERE "myapp_user"."name" = user 3 ',
  'time': '0.000'},
 {'sql': u'SELECT ("myapp_group_users"."user_id") AS "_prefetch_related_val", "myapp_group"."id" FROM "myapp_group" INNER JOIN "myapp_group_users" ON ("myapp_group"."id" = "myapp_group_users"."group_id") WHERE "myapp_group_users"."user_id" IN (4)',
  'time': '0.000'},
 {'sql': u'SELECT "myapp_groupextra"."id", "myapp_groupextra"."group_id" FROM "myapp_groupextra"',
  'time': '0.000'}]

# The last query unnecessarily loaded the entire `myapp_groupextra` table.

# But prefetch_related worked as expected: the following code doesn't trigger another query

>>> connection.queries = []
>>> for group in user.group_set.all():
...     print group.pk, group.groupextra.pk
... 
1 1
2 2
3 3
4 4
>>> pprint(connection.queries)
[]

Attachments (2)

ticket_18090.diff (668 bytes) - added by lukeplant 2 years ago.
fix
ticket_18090.2.diff (670 bytes) - added by lukeplant 2 years ago.
fix (version 2)

Download all attachments as: .zip

Change History (5)

Changed 2 years ago by lukeplant

fix

Changed 2 years ago by lukeplant

fix (version 2)

comment:1 Changed 2 years ago by lukeplant

The attached patch is completely untested, but should fix the issue. It was a simple case of forgetting to use the filter parameters.

Since this is a major bug in a new feature (would have been a release blocker), and is extremely unlikely to cause regressions, I think it should be backported to 1.4.X

comment:2 Changed 2 years ago by aaugustin

  • Resolution set to fixed
  • Status changed from new to closed

In [17888]:

Fixed #18090 -- Applied filters when running prefetch_related backwards through a one-to-one relation.

comment:3 Changed 2 years ago by aaugustin

In [17889]:

[1.4.X] Fixed #18090 -- Applied filters when running prefetch_related backwards through a one-to-one relation. Backport of r17888 from trunk.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.