[Django] #19195: Using distinct([*fields]) filter on a foreign key produces an ordering error when the foreign key has a Meta ordering field.

Django

unread,

Oct 26, 2012, 6:41:53 PM10/26/12

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.
----------------------------------------------+----------------------------
Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords: distinct,
Triage Stage: Unreviewed | query
Easy pickings: 0 | Has patch: 0
| UI/UX: 0
----------------------------------------------+----------------------------
I tried to using a distinct filter like this:
Appearance.objects.order_by('team').distinct('team'); this fails with the
following Database Error: "DatabaseError: SELECT DISTINCT ON expressions
must match initial ORDER BY expressions"

It is possible to work around this problem by using this modified filter:
Appearance.objects.order_by('team__id').distinct('team__id').

Model definition: http://pastebin.com/index/J45fy9fr
Full traceback: http://pastebin.com/feSFMbzX

--
Ticket URL: <https://code.djangoproject.com/ticket/19195>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 26, 2012, 7:04:54 PM10/26/12

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

* needs_docs: => 0
* needs_better_patch: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted

Comment:

The problem is that .distinct('team') can't do distinct on
Team._meta.ordering as that will give unexpected results if the
Team._meta.ordering isn't unique. In general, the user doesn't want that
anyways. On the other hand we can't alter what .order_by('team') does. So,
I think we have to disallow doing .distinct('team') if there is ordering
defined for the related model.

The error should point to using .distinct('team_id').order_by('team_id') -
though this syntax doesn't seem to work at the moment.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:1>

Django

unread,

Oct 26, 2012, 7:22:44 PM10/26/12

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Comment (by chrisedgemon@…):

There should actually be two underscores for the team_id filter - order_by
fails on team_id.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:2>

Django

unread,

Oct 26, 2012, 7:26:25 PM10/26/12

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Comment (by akaariai):

I think we should allow using .order_by('team_id') here.

Related fields have two attributes on model level - team and team_id in
this case. We allow using team_id in many places in the ORM already, and
to me it seems we should allow it in order_by and distinct, too.

Is there some reason to *not* allow them?

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:3>

Django

unread,

Nov 15, 2013, 2:19:47 AM11/15/13

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 1.4

(models, ORM) | Resolution: wontfix

Changes (by anonymous):

* status: new => closed
* resolution: => wontfix

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:4>

Django

unread,

Nov 15, 2013, 2:21:38 AM11/15/13

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.4
(models, ORM) | Resolution:

Severity: Normal | Triage Stage: Accepted
Keywords: distinct, query | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by anonymous):

* status: closed => new
* resolution: wontfix =>

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

Django

unread,

Feb 13, 2014, 1:01:15 AM2/13/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Changes (by charettes):

* cc: charettes (added)

Old description:

> I tried to using a distinct filter like this:
> Appearance.objects.order_by('team').distinct('team'); this fails with the
> following Database Error: "DatabaseError: SELECT DISTINCT ON expressions
> must match initial ORDER BY expressions"
>
> It is possible to work around this problem by using this modified filter:
> Appearance.objects.order_by('team__id').distinct('team__id').
>
> Model definition: http://pastebin.com/index/J45fy9fr
> Full traceback: http://pastebin.com/feSFMbzX

New description:

I tried to using a distinct filter like this:
Appearance.objects.order_by('team').distinct('team'); this fails with the
following Database Error: "DatabaseError: SELECT DISTINCT ON expressions
must match initial ORDER BY expressions"

It is possible to work around this problem by using this modified filter:

`Appearance.objects.order_by('team__id').distinct('team__id')`.

Model definition: http://pastebin.com/index/J45fy9fr
Full traceback: http://pastebin.com/feSFMbzX

--

Comment:

Just hit this issue and had a hard time figuring out what I've done wrong.

Intuitively I tried `.distinct('related_id').order_by('related_id')` after
realizing removing my `Related._meta.ordering` solved the issue but, as
pointed out by akaariai, this is not allowed ATM.

Replying to [comment:3 akaariai]:

> I think we should allow using .order_by('team_id') here.
>
> Related fields have two attributes on model level - team and team_id in
this case. We allow using team_id in many places in the ORM already, and
to me it seems we should allow it in order_by and distinct, too.
>
> Is there some reason to *not* allow them?

I can't think of any reason we'd like *not* to allow them. It looks like
sanest to expose an API to explicitly opt-out of the existing related
model ordering behavior while maintaining backward compatibility.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:6>

Django

unread,

Apr 26, 2014, 3:48:00 AM4/26/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Comment (by charettes):

Calling `order_by('team_id')` doesn't raise a `FieldError` anymore on
Django 1.7 and silently behave just like `order_by('team')`.

If we want to avoid breaking code that might rely on this in 1.7 we should
fix it now. Else we'll have to deal with this by introducing an entry
point to `order_by` in order to opt-out of this unexpected behavior.

I wrote a patch for
[https://github.com/charettes/django/compare/ticket-19195-fk-order_by-
attname the order_by issue here].

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

Django

unread,

Apr 28, 2014, 8:21:22 AM4/28/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Comment (by akaariai):

Yes, I think we should do this for 1.7. Some review comments:
- The actual code changes look correct
- Release notes and some documentation changes for order_by() needed

The test changes looked a bit scary to me (do all existing tests continue
to test what they originally tested?), but after a bit more reading I
think they are safe. A final check here wouldn't hurt.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:8>

Django

unread,

Apr 28, 2014, 9:03:19 AM4/28/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version:
(models, ORM) | 1.7-beta-2
Severity: Release blocker | Resolution:
Keywords: distinct, query | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timo):

* version: 1.4 => 1.7-beta-2
* severity: Normal => Release blocker

Comment:

Setting release blocker flag per previous comment.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:9>

Django

unread,

Apr 28, 2014, 3:33:08 PM4/28/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version:
(models, ORM) | 1.7-beta-2
Severity: Release blocker | Resolution:
Keywords: distinct, query | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

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

Changes (by charettes):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/2614 Create a PR with doc and
release note]. I would appreciate a wording review.

In order to make sure the test refactoring didn't break anything I review
and moved every cases in their own methods. I you feel like this is adding
too much noise to the patch I can remove this part from the final patch.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:10>

Django

unread,

Apr 30, 2014, 11:40:22 AM4/30/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------

Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version:
(models, ORM) | 1.7-beta-2
Severity: Release blocker | Resolution:

Keywords: distinct, query | Triage Stage: Ready for
Has patch: 1 | checkin
Needs tests: 0 | Needs documentation: 0
Easy pickings: 0 | Patch needs improvement: 0
| UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timo):

* stage: Accepted => Ready for checkin

Comment:

Left some comments for doc improvements.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:11>

Django

unread,

Apr 30, 2014, 2:24:02 PM4/30/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version:
(models, ORM) | 1.7-beta-2

Severity: Release blocker | Resolution: fixed

Changes (by Simon Charette <charette.s@…>):

* status: new => closed

* resolution: => fixed

Comment:

In [changeset:"24ec9538b7ca400f68ba08fab380445ca95d7c02"]:
{{{
#!CommitTicketReference repository=""
revision="24ec9538b7ca400f68ba08fab380445ca95d7c02"
Fixed #19195 -- Allow explicit ordering by a relation `_id` field.

Thanks to chrisedgemon for the report and shaib, akaariai and
timgraham for the review.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:12>

Django

unread,

Apr 30, 2014, 2:27:15 PM4/30/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by Simon Charette <charette.s@…>):

In [changeset:"a6ecd5dbb34249f756a337c359eef1e8d78dc01e"]:
{{{
#!CommitTicketReference repository=""
revision="a6ecd5dbb34249f756a337c359eef1e8d78dc01e"
[1.7.x] Fixed #19195 -- Allow explicit ordering by a relation `_id` field.

Thanks to chrisedgemon for the report and shaib, akaariai and
timgraham for the review.

Backport of 24ec9538b7 from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:13>

Django

unread,

May 27, 2014, 12:11:06 PM5/27/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by Althalus):

I've found an issue with Django 1.7 checks framework and this ticket.
Now we're allowed to order by `fieldname` as before and by `fieldname_id`
(without joins).
But django/db/models/base.py `_check_ordering` method still does not allow
to set `fieldname_id` as one of Meta.ordering values.
I've made a patch here: https://github.com/Vincent-
Vega/django/commit/7d94a3d822746f2815e7184928311e1d91dff467

I'm quite new to contributing (and also django faq says that pull requests
without ticket are not accepted on github).
So it would be nice if you explain what can I do next.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:14>

Django

unread,

May 27, 2014, 12:22:54 PM5/27/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by charettes):

Hi Althalus, you should open a ticket for this and refer to it from your
pull request. This looks like a legitimate bug.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:15>

Django

unread,

May 27, 2014, 1:36:53 PM5/27/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by Althalus):

I think it's worth noting here that I've created #22711.

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:16>

Django

unread,

Jun 1, 2014, 3:38:40 PM6/1/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by Simon Charette <charette.s@…>):

In [changeset:"d04e7302240f5be34cdd303002bc8e7dcd81f529"]:
{{{
#!CommitTicketReference repository=""
revision="d04e7302240f5be34cdd303002bc8e7dcd81f529"
Fixed #22711 -- Adjusted ordering checks to allow implicit relation
fields.

refs #19195.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19195#comment:17>

Django

unread,

Jun 1, 2014, 3:38:49 PM6/1/14

to django-...@googlegroups.com

#19195: Using distinct([*fields]) filter on a foreign key produces an ordering
error when the foreign key has a Meta ordering field.

-------------------------------------+-------------------------------------
Reporter: chrisedgemon@… | Owner: nobody

Comment (by Simon Charette <charette.s@…>):

In [changeset:"d773a08b270e3b2c387985a9d9a4d01c991469c8"]:
{{{
#!CommitTicketReference repository=""
revision="d773a08b270e3b2c387985a9d9a4d01c991469c8"
[1.7.x] Fixed #22711 -- Adjusted ordering checks to allow implicit
relation fields.