Opened 6 years ago

Closed 6 years ago

Last modified 3 years ago

#10574 closed (fixed)

Remove unnecessary ordering in values() queries

Reported by: mtredinnick Owned by: mtredinnick
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

The ORM applies any ordering (such as default ordering given by Meta.ordering) all the time. For this model:

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

This is redundant in values() queries like this:

Item.objects.values("data")

Even worse, it leads to incorrect results in aggregate queries like this one:

Item.objects.values("data").annotate(Count("id"))

Normally, that would return the number of entries for each data value. However, due to the ordering by name being included, we group by (data, name), which affects the result. The solution is to realise that the ordering columns aren't going to feature in the result and remove them.

It can be worked around, once you realise the problem, although that last part isn't easy (it took me more than a few minutes):

Items.objects.values("data").annotate(Count("id")).order_by()

but Django has enough information to work this out itself.

(I'm going to put this in the aggregation category, since that's where the real apparent bug emerges and people will be looking there for similar things, I guess.)

Change History (5)

comment:1 Changed 6 years ago by mtredinnick

  • milestone set to 1.1
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted
  • Version set to SVN

comment:2 Changed 6 years ago by mtredinnick

I'm going to document our way out of this one so that Django's behaviour is consistent (if not brilliant). We never remove ordering constraints internally, it's always up to the user.

comment:3 Changed 6 years ago by mtredinnick

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

(In [10172]) Fixed #10574 -- Documented interaction between annotations and order_by.

In the future, I'd like to fix this properly, but the current behavior
has the advantage of being consistent across the board (and changing it
everywhere is backwards-incompatible with documented functionality).

comment:4 Changed 4 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

comment:5 Changed 3 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top