Failure with .annotate() and Concat()

[dwasyl]

Thanks for putting together this update.

I've run across a bug when I run the follow statement:
People.objects.annotate(full_name=Concat('last_name', Value(', '), 'first_name'), other_name=Concat('first_name', Value(' '), 'last_name'))

It generates a ProgrammingError:
ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'core_people.last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'core_people.first_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120);

This is running on SQL Server Express 2012.

Any ideas? The code runs fine on MySQL so it's not just a code issue.

Looking closer, this seems similar to michiya#94 and michiya#183. ichiya#94](michiya#94) points to a possible fix at azure-2.0...rnovacek:fix-concat-group-by-new

[OskarPersson]

Do you get the error with only executing People.objects.annotate(full_name=Concat('last_name', Value(', '), 'first_name'), other_name=Concat('first_name', Value(' '), 'last_name')) or when you also use .count()?

People.objects.annotate(full_name=Concat('last_name', Value(', '), 'first_name'), other_name=Concat('first_name', Value(' '), 'last_name')).count()

When running a test similar to those found in #15 I only get the error you described when running .count():

class TestConcat(TestCase):
    def setUp(self):
        self.post = Post.objects.create(title="foo")

    def test_with_count(self):
        qs = Post.objects.annotate(
            full_name=Concat('title', Value(', '), 'title'),
            other_name=Concat('title', Value(' '), 'title')
        )

        print(qs)
        print(qs.count())

[dwasyl]

@OskarPersson going back and looking again, the error occurs when using .count(). .count() was being called in my code in an area I hadn't noticed.

[dbaldy]

In general the .count() function in combination with annotate and a specific function (like Concat) doesn't work.

This is something peculiar, because the query "generated" that appears in the django debug toolbar is correct and can be run in MS SQL Management Studio.

A PR on this would be very much welcome. If I have time this week-end, I will try to work on it. In the meantime, len(queryset) works and can be used as a slow Ersatz.

EDIT: I found the issue. It is related to this and how django builds aggregates.

The stored procedure used by pyodbc doesn't accept the format
SELECT ... FROM ( SELECT a, F(b) FROM ... GROUP BY a, F(b)) subquery

As django builds it in SQLAggregateCompiler in django/db/models/sql/compiler.py.

EDIT 2: It looks like the working query is
SELECT ... FROM ( SELECT a, F(b) FROM ... GROUP BY a, F(b), b) subquery