Opened 6 years ago

Closed 76 minutes ago

#11096 closed Bug (worksforme)

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

Reported by: dgouldin 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 6 years ago by dgouldin

  • Cc dgouldin@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 6 years ago by Alex

  • Triage Stage changed from Unreviewed to Accepted

comment:3 Changed 6 years ago by cmutel

  • Cc cmutel@… added
  • Version changed from 1.0 to SVN

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 2 days ago by timgraham (previous) (diff)

comment:4 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

comment:8 Changed 76 minutes ago by timgraham

  • Resolution set to worksforme
  • Status changed from new to closed

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