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