#23383: Django ORM generates inefficient SQL (only selected columns should be
grouped by)
-------------------------------------+-------------------------------------
Reporter: aalebedev | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.6
(models, ORM) | Resolution: invalid
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by akaariai):
* status: new => closed
* needs_better_patch: => 0
* resolution: => invalid
* needs_tests: => 0
* needs_docs: => 0
Comment:
The reason for this is that values + annotate has a bit complex
definition. If I recall correctly it goes like this:
- When you call .annotate() the GROUP BY is set to the current SELECT
list
- When you call .values().annotate(), then .values() first sets the
SELECT list, after that the .annotate() sets the GROUP BY to the select
list generated by .values()
- When you call .values() after .annotate() it doesn't change the GROUP
BY, but it changes the SELECT clause (this is what is happening to you)
In addition .extra() has some special casing.
So, for your case, you'll need to first call .values(), then .annotate()
and the result should be what you expect. Closing as invalid - the query
behaves as defined. Ideas welcome how to make values + annotate
interactions more intuitive.
We have two improvements we could do here:
1) For MySQL, we should be able to do GROUP BY <primary key> even when
not using full fields list from the concrete model
2) On PostgreSQL we should be able to switch to primary key grouping (it
works a bit differently to MySQL, but the idea is similar to MySQL's)
The latter has a ticket, but MySQL GROUP BY for the case mentioned in this
ticket doesn't have a separate ticket (at least I don't recall one). A
separate ticket for the MySQL case is welcome.
--
Ticket URL: <https://code.djangoproject.com/ticket/23383#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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.8720c226e1ab96297b66a5c17b741520%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
