Query fails because of subquery in group by

[federicoemartinez]

The following test fails

    def test_unnecessary_exists_group_by(self):
        author = Author.objects.annotate(
            has_post=Case(
                When(Exists(Post.objects.filter(author=OuterRef('pk')).values('pk')), then=Value(1)),
                default=Value(0),
                output_field=IntegerField(),
            )).annotate(
            amount=Count("post")
        ).get()
        self.assertEqual(author.amount, 1)
        self.assertEqual(author.has_post, 1)

with the error:

django.db.utils.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. (144) (SQLExecDirectW)')

The error is caused because the query looks like this:

SELECT TOP 21 [testapp_author].[id], [testapp_author].[name], CASE WHEN EXISTS(SELECT TOP 1 (1) AS [a] FROM [testapp_post] U0 WHERE U0.[author_id] = [testapp_author].[id]) THEN ? ELSE ? END AS [has_post], COUNT_BIG([testapp_post].[id]) AS [_count] FROM [testapp_author] LEFT OUTER JOIN [testapp_post] ON ([testapp_author].[id] = [testapp_post].[author_id]) 
GROUP BY [testapp_author].[id], [testapp_author].[name], CASE WHEN EXISTS(SELECT TOP 1 (1) AS [a] FROM [testapp_post] U0 WHERE U0.[author_id] = [testapp_author].[id]) THEN ? ELSE ? END

If the case is not added to the group by, the query works:

SELECT TOP 21 [testapp_author].[id], [testapp_author].[name], CASE WHEN EXISTS(SELECT TOP 1 (1) AS [a] FROM [testapp_post] U0 WHERE U0.[author_id] = [testapp_author].[id]) THEN ? ELSE ? END AS [has_post], COUNT_BIG([testapp_post].[id]) AS [_count] FROM [testapp_author] LEFT OUTER JOIN [testapp_post] ON ([testapp_author].[id] = [testapp_post].[author_id]) 
GROUP BY [testapp_author].[id], [testapp_author].[name]

For what I see, the code tries to remove subqueries in collapse_group_by but it does not consider that there may be "nested" subqueries. In the example, the Case is not a subquery, but it contains a Exists which is a subquery

[mShan0]

Thanks for bringing up the issue and PR. We'll get your PR merged in as soon as possible