Nullable foreign keys use inner joins with select related

[Petr Marhoun <petr.marhoun@…>]

Short version - if select related has name of nullable foreign key as an argument, inner joins are used and all objects with null are filtered.

I don't know if it is a problem - maybe it should be so. But it is not intuitive.

Long version (example):

from django.db import models

class One(models.Model):
    a = models.IntegerField()
    
class Two(models.Model):
    b = models.IntegerField()
    
class Three(models.Model):
    c = models.IntegerField()
    one = models.ForeignKey(One)
    two = models.ForeignKey(Two, null=True)

>>> from tmp.models import One, Two, Three
>>> from django.db import connection
>>> 
>>> One.objects.all().delete()
>>> first = [One.objects.create(a=a) for a in range(10)]
>>> 
>>> Two.objects.all().delete()
>>> second = [Two.objects.create(b=b) for b in range(10)]
>>> 
>>> Three.objects.all().delete()
>>> for c in range(10):
...     one = first[c]
...     two = c % 2 and second[c] or None
...     Three.objects.create(c=c, one=one, two=two)
>>>
>>> # No select related
... print len(Three.objects.all())
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related without argument - only not-null foreign keys are used
>>> print len(Three.objects.select_related()) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id")
>>> 
>>> # Select related with argument which is not foreign key - it is ingored
... print len(Three.objects.select_related('c')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related with argument which does not exists - it is ingored
... print len(Three.objects.select_related('nonsense')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related with argument which is not-null foreign key - inner join is used
... print len(Three.objects.select_related('one')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id")
>>> 
>>> # Select related with argument which is null foreign key - inner join is used
... print len(Three.objects.select_related('two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")
>>> 
>>> # Select related with arguments for both foreign keys - inner joins are used
... print len(Three.objects.select_related('two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")

[Petr Marhoun <petr.marhoun@…>]

Last example has a mistake, right version:

>>> # Select related with argument which is both foreign keys - inner joins are used
... print len(Three.objects.select_related('one', 'two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id") INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")

[Malcolm Tredinnick]

(In [7418]) queryset-refactor: When using select_related() with an explicit foreign key,
use the right join type if the FK is nullable. Fixed #6981.