QuerySet.distinct(*fields) with order_by() and values() crashes on PostgreSQL when two lookup paths resolve to the same column

[Dave Gaeddert]

On PostgreSQL, passing the same fields to order_by() / distinct() / values_list() crashes when two of the lookup paths resolve to the same column:

class Tracer(models.Model):
    name = models.CharField(max_length=100)


class Infusate(models.Model):
    tracers = models.ManyToManyField(Tracer, through="InfusateTracer")


class InfusateTracer(models.Model):
    infusate = models.ForeignKey(Infusate, models.CASCADE, related_name="tracer_links")
    tracer = models.ForeignKey(Tracer, models.CASCADE)
    concentration = models.FloatField()


# The M2M shortcut and its through model reach the same column.
fields = ["tracer_links__tracer__name", "tracers__name", "tracer_links__concentration"]
Infusate.objects.order_by(*fields).distinct(*fields).values_list(*fields)

django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Works on 4.2, 5.0, and 5.1; crashes on 5.2, 6.0, and main. Bisects to 65ad4ade74dc9208b9d686a451cd6045df0c9c3a (refs #28900), which made ordering refer to values() selections by select position.

The duplicated column is selected at two positions and ORDER BY 1 ASC, 2 ASC, 3 ASC refers to both — but PostgreSQL binds each DISTINCT ON expression to the first position it is selected at, so position 2 falls outside the DISTINCT ON set and the query is rejected. Before 5.2, ordering compiled expressions instead of positions and the duplicate collapsed through the existing deduplication.

Originally reported by Robert Leach on the forum:
​https://forum.djangoproject.com/t/but-in-django-5-2-when-joining-the-same-table-twice-and-using-order-by-and-distinct-on/45441

Possibly an earlier sighting: #35958 (closed worksforme without a reproducer).

I have code here that I can probably just update and point towards django/django if accepted:
​https://github.com/davegaeddert/django/pull/3

(AI assistance: Claude Code was used to reduce the reproducer, bisect, and draft the patch; I verified the reproducer, the patch, and the test results against PostgreSQL 16.)

[Simon Charette]

Thanks for this investigation and filling this ticket Dave!

Ordering twice by the same field (or passing it to distinct) is likely a user error in the first place but given this was working fine before a refactor that unintentionally broke it I believe it's worth fixing as the error message can be quite hard to trace back to the problem (as the forum thread supports).

It's interesting that the problem only manifest itself when referring the same expression through different aliases. Another way to reproduce is by using the local _id and remote __id reference of a foreign key which might be a more common way users run into this:

fields = ["tracer_id", "tracer__id", "tracer__name"]
list(InfusateTracer.objects.order_by(*fields).distinct(*fields).values_list(*fields))

Your proposed changes are looking promising so please open a pull request against main referencing this ticket and ​follow the contributing docs.

[Dave Gaeddert]

Thanks Simon — good point on __id and _id, I went ahead and added a test for that specifically.

​https://github.com/django/django/pull/21659