annotate + aggregate produces incorrect sql statement

[zeroos <232002@…>]

I have a following model:

class Solution(models.Model): 
    griddler = models.ForeignKey(Griddler) 
    user = models.ForeignKey(User) 
    user_time = models.IntegerField(null=True) 
    date = models.DateTimeField(auto_now_add=True) 
class Griddler(models.Model): 
    .... 
    solved_by = models.ManyToManyField(Uesr, through='Solution') 
    .... 

What I am trying to do is to find the Griddler that has the biggest
avg user time. I've written the following query:
Solution.objects.values('griddler').annotate(a=Avg('user_time')).aggregate( Max('a'))

Unfortunately, after executing it I get

DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT griddlers_solution.griddler_id AS griddler_id, AVG(`griddlers' at line 1")

>>> connection.queries[-1] 
{'time': '0.000', 'sql': u'SELECT  FROM (SELECT 
`griddlers_solution`.`griddler_id` AS `griddler_id`, 
AVG(`griddlers_solution`.`user_time`) AS `a` FROM `griddlers_solution` 
GROUP BY `griddlers_solution`.`griddler_id`, 
`griddlers_solution`.`griddler_id` ORDER BY NULL) subquery'} 

The problem is that Max('a') is skipped in the sql query. Instead of :

SELECT  FROM (SELECT ...

It should look like this:

SELECT Max(`a`) FROM (SELECT... 

I am using Django from SVN (pulled today, 16.03.11) and MySQL backend.

>>> django.get_version() 
'1.3 beta 1'

[Matthias Kestenholz]

Attached patch with a unit test confirming this issue.

[marekw2143]

That's a quick hack that should work, but there probably exist more elegant solution.

[mwawrzyczek]

Added more consistent version of the patch.

[stefano@…]

Hello! Any news on that? I see there's a patch, now oldish, but nothing happened since and nobody from core has commented on it... is that patch a viable solution?

[fhahn]

I think the problem with Book.objects.annotate(c=Count('authors')).values('c').aggregate(Max('c')) was that the call to .values('c') set the aggregates_select_mask to set(['c']) and then c__max does not show up in aggregate_select .

My patch adds the alias of the aggregate to aggregate_select_mask , if aggregate_select_mask is not None.

I've updated the patch and created a pull request: ​https://github.com/django/django/pull/738

[Tim Graham]

Patch applies cleanly and tests pass. Would like someone more familiar with the code to +1 before committing.

[Anssi Kääriäinen <akaariai@…>]

In e888a9b30dd9d62a930b9244d1b5531bb17544b4:

Fixed #15624 -- Made sure aggregations are present in SELECT