#20955 closed Bug (fixed)
Deep select_related to same model returns incorrect relationship
Description ¶
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:
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:
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:
"deepselectrelated_staff"."id", "deepselectrelated_staff"."name"
There should infact be two joins to the staff table and the SELECT should be as follows:
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" )
Change History (3)
comment:1 by , 12 years ago
Severity: | Normal → Release blocker |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 12 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Seems to be 1.6.x regression, so release blocker. I will try to fix this today.