Code

Opened 18 months ago

Last modified 2 months ago

#19195 new Bug

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

Reported by: chrisedgemon@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: distinct, query
Cc: charettes Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by charettes)

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

Attachments (0)

Change History (6)

comment:1 Changed 18 months ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:2 Changed 18 months ago by chrisedgemon@…

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

comment:3 Changed 18 months ago 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?

comment:4 Changed 5 months ago by anonymous

  • Resolution set to wontfix
  • Status changed from new to closed

comment:5 Changed 5 months ago by anonymous

  • Resolution wontfix deleted
  • Status changed from closed to new

comment:6 Changed 2 months ago by charettes

  • Cc charettes added
  • Description modified (diff)

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.