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
37222	QuerySet.distinct(*fields) with order_by() and values() crashes on PostgreSQL when two lookup paths resolve to the same column	Dave Gaeddert	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:

{{{#!python
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.)"	Bug	assigned	Database layer (models, ORM)	5.2	Normal				Accepted	0	0	0	0	0	0