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
24462	Add a new QuerySet operation to use current results as a subquery	trosos	nobody	"When I try to order a queryset that uses {{{distinct(...)}}}, e.g.:
{{{
#!python
qs2 = qs.order_by('a', 'b').distinct('a')
qs3 = qs2.order_by('c')
}}}
then Django seems to forget about the first {{{order_by}}} and apply the second {{{order_by}}} before applying {{{distinct}}}. I would expect the second {{{order_by}}} to be applied after applying {{{distinct}}}.

This is particularly troublesome when one wants to create a custom manager on top of {{{qs2}}}.

I don't know if this is the intended behavior, but it is very unexpected to me (see the section ""Rationale"").

=== Steps to reproduce ===

Use the Postgre SQL backend.

In models.py:
{{{
#!python
from django.db import models

class YoungestAmongNamesakes(models.Manager):
    def get_queryset(self):
        return super(YoungestAmongNamesakes, self).get_queryset().\
          order_by('name', 'age').distinct('name')

class Human(models.Model):
    nick = models.CharField(max_length=20)
    name = models.CharField(max_length=20)
    age = models.IntegerField()

    objects = models.Manager()
    youngest_among_namesakes = YoungestAmongNamesakes()
}}}

In the interactive shell:
{{{
#!python
Human.objects.create(nick='foo', name='Helen', age=20)
Human.objects.create(nick='bar', name='Helen', age=23)
Human.objects.create(nick='baz', name='Jennifer', age=15)

for human in Human.youngest_among_namesakes.all():
    print human.nick

for human in Human.youngest_among_namesakes.order_by('name', 'nick'):
    print human.nick

for human in Human.youngest_among_namesakes.order_by('nick'):
    print human.nick
}}}

=== Actual results ===

The first iteration outputs:
{{{
foo
baz
}}}

The second iteration outputs:
{{{
bar
baz
}}}

The third iteration raises
{{{
ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
}}}

{{{Human.youngest_among_namesakes.all()}}} generates:
{{{
SELECT DISTINCT ON (""myapp_human"".""name"") ""myapp_human"".""id"", ""myapp_human"".""nick"", ""myapp_human"".""name"", ""myapp_human"".""age"" FROM ""myapp_human"" ORDER BY ""myapp_human"".""name"" ASC, ""myapp_human"".""age"" ASC
}}}

{{{Human.youngest_among_namesakes.order_by('name', 'nick')}}} generates:
{{{
SELECT DISTINCT ON (""myapp_human"".""name"") ""myapp_human"".""id"", ""myapp_human"".""nick"", ""myapp_human"".""name"", ""myapp_human"".""age"" FROM ""myapp_human"" ORDER BY ""myapp_human"".""name"" ASC, ""myapp_human"".""nick"" ASC
}}}

Similarly, {{{Human.youngest_among_namesakes.order_by('nick')}}} generates:
{{{
SELECT DISTINCT ON (""myapp_human"".""name"") ""myapp_human"".""id"", ""myapp_human"".""nick"", ""myapp_human"".""name"", ""myapp_human"".""age"" FROM ""myapp_human"" ORDER BY ""myapp_human"".""nick"" ASC
}}}

=== Expected results ===

I would expect the second iteration to output:
{{{
foo
baz
}}}

I would expect the third iteration to output:
{{{
baz
foo
}}}

I would expect {{{Human.youngest_among_namesakes.order_by('name', 'nick')}}} to generate the following query:
{{{
SELECT ""myapp_human"".""id"", ""myapp_human"".""nick"", ""myapp_human"".""name"", ""myapp_human"".""age"" FROM ""myapp_human"" WHERE ""myapp_human"".""id"" IN (SELECT DISTINCT ON (""myapp_human"".""name"") ""myapp_human"".""id"" FROM ""myapp_human"" ORDER BY ""myapp_human"".""name"" ASC, ""myapp_human"".""age"" ASC) ORDER BY ""myapp_human"".""name"" ASC, ""myapp_human"".""nick"" ASC
}}}

Similarly, I would expect {{{Human.youngest_among_namesakes.order_by('nick')}}} to generate the following query:
{{{
SELECT ""myapp_human"".""id"", ""myapp_human"".""nick"", ""myapp_human"".""name"", ""myapp_human"".""age"" FROM ""myapp_human"" WHERE ""myapp_human"".""id"" IN (SELECT DISTINCT ON (""myapp_human"".""name"") ""myapp_human"".""id"" FROM ""myapp_human"" ORDER BY ""myapp_human"".""name"" ASC, ""myapp_human"".""age"" ASC) ORDER BY ""myapp_human"".""nick"" ASC
}}}

=== Rationale ===

I would expect any queryset {{{qs}}} to contain the same objects as {{{qs.order_by(...)}}}, just in a possibly different order.

=== Workaround ===

As a (suboptimal) workaround, one might use a subquery instead:

{{{
#!python
class YoungestAmongNamesakes(models.Manager):
    def get_queryset(self):
        qs = super(YoungestAmongNamesakes, self).get_queryset().\
          order_by('name', 'age').distinct('name')
        return super(YoungestAmongNamesakes, self).get_queryset().filter(pk__in=qs)
}}}

This however generates unnecessarily complex SQL query for {{{Human.youngest_among_namesakes.all()}}}.

=== Suggestion ===

I would suggest to rewrite {{{django.db.models.query.QuerySet.order_by}}} not to unconditionally clear the previous ordering, but to first check whether {{{distinct(...)}}} is used in the present queryset and use a subquery in such a case.

Something like this (the code is untested and written quickly just to document my thought):

{{{
#!python
    def order_by(self, *field_names):
        """"""
        Returns a new QuerySet instance with the ordering changed.
        """"""
        assert self.query.can_filter(), \
            ""Cannot reorder a query once a slice has been taken.""
        obj = self._clone()

        if self.query.distinct and self.query.distinct_fields:
            from django.db.models.sql.subqueries import AggregateQuery
            subquery = obj

            obj = AggregateQuery(obj.model)
            try:
                obj.add_subquery(subquery, using=self.db)
            except EmptyResultSet:
                obj = subquery
        else:
            obj.query.clear_ordering(force_empty=False)

        obj.query.add_ordering(*field_names)
        return obj
}}}"	New feature	new	Database layer (models, ORM)	dev	Normal		distinct order_by subquery	mihadra@… trosos John Speno Jameel A. Paolo Melchiorre Dave Johansen Stephen ldeluigi	Accepted	0	0	0	0	0	0