#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) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm, annotate, | Unreviewed
aggregate, values, avg, max | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Description changed by kot-behemoth:
Old description:
> 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
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.b082f8fe440805359802eb755065b48b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
