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

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Dave Gaeddert):

* has_patch: 0 => 1

Comment:

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
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:2>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

Comment:

I'd be open to a 6.1 backport as a "crashing bug", but open to more
opinions there. In which case, we'd add back the release note.
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:3>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_docs: 0 => 1
* stage: Ready for checkin => Accepted

Comment:

Dave, if you can, please re-add the release note to 6.1.1.txt. Otherwise I
can do it tomorrow before the release. Thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:4>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* severity: Normal => Release blocker

--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:5>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Dave Gaeddert):

Hey Jacob, thanks for picking this back up! Unfortunately my computer bit
the dust today, so I'm scrambling to get a new one. I doubt I'll be able
to spend any time on this before your release. I saw your comment about
the other tickets also — I'll see if I can work on that once I'm back up
and going!
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:6>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_docs: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:7>

[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 | Owner: Dave
| Gaeddert

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"00dca6f097f443de7a24c04bc133f98c83c632aa" 00dca6f0]:
{{{#!CommitTicketReference repository=""
revision="00dca6f097f443de7a24c04bc133f98c83c632aa"
Fixed #37222 -- Fixed QuerySet.distinct() crash on duplicated selections.

When two lookup paths resolve to the same column, values() selects that
column once per path, at a different position each time. get_distinct()
refers to such selections by expression, and PostgreSQL binds an
expression reference to the first position the expression is selected
at. Ordering referred to each selection by its own position, so the
later ones yielded sort keys that DISTINCT ON could not be matched
against:

SELECT DISTINCT ON expressions must match initial ORDER BY
expressions

Ordering now refers to the first position an expression is selected at
when distinct fields are used. Annotations take part in that, as
PostgreSQL binds to their position as well when they select an
expression first, but their alias keeps referring to their own position
since get_distinct() refers to annotations by alias. Raw selections are
left out as equal SQL is not necessarily interchangeable, e.g. two
volatile extra() selections must keep ordering by their own position.

Regression in 65ad4ade74dc9208b9d686a451cd6045df0c9c3a.

Thanks Robert Leach for the report and Simon Charette for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:8>

[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 | Owner: Dave
| Gaeddert
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"72415689785fdf68238a288e71338a2f7b8deb2a" 7241568]:
{{{#!CommitTicketReference repository=""
revision="72415689785fdf68238a288e71338a2f7b8deb2a"
[6.1.x] Fixed #37222 -- Fixed QuerySet.distinct() crash on duplicated

selections.

When two lookup paths resolve to the same column, values() selects that
column once per path, at a different position each time. get_distinct()
refers to such selections by expression, and PostgreSQL binds an
expression reference to the first position the expression is selected
at. Ordering referred to each selection by its own position, so the
later ones yielded sort keys that DISTINCT ON could not be matched
against:

SELECT DISTINCT ON expressions must match initial ORDER BY
expressions

Ordering now refers to the first position an expression is selected at
when distinct fields are used. Annotations take part in that, as
PostgreSQL binds to their position as well when they select an
expression first, but their alias keeps referring to their own position
since get_distinct() refers to annotations by alias. Raw selections are
left out as equal SQL is not necessarily interchangeable, e.g. two
volatile extra() selections must keep ordering by their own position.

Regression in 65ad4ade74dc9208b9d686a451cd6045df0c9c3a.

Thanks Robert Leach for the report and Simon Charette for the review.

Backport of 00dca6f097f443de7a24c04bc133f98c83c632aa from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37222#comment:9>