QuerySet.get() ignores order_by in DISTINCT ON queries

[Peter De Wachter]

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')

[Simon Charette]

Managed to reproduce against master. The culprit seems to be ​an ordering clearing when limits have been set.

I'm actually running the test suite with those lines removed since I can't think of a reason this is required.

e.g. Why do ordering should be cleared in this case

Foo.objects.order_by('bar').get()

But not this one

Foo.objects.order_by('bar')[10:20].get()

[Simon Charette]

It looks like latest and earliest rely on this behavior.

@pdewacht, your proposed patch makes sense. Please submit a PR with tests.

[Peter De Wachter]

Patch with unit test

[Simon Charette]

WIll turn this into a PR.

[Simon Charette]

Created a PR.

[Simon Charette <charette.s@…>]

In 07577a2d:

Fixed #25081 -- Prevented DISTINCT ON ordering from being cleared in get().

Thanks to pdewacht for the patch.