id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
6981	Nullable foreign keys use inner joins with select related	Petr Marhoun <petr.marhoun@…>	nobody	"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):

{{{
#!python
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"")
}}}"		closed	Database layer (models, ORM)	queryset-refactor		fixed	qs-rf		Unreviewed	0	0	0	0	0	0