id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31136	Multiple annotations with Exists() should not group by aliases.	Sigurd Ljødal	Mariusz Felisiak	"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:
{{{#!python
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:
{{{#!sql
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...
}}}"	Bug	closed	Database layer (models, ORM)	3.0	Release blocker	fixed		Simon Charette jon.dufresne@…	Ready for checkin	1	0	0	0	0	0