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
17144	Excessive GROUP BY clauses during aggregations.	christian_oudard	Anssi Kääriäinen	"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?"	Bug	closed	Database layer (models, ORM)	1.3	Normal	fixed		Michael Curry kmike84@… YenTheFirst baryshev@… anssi.kaariainen@… hcarvalhoalves@… dev@…	Accepted	1	0	0	0	0	0