[Django] #37222: QuerySet.distinct(*fields) with order_by() and values() crashes on PostgreSQL when two lookup paths resolve to the same column

[Django]

#37222: QuerySet.distinct(*fields) with order_by() and values() crashes on
PostgreSQL when two lookup paths resolve to the same column
-------------------------------------+-------------------------------------
Reporter: Dave Gaeddert | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/37222>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.