#34133 closed Uncategorized (duplicate)

Django ordering in model meta causing unexpected results of group_by

Reported by: Szymon Sobczak Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Szymon Sobczak)

For the following model:

class Product(models.Model):
   name = models.CharField()
   category = models.CharField()
   price = models.IntegerField()

   class Meta:
      ordering = ("name",)

Given the following products:

Product.objects.create(name="Milk", category="Food", price=5)
Product.objects.create(name="Cookies", category="Food", price=7)

Product.objects.create(name="Table", category="Furniture", price=100)

I want to calculate average product price per category:

qs = Product.objects.values("category").annotate(avg_price=Avg("price"))

I expect to see a result like:

[
   {"category": "Food", "avg_price": 6},
   {"category": "Furniture", "avg_price": 100},
]

but I actually get:

[
   {"category": "Food", "avg_price": 5},
   {"category": "Food", "avg_price": 7},
   {"category": "Furniture", "avg_price": 100},
]

That's because the column "name" from model's ordering in Meta is automatically added to the GROUP BY part of the SQL, which causes the category "Food" to be duplicated in the result:

qs = Product.objects.values("category").annotate(avg_price=Avg("price"))
print(qs.query)

> SELECT 
>    "x"."category", 
>    AVG("x"."price") AS "avg_price" 
> FROM "x_product" 
> GROUP BY 
>    "x_product"."category", 
>    "x_product"."name" -- UNEXPECTED!

I know as a workaround I can "force" an empty order_by() on the queryset:

qs = Product.objects.order_by().values("category").annotate(avg_price=Avg("price"))

Remembering about this every time I run values().annotate() is very error-prone. This is the second time I'm debugging this issue in my current project.

I think the default ordering (from model Meta) should be cleared whenever SQL GROUP BY is generated by the query.

Change History (2)

comment:1 by Szymon Sobczak, 17 months ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 17 months ago

Resolution: duplicate
Status: newclosed

Duplicate of #32546, fixed in 330bc402a8d2d8f23cf2e07d9dabf333003677d3 (Django 4.0+).

Note: See TracTickets for help on using tickets.
Back to Top