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