#31657: self referencing foreign key join and order_by issues
-----------------------------------------+------------------------
Reporter: Jack Delany | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
Initially discovered on 2.2.10 but verified still happens on 3.0.6. Given
the following models:
{{{
class OneModel(models.Model):
class Meta:
ordering = ("-id",)
id = models.BigAutoField(primary_key=True)
root = models.ForeignKey("OneModel", on_delete=models.CASCADE,
null=True)
oneval = models.BigIntegerField(null=True)
class TwoModel(models.Model):
id = models.BigAutoField(primary_key=True)
record = models.ForeignKey(OneModel, on_delete=models.CASCADE)
twoval = models.BigIntegerField(null=True)
}}}
The following queryset gives unexpected results and appears to be an
incorrect SQL query:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by("record__root_id")
print(qs.query)
}}}
{{{
SELECT "orion_twomodel"."id", "orion_twomodel"."record_id",
"orion_twomodel"."twoval" FROM "orion_twomodel" INNER JOIN
"orion_onemodel" ON ("orion_twomodel"."record_id" = "orion_onemodel"."id")
LEFT OUTER JOIN "orion_onemodel" T3 ON ("orion_onemodel"."root_id" =
T3."id") WHERE "orion_onemodel"."oneval" IN (1, 2, 3) ORDER BY T3."id"
DESC
}}}
The query has an unexpected DESCENDING sort. That appears to come from
the default sort order on the OneModel class, but I would expect the
order_by() to take prececence. The the query has two JOINS, which is
unnecessary. It appears that, since OneModel.root is a foreign key to
itself, that is causing it to do the unnecessary extra join. In fact,
testing a model where root is a foreign key to a third model doesn't show
the problem behavior.
Note also that the queryset with `order_by("record__root")` gives the
exact same SQL.
This queryset gives correct results and what looks like a pretty optimal
SQL:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by("record__root__id")
print(qs.query)
}}}
{{{
SELECT "orion_twomodel"."id", "orion_twomodel"."record_id",
"orion_twomodel"."twoval" FROM "orion_twomodel" INNER JOIN
"orion_onemodel" ON ("orion_twomodel"."record_id" = "orion_onemodel"."id")
WHERE "orion_onemodel"."oneval" IN (1, 2, 3) ORDER BY
"orion_onemodel"."root_id" ASC
}}}
So is this a potential bug or a misunderstanding on my part?
Another queryset that works around the issue and gives a reasonable SQL
query and expected results:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.annotate(root_id=F("record__root_id"))
qs = qs.order_by("root_id")
print(qs.query)
}}}
{{{
SELECT "orion_twomodel"."id", "orion_twomodel"."record_id",
"orion_twomodel"."twoval" FROM "orion_twomodel" INNER JOIN
"orion_onemodel" ON ("orion_twomodel"."record_id" = "orion_onemodel"."id")
WHERE "orion_onemodel"."oneval" IN (1, 2, 3) ORDER BY
"orion_onemodel"."zero_id" ASC
}}}
ASCENDING sort, and a single INNER JOIN, as I'd expect. That actually
works for my use because I need that output column anyway.
One final oddity; with the original queryset but the inverted sort
order_by():
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by("-record__root_id")
print(qs.query)
}}}
{{{
SELECT "orion_twomodel"."id", "orion_twomodel"."record_id",
"orion_twomodel"."twoval" FROM "orion_twomodel" INNER JOIN
"orion_onemodel" ON ("orion_twomodel"."record_id" = "orion_onemodel"."id")
LEFT OUTER JOIN "orion_onemodel" T3 ON ("orion_onemodel"."root_id" =
T3."id") WHERE "orion_onemodel"."oneval" IN (1, 2, 3) ORDER BY T3."id" ASC
}}}
One gets the query with the two JOINs but an ASCENDING sort order. I was
not under the impression that sort orders are somehow relative to the
class level sort order, eg: does specifing `order_by("-record__root_id")`
invert the class sort order? Testing that on a simple case doesn't show
that behavior at all.
Thanks for any assistance and clarification.
--
Ticket URL: <https://code.djangoproject.com/ticket/31657>
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/051.229ac4da8468f93df7b7380e956644aa%40djangoproject.com.
