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
25081	QuerySet.get() ignores order_by in DISTINCT ON queries	Peter De Wachter	Simon Charette	"The following query, using the PostgreSQL-specific DISTINCT ON, is mistranslated:

{{{
Model.objects.order_by('key', '-last_updated').distinct('key').get(key=x)
}}}

This generated this SQL query:

{{{
SELECT DISTINCT ON (""bug_mymodel"".""key"")
   ""bug_mymodel"".""id"", ""bug_mymodel"".""key"", ""bug_mymodel"".""last_updated"", ""bug_mymodel"".""text""
FROM ""bug_mymodel""
WHERE ""bug_mymodel"".""key"" = 'xyz'
}}}

The ORDER BY request is omitted from the query, but DISTINCT ON is meaningless without sort order. Note that it is correct to use get() in this case, as it is guaranteed that there is only one result.

We expected the following SQL:

{{{
SELECT DISTINCT ON (""bug_mymodel"".""key"")
   ""bug_mymodel"".""id"", ""bug_mymodel"".""key"", ""bug_mymodel"".""last_updated"", ""bug_mymodel"".""text""
FROM ""bug_mymodel""
WHERE ""bug_mymodel"".""key"" = 'xyz'
ORDER BY ""bug_mymodel"".""key"" ASC, ""bug_mymodel"".""last_updated"" DESC
}}}

Full test case:

{{{
class MyModel(models.Model):
    key     = models.CharField(max_length=100)
    last_updated = models.DateTimeField(auto_now=True)
    text         = models.CharField(max_length=100)

def test():
    return MyModel.objects.order_by('key', '-last_updated').distinct('key').get(key='xyz')
}}}

"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed		Simon Charette	Ready for checkin	1	0	0	0	0	0