#34450: Lookup expressions across foreign keys introduce extra joins
-------------------------------------+-------------------------------------
Reporter: Roman | Owner: nobody
Odaisky |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Assuming Child has a ForeignKey to Parent,
{{{
Parent.objects.filter(child__x="x", child__y="y")
}}}
results in
{{{
SELECT ...
FROM "app_parent"
INNER JOIN "app_child" ON ("app_parent"."id" = "app_child"."parent_id")
WHERE ("app_child"."x" = ('x') AND "app_child"."y" = ('y'))
}}}
which makes perfect sense.
However,
{{{
Parent.objects.filter(Exact(F("child__x"), "x"), child__y="y")
}}}
unexpectedly produces
{{{
SELECT ...
FROM "app_parent"
LEFT OUTER JOIN "app_child" ON ("app_parent"."id" =
"app_child"."parent_id")
INNER JOIN "app_child" T3 ON ("app_parent"."id" = T3."parent_id")
WHERE ("app_child"."x" = ('x') AND T3."y" = ('y'))
}}}
basically converting an AND lookup into an OR, which can’t be correct.
Same error here:
{{{
Parent.objects.filter(Exact(F("child__x"), "x") & Q(child__y="y"))
}}}
But these work correctly:
{{{
Parent.objects.filter(Q(child__y="y") & Exact(F("child__x"), "x"))
Parent.objects.filter(Exact(F("child__x"), "x") & Exact(F("child__y"),
"y"))
}}}
It would seem that all of the above should produce the same SQL as the
first invocation instead of introducing extra joins that quietly turn ANDs
into ORs, and most definitely there should be no dependence on the order
of the operands.
--
Ticket URL: <https://code.djangoproject.com/ticket/34450>
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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018735881aa6-3f0d6e92-35f1-43ca-815d-a0f77038dfe9-000000%40eu-central-1.amazonses.com.
