Opened 7 years ago

Closed 7 years ago

Last modified 4 years ago

#8106 closed (fixed)

select_related queries return wrong data

Reported by: soroosh@… Owned by: mtredinnick
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@…> 7 years ago.
regressiontest patch for this ticket.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 7 years ago by remohammadi

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

comment:2 Changed 7 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick

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

regressiontest patch for this ticket.

comment:3 Changed 7 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 7 years ago by jacob

  • Triage Stage changed from Unreviewed to Accepted

comment:5 Changed 7 years ago by mtredinnick

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

(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 4 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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