#33682: SQL generation bug in `.distinct()` when supplied fields go through
multiple many-related tables
-------------------------------------+-------------------------------------
Reporter: Robert Leach | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sql, distinct, | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
The `QuerySet.distinct` and `order_by` methods
[https://docs.djangoproject.com/en/4.0/ref/models/querysets/#django.db.models.query.QuerySet.order_by
don't resolve references to foreign key the same way] when referenced
models define a `Meta.ordering`.
You can get a similar crash with way less code by doing
{{{#!python
list(TestSynonym.objects.distinct('compound').order_by('compound'))
}}}
As the above actually translates to
{{{#!python
list(TestSynonym.objects.distinct('compound').order_by('compound__name'))
}}}
Due to `TestCompound.Meta.ordering = ('name',)`
------
I would argue that this is ''invalid'' but I'm curious to hear what others
have to say as the resulting error is definitely cryptic there's possibly
ways we could do things better.
Given `DISTINCT ON` usage requires a matching `ORDER BY` I see three
options
1. Do nothing, users should learn the gotchas of `Meta.ordering` before
using it. We've got a precedent against that by making aggregations ignore
`Meta.ordering` in the recent versions.
2. Make `distinct('related_with_ordering')` behave like `order_by` with
regards to ordering expansion to make both APIs coherent given they a
dependent (resulting clause could match). This will require a deprecation
period and spreads the arguable bad related ordering expansion pattern to
another method.
3. Refuse the temptation to guess and make
`distinct('related_with_ordering')` error out loudly so at least users are
not presented this cryptic error. This maintains the arguably confusing
mismatch between both APIs but we stop the spread of `ordering` expansion.
--
Ticket URL: <https://code.djangoproject.com/ticket/33682#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/010701809761d05f-54fbd00f-8188-4a52-8af0-b6693aba8e3b-000000%40eu-central-1.amazonses.com.
