#20955: Deep select_related to same model returns incorrect relationship
----------------------------------------------+--------------------
Reporter: zanuxzan | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
When using select_related on a deeply nested relationship and you have two
(or possibly more) relationships that use the same model the SQL query is
incorrect and thus the resulting model incorrect.
This is best illustrated with an example:
{{{#!python
expect = Task.objects.get(pk=1)
actual = Task.objects.select_related('creator__staffuser__staff',
'owner__staffuser__staff').get(pk=1)
if actual.creator.staffuser.staff != expect.creator.staffuser.staff:
print "Creator Incorrect"
if actual.owner.staffuser.staff != expect.owner.staffuser.staff:
print "Owner Incorrect"
}}}
Outputs ```Owner Incorrect```
== Test Project
I've created a test project at
https://github.com/alexhayes/deepselectrelated
This test project has two tests that illustrates the difference in
behaviour.
== Issue Location
It appears that this issue exists in the actual SQL statements that are
being generated, as taken from my test project:
{{{#!sql
SELECT "deepselectrelated_task"."id",
"deepselectrelated_task"."title", "deepselectrelated_task"."creator_id",
"deepselectrelated_task"."owner_id",
"auth_user"."id", "auth_user"."password",
"auth_user"."last_login", "auth_user"."is_superuser",
"auth_user"."username", "auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."date_joined",
"deepselectrelated_staffuser"."user_ptr_id",
"deepselectrelated_staffuser"."staff_id",
"deepselectrelated_staff"."id",
"deepselectrelated_staff"."name",
T5."id", T5."password", T5."last_login",
T5."is_superuser", T5."username", T5."first_name", T5."last_name",
T5."email", T5."is_staff", T5."is_active", T5."date_joined",
T6."user_ptr_id", T6."staff_id",
"deepselectrelated_staff"."id",
"deepselectrelated_staff"."name"
FROM "deepselectrelated_task"
INNER JOIN "auth_user" ON ( "deepselectrelated_task"."creator_id" =
"auth_user"."id" )
LEFT OUTER JOIN "deepselectrelated_staffuser" ON ( "auth_user"."id" =
"deepselectrelated_staffuser"."user_ptr_id" )
LEFT OUTER JOIN "deepselectrelated_staff" ON (
"deepselectrelated_staffuser"."staff_id" = "deepselectrelated_staff"."id"
)
INNER JOIN "auth_user" T5 ON ( "deepselectrelated_task"."owner_id" =
T5."id" )
LEFT OUTER JOIN "deepselectrelated_staffuser" T6 ON ( T5."id" =
T6."user_ptr_id" )
}}}
Note that there is only one join to the staff table and the following part
of the SELECT query is incorrectly repeated:
{{{#!sql
"deepselectrelated_staff"."id", "deepselectrelated_staff"."name"
}}}
There should infact be two joins to the staff table and the SELECT should
be as follows:
{{{#!sql
SELECT "deepselectrelated_task"."id",
"deepselectrelated_task"."title", "deepselectrelated_task"."creator_id",
"deepselectrelated_task"."owner_id",
"auth_user"."id", "auth_user"."password",
"auth_user"."last_login", "auth_user"."is_superuser",
"auth_user"."username", "auth_user"."first_name", "auth_user"."last_name",
"auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."date_joined",
"deepselectrelated_staffuser"."user_ptr_id",
"deepselectrelated_staffuser"."staff_id",
"deepselectrelated_staff"."id",
"deepselectrelated_staff"."name",
T5."id", T5."password", T5."last_login",
T5."is_superuser", T5."username", T5."first_name", T5."last_name",
T5."email", T5."is_staff", T5."is_active", T5."date_joined",
T6."user_ptr_id", T6."staff_id",
T7."id", T7."name"
FROM "deepselectrelated_task"
INNER JOIN "auth_user" ON ( "deepselectrelated_task"."creator_id" =
"auth_user"."id" )
LEFT OUTER JOIN "deepselectrelated_staffuser" ON ( "auth_user"."id" =
"deepselectrelated_staffuser"."user_ptr_id" )
LEFT OUTER JOIN "deepselectrelated_staff" ON (
"deepselectrelated_staffuser"."staff_id" = "deepselectrelated_staff"."id"
)
INNER JOIN "auth_user" T5 ON ( "deepselectrelated_task"."owner_id" =
T5."id" )
LEFT OUTER JOIN "deepselectrelated_staffuser" T6 ON ( T5."id" =
T6."user_ptr_id" )
LEFT OUTER JOIN "deepselectrelated_staff" T7 ON ( T6."staff_id" = T7."id"
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/20955>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.6c80da9da49157979c0425220a4d7516%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.
