Opened 8 years ago

Closed 8 years ago

Last modified 5 years ago

#8106 closed (fixed)

select_related queries return wrong data

Reported by: soroosh@… Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: reza@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

select_related queries are wrong in a situation where there are two different paths to a model, for example:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=200)
    
class Person(models.Model):
    user = models.ForeignKey("User", unique=True)

class Organizer(models.Model):
    person = models.ForeignKey("Person")

class Student(models.Model):
    person = models.ForeignKey("Person")

class Class(models.Model):
    org = models.ForeignKey("Organizer")


class Enrollment(models.Model):
    std = models.ForeignKey("Student")
    claz = models.ForeignKey("Class")

The bug can be triggered with this snippet on latest SVN:

us = User(name="std")
us.save()
usp = Person(user=us)
usp.save()

uo = User(name="org")
uo.save()
uop = Person(user=uo)
uop.save()

s = Student(person = usp)
s.save()

o = Organizer(person = uop)
o.save()

c = Class(org=o)
c.save()

e = Enrollment(std=s, claz=c)
e.save()

e_related = Enrollment.objects.all().select_related()[0]
print e_related.std.person.user.name
print e_related.claz.org.person.user.name

output is "std" for both, but it should be

std
org

Attachments (1)

queries.models.patch (1.6 KB) - added by Camron Flanders <camron.flanders@…> 8 years ago.
regressiontest patch for this ticket.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 8 years ago by Reza Mohammadi

Cc: reza@… added
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

comment:2 Changed 8 years ago by Malcolm Tredinnick

Owner: changed from nobody to Malcolm Tredinnick

Changed 8 years ago by Camron Flanders <camron.flanders@…>

Attachment: queries.models.patch added

regressiontest patch for this ticket.

comment:3 Changed 8 years ago by Camron Flanders <camron.flanders@…>

This is the query that django is currently producing:

                        SELECT	
				"ticket_enrollment"."id", "ticket_enrollment"."std_id", "ticket_enrollment"."claz_id", 
				"ticket_student"."id", "ticket_student"."person_id", 
				"ticket_person"."id", "ticket_person"."user_id", 
				"ticket_user"."id", "ticket_user"."name", 
				"ticket_class"."id", "ticket_class"."org_id", 
				"ticket_organizer"."id", "ticket_organizer"."person_id", 
				T7."id", T7."user_id", 
				"ticket_user"."id", "ticket_user"."name" 

			FROM "ticket_enrollment" 
				INNER JOIN "ticket_student"		ON ("ticket_enrollment"."std_id"	= "ticket_student"."id") 
				INNER JOIN "ticket_person"		ON ("ticket_student"."person_id" 	= "ticket_person"."id") 
				INNER JOIN "ticket_user"		ON ("ticket_person"."user_id"		= "ticket_user"."id") 
				INNER JOIN "ticket_class"		ON ("ticket_enrollment"."claz_id"	= "ticket_class"."id") 
				INNER JOIN "ticket_organizer"	ON ("ticket_class"."org_id"			= "ticket_organizer"."id") 
				INNER JOIN "ticket_person" T7	ON ("ticket_organizer"."person_id"	= T7."id") 
			LIMIT 1

This is the query that returns the correct results

		        SELECT	
				"ticket_enrollment"."id", "ticket_enrollment"."std_id", "ticket_enrollment"."claz_id", 
				"ticket_student"."id", "ticket_student"."person_id", 
				"ticket_person"."id", "ticket_person"."user_id", 
				"ticket_user"."id", "ticket_user"."name", 
				"ticket_class"."id", "ticket_class"."org_id", 
				"ticket_organizer"."id", "ticket_organizer"."person_id", 
				T7."id", T7."user_id", 
				T8."id", T8."name" 

			FROM "ticket_enrollment" 
				INNER JOIN "ticket_student"		ON ("ticket_enrollment"."std_id"	= "ticket_student"."id") 
				INNER JOIN "ticket_person"		ON ("ticket_student"."person_id" 	= "ticket_person"."id") 
				INNER JOIN "ticket_user"		ON ("ticket_person"."user_id"		= "ticket_user"."id") 
				INNER JOIN "ticket_class"		ON ("ticket_enrollment"."claz_id"	= "ticket_class"."id") 
				INNER JOIN "ticket_organizer"	ON ("ticket_class"."org_id"		= "ticket_organizer"."id") 
				INNER JOIN "ticket_person" T7	ON ("ticket_organizer"."person_id"	= T7."id")
				INNER JOIN "ticket_user" T8		ON (T8."id" = T7."id")
			LIMIT 1

comment:4 Changed 8 years ago by Jacob

Triage Stage: UnreviewedAccepted

comment:5 Changed 8 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(In [8559]) Fixed #8106 -- Untangled some problems with complex select_related() queries
and models that have multiple paths to them from other models.

comment:6 Changed 5 years ago by Jacob

milestone: 1.0

Milestone 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top