Opened 8 years ago

Closed 16 months ago

#11096 closed Bug (worksforme)

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

Reported by: David Gouldin Owned by:
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: dgouldin@…, cmutel@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (8)

comment:1 Changed 8 years ago by David Gouldin

Cc: dgouldin@… added

comment:2 Changed 7 years ago by Alex Gaynor

Triage Stage: UnreviewedAccepted

comment:3 Changed 7 years ago by cmutel

Cc: cmutel@… added
Version: 1.0SVN

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.

Last edited 16 months ago by Tim Graham (previous) (diff)

comment:4 Changed 6 years ago by Julien Phalip

Severity: Normal
Type: Bug

comment:5 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:6 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:7 Changed 4 years ago by Anssi Kääriäinen

Component: ORM aggregationDatabase layer (models, ORM)

comment:8 Changed 16 months ago by Tim Graham

Resolution: worksforme
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top