Select related() not working as of Beta

[Yeago]

Given the following expression:

bmodels.Contact.objects.select_related('IR_contact','consumer_contact','program','consumer').filter(consumer__file=file).order_by('-date')

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,\
            null=True,blank=True)

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

#ir_contact
class Contact(basemodels.Contact):
    base=models.OneToOneField(BaseContact,parent_link=True,related_name='IR_contact')
    IR=models.ForeignKey(IR,blank=True,null=True)
    referred_from=models.ForeignKey(ReferralSource,related_name='IR_referred_from')
    referred_to=models.ManyToManyField(ReferralSource,related_name='IR_referred_to')
    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` =
`consumer_service`.`id`)

INNER JOIN `consumer_servicetype` ON (`consumer_service`.`type_id` =
`consumer_servicetype`.`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.

[Ramiro Morales]

reformatted description, please use the Preview feature.

[Russell Keith-Magee]

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.

[Russell Keith-Magee]

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

[Jacob]

Milestone 1.2 deleted