id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31687	Subquery loses ordering when passed to Func even if Func needs ordering.	john-parton	nobody	"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.

"	Bug	closed	Database layer (models, ORM)	3.0	Normal	invalid			Unreviewed	0	0	0	0	0	0