ProgrammingError when aggregating over an annotated & grouped ORM query

[Gregory Goltsov]

I have a simplified Django model:

class Module(models.Model):
    score = models.IntegerField()
    user = models.ForeignKey(User)

I was trying to construct a query to get the average highest score per module per user. In SQL, the query would look something like:

SELECT
    m.id
    AVG(max_score)
FROM
    (
        SELECT
            m.id
            Max(m.score) AS max_score
        FROM
            module m
        GROUP BY
            m.user_id,
            m.id
    ) f
GROUP BY
    m.id

In Django ORM the query looks like:

Module.objects.values(     # GROUP BY module_id, user_id
    'id', 'user'
).annotate(                # adding MAX(score) to the SELECT clause
    max_score=Max('score')
).aggregate(               # actually getting the average
    Avg('max_score')
)

This case is even documented in the docs: ​https://docs.djangoproject.com/en/1.6/topics/db/aggregation/#aggregating-annotations

However, upon evaluating, I encounter the following ProgrammingError:

ProgrammingError: syntax error at or near "FROM"
LINE 1: SELECT  FROM (SELECT

The ORM query looks like a perfectly normal query, no hacking, so I was really surprised to see this behaviour. Furthermore, I'm not the only one having this issue, dating as far as 2010:

• ​https://stackoverflow.com/questions/2558992/programmingerror-when-aggregating-over-an-annotated-grouped-django-orm-query
• ​https://stackoverflow.com/questions/23279393/count-and-max-after-values-method-on-django-query

[Gregory Goltsov]

Interestingly enough, in one of the questions someone hacked django.db.models.sql.query.BaseQuery internals to make the query work: it's in the Hacking Django section of the question from ​https://stackoverflow.com/questions/2558992/programmingerror-when-aggregating-over-an-annotated-grouped-django-orm-query