Multiple annotations with Exists() should not group by aliases.

[Sigurd Ljødal]

There's a regression in Django 3.0 where an annotation that is not selected is included in the group by section of the query on PostgreSQL. I looks a bit like https://code.djangoproject.com/ticket/31109, but it is still present in Django 3.0.2 (I haven't tested earlier versions yet). I haven't had time to look into where the incorrect group by cause is generated yet.

I have created a test case to reproduce the issue here: ​https://github.com/django/django/compare/master...ljodal:regression/annotate-groupby-postgresql

It only crashes when running against PostgreSQL. Here's the test case query:

latest_book_pubdate_qs = Book.objects.filter(
    publisher=OuterRef('pk')
).values_list('id')
publisher_qs = Publisher.objects.values('pk').annotate(
    has_published_books=Exists(latest_book_pubdate_qs),
).filter(
    has_published_books=True
).annotate(
    book_count=Count('book')
).values_list('id')

The generated query is this:

SELECT "aggregation_publisher"."id" FROM "aggregation_publisher" LEFT OUTER JOIN "aggregation_book" ON ("aggregation_publisher"."id" = "aggregation_book"."publisher_id") WHERE EXISTS(SELECT U0."id" FROM "aggregation_book" U0 WHERE U0."publisher_id" = "aggregation_publisher"."id") GROUP BY "aggregation_publisher"."id", "has_published_books"

Which crashes because "has_published_books" in the "GROUP BY" clause is not a column on the table:

Traceback (most recent call last):
  File "/Users/sigurdljodal/Code/django/tests/aggregation/tests.py", line 1166, in test_aggregation_annotation
    print(list(publisher_qs))
  File "/Users/sigurdljodal/Code/django/django/db/models/query.py", line 270, in __len__
    self._fetch_all()
  File "/Users/sigurdljodal/Code/django/django/db/models/query.py", line 1284, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/sigurdljodal/Code/django/django/db/models/query.py", line 144, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/sigurdljodal/Code/django/django/db/models/sql/compiler.py", line 1085, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/Users/sigurdljodal/Code/django/django/db/models/sql/compiler.py", line 1133, in execute_sql
    cursor.execute(sql, params)
  File "/Users/sigurdljodal/Code/django/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/sigurdljodal/Code/django/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/sigurdljodal/Code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/sigurdljodal/Code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/sigurdljodal/Code/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "has_published_books" does not exist
LINE 1: ...her"."id") GROUP BY "aggregation_publisher"."id", "has_publi...

[felixxm]

Thanks for this report. As a temporary workaround you can use Exists() directly in QuerySet.filter() (without an annotation), i.e.

Publisher.objects.values('pk').filter(
    Exists(latest_book_pubdate_qs)
).annotate(
    book_count=Count('book')
).values_list('id')

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.
Reproduced at 495d7a1ddf3c8c83d49f099ee3599afbc28306fe.

[felixxm]

​PR