#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
-------------------------------------+-------------------------------------
Changes (by sebastian):
* needs_better_patch: => 1
* has_patch: 0 => 1
* needs_tests: => 1
* needs_docs: => 0
Comment:
I am attaching a patch which ''might'' fix the problem. As I am not
familiar with the Django code base, I cannot assess any unintended
consequences this might have. The problem seems to have its cause in the
propagation of LEFT JOINs vs. INNER JOINs. This propagation, or promotion,
is done in `django/db/models/sql/query.py`, method `promote_alias` of
class `Query`.
According to the doc-string of the `promote_alias` method, any JOINs that
might refer to NULL values are promoted to LEFT JOIN (or `LOUTER`). But
the actual promotion of chains of JOINs seems to happen in the separate
method `promote_alias_chain` which makes sure that after one JOIN has been
promoted, all following JOINs are promoted as well.
Unfortunately, this does not work when parts of the JOINs have already
been introduced: `values('screening__movie__pk',
'screening__movie__title')` first creates the JOINs between `event` and
`screening`, and then (in a separate call to `promote_alias_chain`) the
chain `event`, `screening`, `movie`. As this second call does not promote
the first JOIN again, the last JOIN between `screening` and `movie`
remains an INNER JOIN, with the consequences mentioned above. (As a side
note, changing the argument order in the call to `values` to
`values('screening__movie__title', 'screening__movie__pk')` gives the
correct result, since now the first call to `promote_alias_chain` gets all
three joins.)
The patch I'm proposing adds another check to `promote_alias` to see if
the JOIN on the left-hand side of the current JOIN is LEFT OUTER. If so,
the current JOIN will become LEFT OUTER as well. This would make the
method `promote_alias_chain` more or less obsolete. Again, I'm not sure at
all if this has any unintended consequences, so please, please somebody
review this and tell me why it can't work, or tell me why it works. (For
instance, might it not be better to use column NULLABLE for this check,
and in turn set this column to True on any JOIN that we promote from INNER
JOIN to LEFT JOIN? I have no idea.)
Also, if this whole issue is really a bug (and not by design somehow), and
the proposed patch or some derivation of it can fix the problem, a test
case should be added to make sure something like this is not introduced
again.
--
Ticket URL: <https://code.djangoproject.com/ticket/16715#comment:1>
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.
