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
21150	select_related and annotate won't follow nullable foreign keys	Eivind Fonn <evfonn@…>	nobody	"To reproduce, first the models:

{{{#!python
from django.db import models

class Alfa(models.Model):
    pass

class Bravo(models.Model):
    pass

class Charlie(models.Model):
    alfa = models.ForeignKey(Alfa, null=True)
    bravo = models.ForeignKey(Bravo, null=True)
}}}

Then in shell:

{{{#!python
In [2]: b = Bravo.objects.create()

In [3]: c = Charlie.objects.create(bravo=b)

In [4]: qsboth = Charlie.objects.select_related('alfa').annotate(Count('bravo__charlie')); qsboth
Out[4]: []

In [5]: qsselrel = Charlie.objects.select_related('alfa'); qsselrel
Out[5]: [<Charlie: Charlie object>]

In [6]: qsanno = Charlie.objects.annotate(Count('bravo__charlie')); qsanno
Out[6]: [<Charlie: Charlie object>]
}}}

As you can see, select_related and annotate both work as expected, but not together. Queries:

{{{#!python
In [7]: print(str(qsboth.query))
SELECT ""bugrep_charlie"".""id"", ""bugrep_charlie"".""alfa_id"", ""bugrep_charlie"".""bravo_id"", COUNT(T4.""id"") AS ""bravo__charlie__count"", ""bugrep_alfa"".""id"" FROM ""bugrep_charlie"" INNER JOIN ""bugrep_alfa"" ON ( ""bugrep_charlie"".""alfa_id"" = ""bugrep_alfa"".""id"" ) LEFT OUTER JOIN ""bugrep_bravo"" ON ( ""bugrep_charlie"".""bravo_id"" = ""bugrep_bravo"".""id"" ) LEFT OUTER JOIN ""bugrep_charlie"" T4 ON ( ""bugrep_bravo"".""id"" = T4.""bravo_id"" ) GROUP BY ""bugrep_charlie"".""id"", ""bugrep_charlie"".""alfa_id"", ""bugrep_charlie"".""bravo_id"", ""bugrep_alfa"".""id""

In [8]: print(str(qsselrel.query))
SELECT ""bugrep_charlie"".""id"", ""bugrep_charlie"".""alfa_id"", ""bugrep_charlie"".""bravo_id"", ""bugrep_alfa"".""id"" FROM ""bugrep_charlie"" LEFT OUTER JOIN ""bugrep_alfa"" ON ( ""bugrep_charlie"".""alfa_id"" = ""bugrep_alfa"".""id"" )
}}}

Using only select_related yields an outer join on the alfa table, while adding annotate in the mix gives an inner join. Indeed, if we make an alfa object, it works fine:

{{{#!python
In [9]: a = Alfa.objects.create()

In [10]: Charlie.objects.update(alfa=a)
Out[10]: 1

In [11]: Charlie.objects.annotate(Count('bravo__charlie')).select_related('alfa')
Out[11]: [<Charlie: Charlie object>]
}}}"	Bug	closed	Database layer (models, ORM)	1.6-beta-1	Release blocker	fixed	select_related annotate	bmispelon@…	Accepted	0	0	0	0	0	0