Opened 6 years ago

Closed 6 years ago

Last modified 4 years ago

#12937 closed (fixed)

Select related() not working as of Beta

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

Description (last modified by ramiro)

Given the following expression:


And the following models:

class Contact(models.Model):
    program = models.ForeignKey('agency.Program')
    consumer = models.ForeignKey('consumer.Consumer',null=True,blank=True)
    date = models.DateField('Contact Date')
    notes = models.TextField()
    date_added = models.DateTimeField(editable=False)
    method = models.CharField(max_length=10,choices=choices.CONTACT_METHOD,\

# consumer_contact
class Contact(basemodels.Contact):
    base = models.OneToOneField(basemodels.Contact, parent_link=True,related_name="consumer_contact")
    service = models.ForeignKey(Service)
    confidential = models.BooleanField(default=False,help_text='These notes will not be visible to general staff')
    group = models.ForeignKey(Group,null=True,blank=True)
    group_contact = models.ForeignKey('consumer.GroupContact',blank=True,null=True)

    def save(self, *args, **kwargs):
        super(Contact, self).save(*args,**kwargs)

    class Meta:
        permissions = (
            ("allow_confidential", "Can make contacts confidential"),

class Contact(basemodels.Contact):
    information=models.ManyToManyField(Information,verbose_name='Information Requested') 

the following SQL is produced:

SELECT (fields) FROM `base_contact`

LEFT OUTER JOIN `consumer_consumer` ON (`base_contact`.`consumer_id` =
`consumer_consumer`.`id`) # ok

INNER JOIN `agency_program` ON (`base_contact`.`program_id` =
`agency_program`.`id`) # ok

LEFT OUTER JOIN `consumer_contact` ON (`base_contact`.`id` =
`consumer_contact`.`base_id`) # ok

INNER JOIN `base_contact` T5 ON (`consumer_contact`.`base_id` = T5.`id`) # ?!?

# These next two are questionable, since they are joined on a LEFT
OUTER (so they may potentially not be there)

INNER JOIN `consumer_service` ON (`consumer_contact`.`service_id` =

INNER JOIN `consumer_servicetype` ON (`consumer_service`.`type_id` =

LEFT OUTER JOIN `IR_contact` ON (`base_contact`.`id` =
`IR_contact`.`base_id`)  # ok

INNER JOIN `base_contact` T9 ON (`IR_contact`.`base_id` = T9.`id`) # ?!?

WHERE `consumer_consumer`.`file` = 06-1757 ORDER BY `base_contact`.`date` DESC 

As you can see, there are two INNER JOINs which are messing up this otherwise valid and expected query.

Change History (4)

comment:1 Changed 6 years ago by ramiro

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

reformatted description, please use the Preview feature.

comment:2 Changed 6 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

Ok - this is a weird case of an old bug colliding with a new one.

This problem doesn't exist in Django 1.1, because we didn't allow reverse select related lookups for OneToOneFields. However, we didn't prevent them either - as logged by #10414, listing non-existent (oor non-supported) fields in a select related doesn't actually raise an error. So in 1.1, the query looks something like:

SELECT (fields)
FROM "myapp_basecontact" 
LEFT OUTER JOIN "myapp_consumer" 
	ON ("myapp_basecontact"."consumer_id" = "myapp_consumer"."id") 
INNER JOIN "myapp_program" 
	ON ("myapp_basecontact"."program_id" = "myapp_program"."id") 
WHERE "myapp_consumer"."file" = foo  ORDER BY "myapp_basecontact"."date" DESC

Which is fine, but not as good as it could be.

In r12307, we added support for reverse select related fields. This means that the 'IR_contact' and 'consumer_contact' fields now get expanded.

However, because we are following a reverse one-to-one field, and that field forms an inherited link, the select_related is doing a forward select related back onto the parent model. This is what causes the T5 and T9 joins.

There are some tests for inheritance and reverse select related, so it's not just as simple as inheritance being the problem, but it's in that general ballpark.

There is a second problem hidden in here, which is the use of an INNER JOIN for the non-nullable field joined on a nullable link. I think this is a repeat of what is reported by #12819.

comment:3 Changed 6 years ago by russellm

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

(In [12814]) Fixed #12937 -- Corrected the operation of select_related() when following an reverse relation on an inherited model. Thanks to subsume for the report.

comment:4 Changed 4 years ago by jacob

  • milestone 1.2 deleted

Milestone 1.2 deleted

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