#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)
Resolution: | Severity: Normal
Triage Stage: | Keywords: join, values,
Unreviewed | nested, foreign key, null-able
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
UI/UX: 0 | Easy pickings: 0
-------------------------------------+-------------------------------------
Comment (by sebastian):
On a related note, here is something that the patch above doesn't fix.
Maybe it is related to the original problem, maybe it is entirely
unrelated. First we observe that `select_related` instead of `values`
works as expected, even without the proposed patch:
{{{
>>> Event.objects.select_related('screening', 'screening__movie')
[<Event: Event object>, <Event: Event object>]
>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT "app_event"."id",
"app_event"."screening_id", "app_screening"."id",
"app_screening"."movie_id", "app_movie"."id", "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'}
}}}
Both joins are LEFT JOINs and both events (with and without screening) get
selected.
However, if we change the (forward) foreign key reference Event.screening
to an implicit reverse one, such as introduced by a one-to-one relation,
e.g. in multi-table inheritance, the wrong JOIN is selected. Let's assume
the new model looks like this:
{{{
class Event(models.Model):
pass
class Screening(Event):
movie = models.ForeignKey('Movie')
class Movie(models.Model):
title = models.CharField(max_length=200)
}}}
We fill it with data:
{{{
star_wars = Movie.objects.create(title=u'Star Wars')
event_with_screening = Screening.objects.create(movie=star_wars)
event_without_screening = Event.objects.create()
}}}
Now we run the same query as above:
{{{
>>> Event.objects.select_related('screening', 'screening__movie')
[<Event: Event object>]
>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT "app_event"."id",
"app_screening"."event_ptr_id", "app_screening"."movie_id",
"app_movie"."id", "app_movie"."title" FROM "app_event" LEFT OUTER JOIN
"app_screening" ON ("app_event"."id" = "app_screening"."event_ptr_id")
INNER JOIN "app_movie" ON ("app_screening"."movie_id" = "app_movie"."id")
LIMIT 21'}
}}}
Again, as in the original report with `values`, only the event with a
screening (or in this case, the event that is also a screening) is
selected because `screening` is JOINed with `movie` using an INNER JOIN
instead of the correct LEFT JOIN.
It seems that in this case, `promote_alias` is not called at all. So,
maybe, changing this method is not the right place to fix the original
issue after all … Again, maybe somebody with more insight into the Django
codebase can help?
--
Ticket URL: <https://code.djangoproject.com/ticket/16715#comment:2>
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.
