GROUP BY generation and extra seems to be too greedy

[Brian Rosner]

I may not understand the ORM code fully, but this seems like wrong behavior. Take the following queryset:

queryset = channel.message_set.all().values(
   "nickname",
).annotate(
   message_count = models.Count("id"),
).extra(select={
    "percentage": "FLOOR((COUNT(*) / %d.0) * 100)" % channel.message_set.count(),
}).order_by("-message_count")

I am seeing the following query generated:

SELECT (FLOOR((COUNT(*) / 1105908.0) * 100)) AS "percentage",
       "irc_message"."nickname", COUNT("irc_message"."id") AS "message_count"
FROM "irc_message"
WHERE "irc_message"."channel_id" = 3
GROUP BY "irc_message"."nickname",
         FLOOR((COUNT(*) / 1105908.0) * 100)
ORDER BY message_count DESC LIMIT 10

As you can see the expression from extra(select=) is sneaking into the GROUP BY clause. Tracking this down led to #10132 which seems to introduce this new behavior (even though I don't know if things worked before [9838]).

[Brian Rosner]

After thinking about this a bit more it does appear to be doing the most correct thing. My specific use-case was able to do done via a custom aggregate. I'll leave open just in-case there was something overlooked.

[Russell Keith-Magee]

Your thinking is correct. Any extra(select=) columns are assumed to be non-aggregates, and are therefore included in the GROUP BY; it is assumed that if you need an aggregate in the extra, you can use annotate() and a custom aggregate.

[Jacob]

Milestone 1.1 deleted