#31136: Multiple annotations with Exists() should not group by aliases.
-------------------------------------+-------------------------------------
Reporter: Sigurd Ljødal | Owner: felixxm
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jon Dufresne):
* status: closed => new
* resolution: fixed =>
Comment:
Here is a POC test to demonstrate the regression. Fails with the
postgresql backend.
{{{
diff --git a/tests/aggregation/models.py b/tests/aggregation/models.py
index fd441fe51d..211a96d97c 100644
--- a/tests/aggregation/models.py
+++ b/tests/aggregation/models.py
@@ -42,3 +42,17 @@ class Store(models.Model):
def __str__(self):
return self.name
+
+
+class ThisOrThat(models.Model):
+ pass
+
+
+class This(models.Model):
+ thisorthat = models.ForeignKey("ThisOrThat", models.CASCADE)
+ created_at = models.DateTimeField(auto_now_add=True)
+
+
+class That(models.Model):
+ thisorthat = models.OneToOneField("ThisOrThat", models.CASCADE)
+ created_at = models.DateTimeField(auto_now_add=True)
diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py
index bef415abd4..508b9cf687 100644
--- a/tests/aggregation/tests.py
+++ b/tests/aggregation/tests.py
@@ -1239,3 +1239,20 @@ class AggregateTestCase(TestCase):
contact_publisher__isnull=False,
).annotate(count=Count('authors'))
self.assertSequenceEqual(books_qs, [book])
+
+ def test_regression(self):
+ from .models import ThisOrThat
+ from django.db.models.aggregates import Max
+ from django.db.models.functions import Coalesce
+
+ qs = (
+ ThisOrThat.objects.annotate(
+ Max("this__created_at"),
+ created_at=Coalesce(
+ "this__created_at__max", "that__created_at"
+ ),
+ )
+ .order_by("created_at")
+ .values_list("pk", flat=True)
+ )
+ list(qs)
}}}
Running this test results in the following error:
{{{
======================================================================
ERROR: test_regression (aggregation.tests.AggregateTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/jon/devel/django/django/db/backends/utils.py", line 86, in
_execute
return self.cursor.execute(sql, params)
psycopg2.errors.GroupingError: column "aggregation_that.created_at" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...BY COALESCE(MAX("aggregation_this"."created_at"), "aggregati...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/jon/devel/django/tests/aggregation/tests.py", line 1258, in
test_regression
list(qs)
File "/home/jon/devel/django/django/db/models/query.py", line 258, in
__len__
self._fetch_all()
File "/home/jon/devel/django/django/db/models/query.py", line 1261, in
_fetch_all
self._result_cache = list(self._iterable_class(self))
File "/home/jon/devel/django/django/db/models/query.py", line 184, in
__iter__
for row in compiler.results_iter(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size):
File "/home/jon/devel/django/django/db/models/sql/compiler.py", line
1096, in results_iter
results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch,
chunk_size=chunk_size)
File "/home/jon/devel/django/django/db/models/sql/compiler.py", line
1144, in execute_sql
cursor.execute(sql, params)
File "/home/jon/devel/django/django/db/backends/utils.py", line 68, in
execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/home/jon/devel/django/django/db/backends/utils.py", line 77, in
_execute_with_wrappers
return executor(sql, params, many, context)
File "/home/jon/devel/django/django/db/backends/utils.py", line 86, in
_execute
return self.cursor.execute(sql, params)
File "/home/jon/devel/django/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/jon/devel/django/django/db/backends/utils.py", line 86, in
_execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "aggregation_that.created_at"
must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...BY COALESCE(MAX("aggregation_this"."created_at"), "aggregati...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31136#comment:13>
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/064.1aa59bcbac9307fd3e79ed845aedc4ca%40djangoproject.com.
