#16715: Wrong JOIN with nested null-able foreign keys
-------------------------------------+-------------------------------------
Reporter: sebastian | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: 1.3 | (models, ORM)
Keywords: join, values, nested, | Severity: Normal
foreign key, null-able | Triage Stage: Unreviewed
Has patch: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16715>
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 post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
