#31687: Subquery loses ordering when passed to Func even if Func needs ordering
-------------------------------------+-------------------------------------
Reporter: john- | Owner: nobody
parton |
Type: Bug | Status: new
Component: Database | Version: 3.0
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 |
-------------------------------------+-------------------------------------
Description of Problem:
When passing a Subquery object to a Func object with an order by clause
specified, the order by clause is often dropped, even when it might be
significant.
Here's a permalink to the relevant code on master:
https://github.com/django/django/blob/678c8dfee458cda77fce0d1c127f1939dc134584/django/db/models/sql/query.py#L1029-L1034
Here's the snippet from the resolve_expression method of
django.db.models.sql.query.Query
{{{#!python
# It's safe to drop ordering if the queryset isn't using slicing,
# distinct(*fields) or select_for_update().
if (self.low_mark == 0 and self.high_mark is None and
not self.distinct_fields and
not self.select_for_update):
clone.clear_ordering(True)
}}}
Here's some example code:
{{{#!python
class Author(models.Model):
name = models.CharField(max_length=50)
alias = models.CharField(max_length=50, null=True, blank=True)
age = models.PositiveSmallIntegerField(default=30)
class Article(models.Model):
authors = models.ManyToManyField(Author, related_name='articles')
title = models.CharField(max_length=50)
print(Article.objects.annotate(
authors_by_age=Func(
Subquery(
Author.objects.filter(articles__id=OuterRef('id')).order_by('age').values('name')
), function='ARRAY'
)
).query)
}}}
{{{#!sql
SELECT "example_article"."id", "example_article"."title", ARRAY((SELECT
U0."name" FROM "example_author" U0 INNER JOIN "example_article_authors" U1
ON (U0."id" = U1."author_id") WHERE U1."article_id" =
"example_article"."id")) AS "authors_by_age" FROM "example_article"
}}}
ARRAY would be a Postgres-specific function in this case.
I couldn't find this behavior documented anywhere. I only figured it out
by digging through the code.
Possible Solutions:
Right now, I either have to supply a distinct field as a hack, monkey
patch the query object, or write raw sql. Obviously none of those are
great.
For my use-case, I would prefer for Django to just trust that I know when
ordering is necessary and to leave it alone.
--
Ticket URL: <https://code.djangoproject.com/ticket/31687>
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/054.68862f4699744c1685f0e6ac2aedab6e%40djangoproject.com.
