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
32658	Subquery ignores query ordering	Bálint Balina	nobody	"I've started migrating a huge project from Django==2.2 to ==3.2, and many tests started to fail, because of the problem below:

When using a query with ordering in an annotated Subquery, the query's ordering is ignored (or removed).

In a bare new Django project, I have successfully reproduced the issue:

{{{
from django.db import models


class MyModel(models.Model):
    parent = models.ForeignKey(to='self', on_delete=models.CASCADE, null=True)
    name = models.TextField()
}}}


{{{
MyModel.objects.create(name='parent')
MyModel.objects.create(name='b', parent=parent)
MyModel.objects.create(name='a', parent=parent)
}}}


{{{
children_query = MyModel.objects.filter(parent=OuterRef('pk')).annotate(child_names=Func(F('name'), function='group_concat')).values('child_names').order_by('name')
parent_query = MyModel.objects.annotate(child_names=Subquery(children_query))
parent_query.query.__str__()
}}}
Outputs:

{{{
SELECT ""app_mymodel"".""id"", ""app_mymodel"".""parent_id"", ""app_mymodel"".""name"", (SELECT group_concat(U0.""name"") AS ""child_names"" FROM ""app_mymodel"" U0 WHERE U0.""parent_id"" = ""app_mymodel"".""id"") AS ""child_names"" FROM ""app_mymodel""
}}}

The only thing I do, is pip install Django==2.2, run the same thing in a shell and:

Outputs:

{{{
SELECT ""app_mymodel"".""id"", ""app_mymodel"".""parent_id"", ""app_mymodel"".""name"", (SELECT group_concat(U0.""name"") AS ""child_names"" FROM ""app_mymodel"" U0 WHERE U0.""parent_id"" = (""app_mymodel"".""id"") ORDER BY U0.""name"" ASC) AS ""child_names"" FROM ""app_mymodel""
}}}
Notice the  ORDER BY U0.""name"" ASC is missing in Django 3.2, though I have explicitly set the ordering on the query. This happens both on SQLite and PostgreSQL backends.

"	Uncategorized	closed	Database layer (models, ORM)	3.2	Normal	invalid	Subquery, annotations, ordering	Simon Charette Hannes Ljungberg	Unreviewed	0	0	0	0	0	0