#18375: F() doesn't work as expected across multijoin relations
-------------------------------------+-------------------------------------
Reporter: FunkyBob | Owner: akaariai
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Description changed by ramiro:
Old description:
> When trying to compare a field to another on the same related record, F()
> will compare against a separate alias of the table, thus not guaranteeing
> filtering against the same row.
>
> There doesn't appear to be anything in the docs or tests to indicate what
> the 'correct' behavior is. Also, there's no apparent way to control it.
>
> Using the attached models, I get the following:
>
> {{{
>
> (InteractiveConsole)
> >>> from sample import models
> >>> from django.db.models import Q, F
> >>> qset =
> models.Uber.objects.filter(unter__available__gt=F('unter__used'))
> >>> str(qset.query)
> 'SELECT "sample_uber"."id", "sample_uber"."title" FROM "sample_uber"
> INNER JOIN "sample_unter" ON ("sample_uber"."id" =
> "sample_unter"."uber_id") INNER JOIN "sample_unter" T3 ON
> ("sample_uber"."id" = T3."uber_id") WHERE T3."available" >
> "sample_unter"."used"'
> }}}
>
> The SQL nicely formatted shows:
> {{{
> SELECT "sample_uber"."id", "sample_uber"."title"
> FROM "sample_uber"
> INNER JOIN "sample_unter" ON ("sample_uber"."id" =
> "sample_unter"."uber_id")
> INNER JOIN "sample_unter" T3 ON ("sample_uber"."id" = T3."uber_id")
> WHERE T3."available" > "sample_unter"."used"
> }}}
>
> So, the F('unter__used') is using the 'sample_unter' join, whereas the
> unter__available__gt is using the T3 alias.
New description:
When trying to compare a field to another on the same related record, F()
will compare against a separate alias of the table, thus not guaranteeing
filtering against the same row.
There doesn't appear to be anything in the docs or tests to indicate what
the 'correct' behavior is. Also, there's no apparent way to control it.
Using the attached models, I get the following:
{{{
(InteractiveConsole)
>>> from sample import models
>>> from django.db.models import Q, F
>>> qset =
models.Uber.objects.filter(unter__available__gt=F('unter__used'))
>>> str(qset.query)
'SELECT "sample_uber"."id", "sample_uber"."title" FROM "sample_uber" INNER
JOIN "sample_unter" ON ("sample_uber"."id" = "sample_unter"."uber_id")
INNER JOIN "sample_unter" T3 ON ("sample_uber"."id" = T3."uber_id") WHERE
T3."available" > "sample_unter"."used"'
}}}
The SQL nicely formatted shows:
{{{
SELECT "sample_uber"."id", "sample_uber"."title"
FROM "sample_uber"
INNER JOIN "sample_unter" ON ("sample_uber"."id" =
"sample_unter"."uber_id")
INNER JOIN "sample_unter" T3 ON ("sample_uber"."id" = T3."uber_id")
WHERE T3."available" > "sample_unter"."used"
}}}
So, the `F('unter__used')` is using the 'sample_unter' join, whereas the
`unter__available__gt` is using the T3 alias.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/18375#comment:13>
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 django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
