[Django] #23669: ProgrammingError when aggregating over an annotated & grouped ORM query

Django

unread,

Oct 16, 2014, 1:22:39 PM10/16/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6
(models, ORM) | Keywords: orm, annotate,
Severity: Normal | aggregate, values, avg, max
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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

--
Ticket URL: <https://code.djangoproject.com/ticket/23669>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 16, 2014, 1:26:26 PM10/16/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/23669#comment:1>

Django

unread,

Oct 16, 2014, 1:37:06 PM10/16/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

Description changed by kot-behemoth:

Old description:

New description:

I have a simplified Django model:

{{{
#!python

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:

{{{
#!sql

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:

{{{
#!python

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

--

--
Ticket URL: <https://code.djangoproject.com/ticket/23669#comment:2>

Django

unread,

Oct 16, 2014, 6:25:39 PM10/16/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

Comment (by jarshwah):

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?

--
Ticket URL: <https://code.djangoproject.com/ticket/23669#comment:3>

Django

unread,

Oct 16, 2014, 7:55:30 PM10/16/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6

Comment (by shaib):

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).

--
Ticket URL: <https://code.djangoproject.com/ticket/23669#comment:4>

Django

unread,

Oct 17, 2014, 5:58:33 AM10/17/14

to django-...@googlegroups.com

#23669: ProgrammingError when aggregating over an annotated & grouped ORM query
-------------------------------------+-------------------------------------
Reporter: kot-behemoth | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 1.6

(models, ORM) | Resolution: fixed