.values including related fields does not work when specified after an aggregate's .annotate

[David Gouldin]

Using the following models:

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    name = models.CharField(max_length=100)
    author = models.ForeignKey(Author, related_name="books")

This query results in an exception:

Book.objects.annotate(num_authors=Count('author')).filter(num_authors__gt=1).values('author__name')

It looks like the GROUP BY clause of the resulting sql is not getting set correctly. This only seems to happen when .values comes after .annotate.

[cmutel]

This doesn't appear to affect sqlite, but is still a problem for postgresql using trunk. However, the problem rests with Django's ORM, because if annotate comes after values then the SQL is correct. I assume that postgres is simply stricter in what it accepts. A simple test query, using the models in the ticket:

Book.objects.annotate(num_authors=Count('author')).values('author__name')

produces the following SQL:

SELECT "tester_author"."name"
FROM "tester_book"
INNER JOIN "tester_author" ON ("tester_book"."author_id" = "tester_author"."id")
GROUP BY "tester_book"."id",
         "tester_book"."name",
         "tester_book"."author_id"

And raises

ProgrammingError: column "tester_author.name" must appear in the GROUP BY clause or be used in an aggregate function

However, putting the annotate clause at the end results in the correct ""GROUP BY"" clause:

SELECT "tester_author"."name", COUNT("tester_book"."author_id") AS "num_authors"
FROM "tester_book"
INNER JOIN "tester_author" ON ("tester_book"."author_id" = "tester_author"."id")
GROUP BY "tester_author"."name"

I think this problem is related to tickets ​10060 and ​12608.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Tim Graham]

Cannot reproduce (tried back to the current stable/1.4.x branch). I assume it's been fixed since the report.