id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34133	Django ordering in model meta causing unexpected results of group_by	Szymon Sobczak	nobody	"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."	Uncategorized	new	Database layer (models, ORM)	3.2	Normal				Unreviewed	0	0	0	0	0	0