id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24233	group-by column construction is broken	megatrump	nobody	"I recently moved to 1.8alpha version from an older version django-e9d1f11 and I started seeing this error. Looks like the way group by is constructed has changed. I am using postgres database.

I could reproduce the behavior with a simple model.
Here is the model that I have.

{{{
class everydaycost(models.Model):
    cost = models.DecimalField(max_digits=20, decimal_places=2)
    rev = models.DecimalField(max_digits=20, decimal_places=2)
    ts = models.DateField(db_index=True)
}}}

I am trying to get roi (rev/cost) for each day of week.

{{{
$ python manage.py shell
Python 2.7.5 (default, Mar  9 2014, 22:15:05)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin
Type ""help"", ""copyright"", ""credits"" or ""license"" for more information.
(InteractiveConsole)
>>>
>>> from testgroupby.models import *
>>> from django.db.models import Q, Count, Min, Sum, Func, FloatField
>>> qs = everydaycost.objects.all()
>>> qs = qs.extra(select={'dow': ""TO_CHAR(ts, 'D')""})
>>> qs = qs.values('dow')
>>> qs = qs.annotate(sum_cost=Sum('cost'))
>>> qs = qs.annotate(sum_rev=Sum('rev'))
>>> qs = qs.annotate(roi=Func(A='CAST(Sum(""rev"") as numeric)', B='CAST(Sum(""cost"") as numeric)',
...                 template='ROUND(COALESCE(%(A)s / NULLIF(%(B)s,0), 0), 2)', output_field=FloatField()))
>>> print qs
DEBUG    (0.002) SELECT (TO_CHAR(ts, 'D')) AS ""dow"", SUM(""testgroupby_everydaycost"".""cost"") AS ""sum_cost"", SUM(""testgroupby_everydaycost"".""rev"") AS ""sum_rev"", ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2) AS ""roi"" FROM ""testgroupby_everydaycost"" GROUP BY (TO_CHAR(ts, 'D')), ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2) LIMIT 21; args=()
Traceback (most recent call last):
  File ""<console>"", line 1, in <module>
  File ""/Downloads/Django-1.8a1/django/db/models/query.py"", line 139, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File ""/Downloads/Django-1.8a1/django/db/models/query.py"", line 163, in __iter__
    self._fetch_all()
  File ""/Downloads/Django-1.8a1/django/db/models/query.py"", line 955, in _fetch_all
    self._result_cache = list(self.iterator())
  File ""/Downloads/Django-1.8a1/django/db/models/query.py"", line 1075, in iterator
    for row in self.query.get_compiler(self.db).results_iter():
  File ""/Downloads/Django-1.8a1/django/db/models/sql/compiler.py"", line 780, in results_iter
    results = self.execute_sql(MULTI)
  File ""/Downloads/Django-1.8a1/django/db/models/sql/compiler.py"", line 826, in execute_sql
    cursor.execute(sql, params)
  File ""/Downloads/Django-1.8a1/django/db/backends/utils.py"", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File ""/Downloads/Django-1.8a1/django/db/backends/utils.py"", line 65, in execute
    return self.cursor.execute(sql, params)
  File ""/Downloads/Django-1.8a1/django/db/utils.py"", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File ""/Downloads/Django-1.8a1/django/db/backends/utils.py"", line 65, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: aggregate functions are not allowed in GROUP BY
LINE 1: ... GROUP BY (TO_CHAR(ts, 'D')), ROUND(COALESCE(CAST(Sum(""rev"")...
                                                             ^

>>> print qs.query
SELECT (TO_CHAR(ts, 'D')) AS ""dow"", SUM(""testgroupby_everydaycost"".""cost"") AS ""sum_cost"", SUM(""testgroupby_everydaycost"".""rev"") AS ""sum_rev"", ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2) AS ""roi"" FROM ""testgroupby_everydaycost"" GROUP BY (TO_CHAR(ts, 'D')), ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2)
>>>
}}}

With earlier version, this worked fine for me.

{{{
$ python manage.py shell
Python 2.7.5 (default, Mar  9 2014, 22:15:05)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.0.68)] on darwin
Type ""help"", ""copyright"", ""credits"" or ""license"" for more information.
(InteractiveConsole)
>>>
>>> from testgroupby.models import *
>>> from django.db.models import Q, Count, Min, Sum, Func, FloatField
>>> qs = everydaycost.objects.all()
>>> qs = qs.extra(select={'dow': ""TO_CHAR(ts, 'D')""})
>>> qs = qs.values('dow')
>>> qs = qs.annotate(sum_cost=Sum('
cost'))
>>> qs = qs.annotate(sum_rev=Sum('rev'))
>>> qs = qs.annotate(roi=Func(A='CAST(Sum(""rev"") as numeric)', B='CAST(Sum(""cost"") as numeric)',
...                 template='ROUND(COALESCE(%(A)s / NULLIF(%(B)s,0), 0), 2)', output_field=FloatField()))
>>> print qs
DEBUG    (0.002) SELECT (TO_CHAR(ts, 'D')) AS ""dow"", SUM(""testgroupby_everydaycost"".""cost"") AS ""sum_cost"", SUM(""testgroupby_everydaycost"".""rev"") AS ""sum_rev"", ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2) AS ""roi"" FROM ""testgroupby_everydaycost"" GROUP BY (TO_CHAR(ts, 'D')) LIMIT 21; args=()
[]
>>> print qs.query
SELECT (TO_CHAR(ts, 'D')) AS ""dow"", SUM(""testgroupby_everydaycost"".""cost"") AS ""sum_cost"", SUM(""testgroupby_everydaycost"".""rev"") AS ""sum_rev"", ROUND(COALESCE(CAST(Sum(""rev"") as numeric) / NULLIF(CAST(Sum(""cost"") as numeric),0), 0), 2) AS ""roi"" FROM ""testgroupby_everydaycost"" GROUP BY (TO_CHAR(ts, 'D'))
>>>
}}}"	Bug	closed	Database layer (models, ORM)	1.8alpha1	Normal	wontfix			Accepted	0	0	0	0	0	0