#34967: Django ORM not generating GROUP BY clause with static annotation
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: nobody
Legtenborg |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Django ORM is not generating a GROUP BY clause with static annotation.
After filtering, Django ORM inserts a HAVING clause, but GROUP BY is
missing.
**Expected Behavior**
The Django ORM should generate a SQL query with a GROUP_BY clause when
using `.values()` and `.annotate()` methods on a queryset.
**Actual Behvaior**
The ORM is not generating a GROUP BY clause when a static annotation is
used. However, it does generate it when a non-trivial annotation is used.
**Steps to reproduce**
With a small model
{{{
class Book(models.Model):
title = models.CharField(max_length=100)
pages = models.IntegerField(default=0)
}}}
and the corresponding view
{{{
def bug_view(request):
queryset = Book.objects.all()
print(f"query: {queryset.query}")
# trivial annotation
annotated_queryset = queryset.annotate(group=Value('all'))
print(f"annotated_query: {annotated_queryset.query}")
# grouped_and_annotated_query won't have a GROUP BY clause
grouped_and_annotated_queryset =
annotated_queryset.values('group').annotate(sum=models.Sum('pages'))
print(f"grouped_and_annotated_query:
{grouped_and_annotated_queryset.query}")
# filtered_query contains HAVING clause but no GROUP BY clause
filtered_queryset = grouped_and_annotated_queryset.filter(sum__gt=10)
print(f"filtered_query: {filtered_queryset.query}")
return HttpResponse(filtered_queryset)
}}}
Django raises a django.db.utils.OperationalError. The (printed) querries
are
{{{
query: SELECT "books_book"."id", "books_book"."title",
"books_book"."pages" FROM "books_book"
annotated_query: SELECT "books_book"."id", "books_book"."title",
"books_book"."pages", all AS "group" FROM "books_book"
grouped_and_annotated_query: SELECT all AS "group",
SUM("books_book"."pages") AS "sum" FROM "books_book"
filtered_query: SELECT all AS "group", SUM("books_book"."pages") AS "sum"
FROM "books_book" HAVING SUM("books_book"."pages") > 10
}}}
As you can see, there is no GROUP BY keyword in the
grouped_and_annotated_query. But after filtering, a HAVING keyword is
inserted, without a GROUP BY. This is the main reason for this error.
This happens only to static annotations. If i evaluate a more complex
annotation, the grouping works as intended:
{{{
def without_bug_view(request):
queryset = Book.objects.all()
print(f"query: {queryset.query}")
# non-trivial annotation
annotated_queryset = queryset.annotate(large=Case(
When(pages__gt=650, then=Value(True)),
default=Value(False),
output_field=BooleanField()))
print(f"annotated_query: {annotated_queryset.query}")
grouped_and_annotated_queryset =
annotated_queryset.values('large').annotate(sum=models.Sum('pages'))
print(f"grouped_and_annotated_query:
{grouped_and_annotated_queryset.query}")
filtered_queryset = grouped_and_annotated_queryset.filter(sum__gt=0)
print(f"filtered_query: {filtered_queryset.query}")
return HttpResponse(filtered_queryset)
}}}
querries:
{{{
query: SELECT "books_book"."id", "books_book"."title",
"books_book"."pages" FROM "books_book"
annotated_query: SELECT "books_book"."id", "books_book"."title",
"books_book"."pages", CASE WHEN "books_book"."pages" > 650 THEN True ELSE
False END AS "large" FROM "books_book"
grouped_and_annotated_query: SELECT CASE WHEN "books_book"."pages" > 650
THEN True ELSE False END AS "large", SUM("books_book"."pages") AS "sum"
FROM "books_book" GROUP BY 1
filtered_query: SELECT CASE WHEN "books_book"."pages" > 650 THEN True ELSE
False END AS "large", SUM("books_book"."pages") AS "sum" FROM "books_book"
GROUP BY 1 HAVING SUM("books_book"."pages") > 0
}}}
The GROUP BY keyword is inserted as is should.
Here is the Stack Trace for completeness:
{{{
Error
Traceback (most recent call last):
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute
return super().execute(query, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: a GROUP BY clause is required before HAVING
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/books/tests.py", line
8, in test_bug_view
response = client.get("/books/bug")
^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/test/client.py", line 927, in get
response = super().get(path, data=data, secure=secure,
headers=headers, **extra)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/test/client.py", line 457, in get
return self.generic(
^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/test/client.py", line 609, in generic
return self.request(**r)
^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/test/client.py", line 891, in request
self.check_exception(response)
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/test/client.py", line 738, in check_exception
raise exc_value
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/core/handlers/exception.py", line 55, in inner
response = get_response(request)
^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/core/handlers/base.py", line 197, in _get_response
response = wrapped_callback(request, *callback_args,
**callback_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/books/views.py", line
23, in bug_view
return HttpResponse(filtered_queryset)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/http/response.py", line 376, in __init__
self.content = content
^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/http/response.py", line 401, in content
content = b"".join(self.make_bytes(chunk) for chunk in value)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/models/query.py", line 398, in __iter__
self._fetch_all()
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/models/query.py", line 1881, in _fetch_all
self._result_cache = list(self._iterable_class(self))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/models/query.py", line 208, in __iter__
for row in compiler.results_iter(
^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/models/sql/compiler.py", line 1513, in
results_iter
results = self.execute_sql(
^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/models/sql/compiler.py", line 1562, in
execute_sql
cursor.execute(sql, params)
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/utils.py", line 80, in
_execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File
"/home/simonlegtenborg/PycharmProjects/djangoProject/venv/lib/python3.11
/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute
return super().execute(query, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.OperationalError: a GROUP BY clause is required before
HAVING
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34967>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018bc3899c99-f747acc1-abfb-4787-b68f-1cff94e85062-000000%40eu-central-1.amazonses.com.
