#24233: group-by column construction is broken
----------------------------------------------+-----------------------
Reporter: megatrump | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8alpha1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+-----------------------
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'))
>>>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24233>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.92437d337494485205ed27735f656ed8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
