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
16715	Wrong JOIN with nested null-able foreign keys	Sebastian Goll	nobody	"Consider the following models:

{{{
class Event(models.Model):
    screening = models.ForeignKey('Screening', blank=True, null=True)

class Screening(models.Model):
    movie = models.ForeignKey('Movie')

class Movie(models.Model):
    title = models.CharField(max_length=200)
}}}

An Event can optionally include a movie screening, so we set null=True. A Screening is always associated with a Movie, so we have an implicit null=False.


We populate the database with the following instances: an event with a screening, and another event without a screening:

{{{
star_wars = Movie.objects.create(title=u'Star Wars')
first_screening = Screening.objects.create(movie=star_wars)
event_with_screening = Event.objects.create(screening=first_screening)
event_without_screening = Event.objects.create(screening=None)
}}}


Now consider the following queries and their results:

{{{
>>> Event.objects.values('screening__movie__pk')
[{'screening__movie__pk': 1}, {'screening__movie__pk': None}]

>>> Event.objects.values('screening__movie__title')
[{'screening__movie__title': u'Star Wars'}, {'screening__movie__title': None}]

>>> Event.objects.values('screening__movie__pk', 'screening__movie__title')
[{'screening__movie__title': u'Star Wars', 'screening__movie__pk': 1}]
}}}

Notice how the event without screening appears in the first two result sets but suddenly disappears in the last query? An inspection of django.db.connection.queries leads to the following surprising observation:

{{{
>>> connection.queries[-3]
{'time': '0.001', 'sql': u'SELECT ""app_screening"".""movie_id"" FROM ""app_event"" LEFT OUTER JOIN ""app_screening"" ON (""app_event"".""screening_id"" = ""app_screening"".""id"") LIMIT 21'}

>>> connection.queries[-2]
{'time': '0.000', 'sql': u'SELECT ""app_movie"".""title"" FROM ""app_event"" LEFT OUTER JOIN ""app_screening"" ON (""app_event"".""screening_id"" = ""app_screening"".""id"") LEFT OUTER JOIN ""app_movie"" ON (""app_screening"".""movie_id"" = ""app_movie"".""id"") LIMIT 21'}

>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT ""app_screening"".""movie_id"", ""app_movie"".""title"" FROM ""app_event"" LEFT OUTER JOIN ""app_screening"" ON (""app_event"".""screening_id"" = ""app_screening"".""id"") INNER JOIN ""app_movie"" ON (""app_screening"".""movie_id"" = ""app_movie"".""id"") LIMIT 21'}
}}}


In the first query we JOIN with the `screening` table only (and don't have to inspect the `movie` table at all) because we can already tell the result of `screening__movie__pk` by the referencing column Screening.movie_id. Also, we LEFT JOIN `event` with `screening` because the Event.screening field is null-able (the alternative would be an incorrect INNER JOIN). So everything works out all right.

In the second query we want to know about `screening__movie__title`, so we have to also JOIN with the `movie` table. Again, everything works out as expected: even though Screening.movie is not null-able, we have to use LEFT JOIN (not INNER JOIN) with the `movie` table because we do not want to exclude events that don't have a screening. So everything is all right in this case too.

But in the third query, Django unexpectedly changes the JOIN with `movie` from a LEFT JOIN to an INNER JOIN, and thereby dropping the event without a screening from the result set.


I assume this is a bug in how Django selects whether to use a LEFT JOIN vs. an INNER JOIN in foreign key lookups. The query is constructed as expected for the individual attributes `screening__movie__pk` and `screening__movie__title`, but Django seems to get confused when we want to have both values at once.

The same unexpected behavior can be observed with implicit foreign lookups, such as reverse references in one-to-one relations."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	join, values, nested, foreign key, null-able	Sebastian Goll	Ready for checkin	1	0	0	0	0	0