Code

Opened 11 months ago

Closed 11 months ago

Last modified 11 months ago

#20955 closed Bug (fixed)

Deep select_related to same model returns incorrect relationship

Reported by: zanuxzan Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

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" )

Attachments (0)

Change History (3)

comment:1 Changed 11 months ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Severity changed from Normal to Release blocker
  • Triage Stage changed from Unreviewed to Accepted

Seems to be 1.6.x regression, so release blocker. I will try to fix this today.

comment:2 Changed 11 months ago by Anssi Kääriäinen <akaariai@…>

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

In 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.

comment:3 Changed 11 months ago by Anssi Kääriäinen <akaariai@…>

In 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

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.