Opened 14 years ago

Closed 14 years ago

Last modified 12 years ago

#12937 closed (fixed)

Select related() not working as of Beta

Reported by: Yeago 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: no UI/UX: no

Description (last modified by Ramiro Morales)

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.

Change History (4)

comment:1 by Ramiro Morales, 14 years ago

Description: modified (diff)

reformatted description, please use the Preview feature.

comment:2 by Russell Keith-Magee, 14 years ago

Triage Stage: UnreviewedAccepted

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 by Russell Keith-Magee, 14 years ago

Resolution: fixed
Status: newclosed

(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 by Jacob, 12 years ago

milestone: 1.2

Milestone 1.2 deleted

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