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

[Josh Smeaton]

I can't replicate this issue with Django 1.7. I do remember running into this about a year ago and I seem to remember it being fixed already. Which version of Django are you using?

[Shai Berger]

I suspect you simplified the code too much -- since m.id is a primary key, there can only be one score value per id value; max(score) makes little sense.

More generally, you may note that the documentation example you linked to aggregates over sub-records, and you are trying to aggregate over the main record -- which, again, makes little sense when used via annotate rather than aggregate.

I have a strong suspicion that the hack mentioned will break "normal" annotate() calls (aggregating over sub-records).

[Baptiste Mispelon]

I can reproduce the reported issue in 1.6 but it's fixed in 1.7.

I traced the fix back to 4bd55547214ae149acadffe60536c379d51e318f.