[Django] #17144: Excessive GROUP BY clauses during aggregations.

[Django]

#17144: Excessive GROUP BY clauses during aggregations.
----------------------------------+--------------------
     Reporter:  christian_oudard  |      Owner:  nobody
         Type:  Bug               |     Status:  new
    Component:  ORM aggregation   |    Version:  1.3
     Severity:  Normal            |   Keywords:
 Triage Stage:  Unreviewed        |  Has patch:  0
Easy pickings:  0                 |      UI/UX:  0
----------------------------------+--------------------
 Doing a simple aggregation using the Django ORM on MySQL, it is producing
 a GROUP BY clause that includes an unnecessary field. In the particular
 case where I discovered this, the unnecessary field is very large, and was
 slowing down the query by 100-fold.

 Here is a simplified version of the model:

 {{{
 class Document(models.Model):
     data = models.TextField() # Very large field

 class Attachment(models.Model):
     document = models.ForeignKey(Document)

 }}}

 And the query I am running:


 {{{
 Document.objects.annotate(num_attachments=Count('attachment'))
 }}}

 And the SQL output:

 {{{
 SELECT
   `document_document`.`id`,
   `document_document`.`data`,
   COUNT(`document_attachment`.`id`) AS `num_attachments`
 FROM `document_document`
   LEFT OUTER JOIN `document_attachment`
     ON (`document_document`.`id` =
 `document_attachment`.`document_id`)
 GROUP BY
   `document_document`.`id`,
   `document_document`.`id`,
   `document_document`.`data`
 ORDER BY NULL
 }}}

 Doing GROUP BY on the data field is unnecessary, and it did not do this in
 Django 1.2.

 From the Django mailing list:

 Karen Tracey On Tue, Nov 1, 2011 at 6:19 PM

 The SQL generated by the ORM for this query changed between Django version
 1.2 and 1.3. The 1.2 SQL did a group by only on the id field. With 1.3
 we're getting id twice and then all other fields in the model. Bisection
 shows the change was made with r14715:
 https://code.djangoproject.com/changeset/14715
 It certainly looks to me like the old SQL was correct and preferable for
 this particular case. In a brief search I did not find a ticket reporting
 this issue -- could you open one?

-- 
Ticket URL: <https://code.djangoproject.com/ticket/17144>
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 post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.