Code

Opened 19 months ago

Closed 16 months ago

Last modified 16 months ago

#19073 closed Bug (worksforme)

strange behaviour of select_related

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Sometimes Django uses inner join instead of left outer join in queries with select_related. This breaks queries with nullable OneToOne fields.
Here's the example

models:

class A(models.Model):
    pass

class B(models.Model):
    pass

class C(models.Model):
    b = models.OneToOneField(B)
    a = models.ForeignKey(A)

class D(models.Model):
    b = models.OneToOneField(B, null=True)

code

D.objects.all()
[<D: D object>, <D: D object>, <D: D object>] #ok
>>> D.objects.select_related('b').all()
[<D: D object>, <D: D object>, <D: D object>] #ok
>>> D.objects.select_related('b', 'b__c').all()
[<D: D object>, <D: D object>, <D: D object>] #ok
>>> D.objects.select_related('b', 'b__c__a').all()
[] #WRONG! empty result
>>> print D.objects.select_related('b', 'b__c__a').all().query
SELECT "app1_d"."id", "app1_d"."b_id", "app1_b"."id", "app1_c"."id", "app1_c"."b_id", "app1_c"."a_id", "app1_a"."id" FROM "app1_d" LEFT OUTER JOIN "app1_b" ON ("app1_d"."b_id" = "app1_b"."id") LEFT OUTER JOIN "app1_c" ON ("app1_b"."id" = "app1_c"."b_id") INNER JOIN "app1_a" ON ("app1_c"."a_id" = "app1_a"."id")

INNER JOIN "app1_a" should be LEFT OUTER JOIN "app1_a" imo

Attachments (0)

Change History (4)

comment:1 Changed 18 months ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to worksforme
  • Status changed from new to closed

Here's the result I get on master with the models described above:

>>> from test19073.models import *
>>> D.objects.create()
<D: D object>
>>> D.objects.all()
[<D: D object>]
>>> D.objects.select_related('b').all()
[<D: D object>]
>>> D.objects.select_related('b', 'b__c').all()
[<D: D object>]
>>> D.objects.select_related('b', 'b__c__a').all()
[<D: D object>]
>>> D.objects.select_related('b', 'b__c__b').all()
[<D: D object>]

This bug may have been fixed after 1.4, or your report may be incomplete.

Please reopen if you can reproduce the bug on master.

comment:2 Changed 16 months ago by anonymous

  • Resolution worksforme deleted
  • Status changed from closed to new
  • Summary changed from strange behaviuor of select_related to strange behaviour of select_related

With the model defined above, the following test fails (django 1.4):

def test_bug19073_ABCD(self):
    nb_of_objects = 10
    {D.objects.create() for _i in xrange(nb_of_objects)}
    self.assertEqual(len(D.objects.all()), nb_of_objects) # ok
    self.assertEqual(len(D.objects.select_related('b').all()), nb_of_objects) # ok
    self.assertEqual(len(D.objects.select_related('b__c').all()), nb_of_objects) # ok
    self.assertEqual(len(D.objects.select_related('b__c__a').all()), nb_of_objects) # nok
    self.assertEqual(len(D.objects.select_related('b__c__b').all()), nb_of_objects) # nok

A similar problem is with the following model (OneToOneField 'hidden' in the inheritance):

class R(models.Model):
    pass

class P(models.Model):
    pass

class Q(P):
    r = models.ForeignKey(R)
    pass
    
class O(models.Model):
    p = models.ForeignKey(P, null=True)

and with the following test:

def test_bug19073_OPQR(self):
    nb_of_objects = 10
    {O.objects.create() for _i in xrange(nb_of_objects)}
    self.assertEqual(len(O.objects.all()), nb_of_objects) # ok
    self.assertEqual(len(O.objects.select_related('p').all()), nb_of_objects) # ok
    self.assertEqual(len(O.objects.select_related('p__q').all()), nb_of_objects) # ok
    self.assertEqual(len(O.objects.select_related('p__q__r').all()), nb_of_objects) # nok

In every "nok" cases, there is a INNER JOIN in the underlying query that messes things up.

Strangely enough, note that the following "simpler" model J->K->L->M (no inheritance as opposed to O->P=>Q->R and without going reverse C->B as in D->B, C->{B,A} above), works:

class M(models.Model): #rush
    pass

class L(models.Model): #captured_snapshot
    m = models.ForeignKey(M)
    pass

class K(models.Model): #snapshot
    l = models.OneToOneField(L)
    pass
    
class J(models.Model): #talk
    k = models.OneToOneField(K, null=True)

Tests are all ok here:

def test_bug19073_JKLM(self):
    nb_of_objects = 10
    {J.objects.create() for _i in xrange(nb_of_objects)}
    self.assertEqual(len(J.objects.all()), nb_of_objects) # ok
    self.assertEqual(len(J.objects.select_related('k').all()), nb_of_objects) # ok
    self.assertEqual(len(J.objects.select_related('k__l').all()), nb_of_objects) # ok
    self.assertEqual(len(J.objects.select_related('k__l__m').all()), nb_of_objects) # ok <- strange!

comment:3 Changed 16 months ago by anonymous

Could this be a regression of #7369? It seems related...

comment:4 Changed 16 months ago by akaariai

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

We are not going to fix this in 1.4. Our backporting policy is such that only critical bug fixes are applied to released versions.

aaugustin tried to reproduce this on master and the queries worked. There have been some improvements in select_related for 1.5. So, it is likely that this has been fixed in 1.5.

Re-closing as worksforme.

EDIT: As a workaround you can add a filter like Q(b__c__a__isnull=True)|Q(b__c__a__isnull=False) to the query. The filter doesn't actually filter anything out but it forces a LEFT join for the problematic join.

Last edited 16 months ago by akaariai (previous) (diff)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.