#34133: Django ordering in model meta causing unexpected results of group_by
-------------------------------------+-------------------------------------
Reporter: ssobczak | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by ssobczak:
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34133#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070184393e5581-5d7b9724-164a-48de-98d0-3c1bfea8c081-000000%40eu-central-1.amazonses.com.
