[Django] #20955: Deep select_related to same model returns incorrect relationship
Django
----------------------------------------------+--------------------
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.
Django
Reporter: zanuxzan | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* needs_better_patch: => 0
* needs_docs: => 0
* severity: Normal => Release blocker
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
Seems to be 1.6.x regression, so release blocker. I will try to fix this
today.
--
Ticket URL: <https://code.djangoproject.com/ticket/20955#comment:1>
Django
Reporter: zanuxzan | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"8d65b6082c8bf5df25608d8733470879a8a61d7d"]:
{{{
#!CommitTicketReference repository=""
revision="8d65b6082c8bf5df25608d8733470879a8a61d7d"
Fixed #20955 -- select_related regression
In cases where the same connection (from model A to model B along the
same field) was needed multiple times in a select_related query, the
join setup code mistakenly reused an existing join.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/20955#comment:2>
Django
Reporter: zanuxzan | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) | Resolution: fixed
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by Anssi Kääriäinen <akaariai@…>):
In [changeset:"161e26c2ec9f88bf0395941aaa2fd193b110affd"]:
{{{
#!CommitTicketReference repository=""
revision="161e26c2ec9f88bf0395941aaa2fd193b110affd"
[1.6.x] Fixed #20955 -- select_related regression
In cases where the same connection (from model A to model B along the
same field) was needed multiple times in a select_related query, the
join setup code mistakenly reused an existing join.
Backpatch of 8d65b6082c8bf5df25608d8733470879a8a61d7d.
Conflicts:
django/db/models/sql/compiler.py
tests/queries/tests.py
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/20955#comment:3>