#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.229ac4da8468f93df7b7380e956644aa%40djangoproject.com.
