#21150: select_related and annotate won't follow nullable foreign keys
----------------------------------------------+----------------------------
Reporter: Eivind Fonn <evfonn@…> | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6-beta-1
Severity: Normal | Keywords: select_related
Triage Stage: Unreviewed | annotate
Easy pickings: 0 | Has patch: 0
| UI/UX: 0
----------------------------------------------+----------------------------
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>]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/21150>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/073.f05ec097463fadb85eda5ca2641e2951%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.
