Opened 14 months ago
Closed 14 months ago
#34967 closed Bug (fixed)
Queries not generating GROUP BY clause with static annotation crashes on SQLite
Reported by: | Simon Legtenborg | Owned by: | David Sanders |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | sqlite |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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
Change History (6)
comment:1 by , 14 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 14 months ago
Keywords: | sqlite added |
---|---|
Summary: | Django ORM not generating GROUP BY clause with static annotation → Queries not generating GROUP BY clause with static annotation crashes on SQLite |
This appears to only affect SQLite.
I haven't tested on Oracle.
comment:3 by , 14 months ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:4 by , 14 months ago
Jacob & Simon discovered this is only for SQLite < 3.39.0 which allowed a HAVING clause without a GROUP BY: https://www.sqlite.org/releaselog/3_39_0.html
comment:5 by , 14 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Thanks for the report 👍