#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
Change History (4)
comment:1 by , 12 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
comment:2 by , 12 years ago
Resolution: | worksforme |
---|---|
Status: | closed → new |
Summary: | strange behaviuor of select_related → 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:4 by , 12 years ago
Resolution: | → worksforme |
---|---|
Status: | new → 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.
Here's the result I get on master with the models described above:
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.