Opened 5 years ago

Last modified 8 weeks ago

#15049 new Bug

Using annotation before and after filter gives wrong results

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



qs = Book.objects.values("name").annotate(

Generates this SQL:

    COUNT("aggregation_regress_book_authors"."author_id") AS "n_authors",
    COUNT("aggregation_regress_book_authors"."author_id") AS "n_authors2"
   LEFT OUTER JOIN "aggregation_regress_book_authors" ON ("aggregation_regress_book"."id" = "aggregation_regress_book_authors"."book_id")
   INNER JOIN "aggregation_regress_book_authors" T4 ON ("aggregation_regress_book"."id" = T4."book_id")
   INNER JOIN "aggregation_regress_author" T5 ON (T4."author_id" = T5."id")
   T5."name" LIKE Adrian% ESCAPE '\'
    "aggregation_regress_book"."name" ASC

Which uses the same alias for both COUNTs.

Attachments (2)

django-t15049.diff (1.0 KB) - added by Alex 5 years ago.
15049-test.diff (930 bytes) - added by timgraham 3 months ago.

Download all attachments as: .zip

Change History (13)

Changed 5 years ago by Alex


comment:1 Changed 5 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 5 years ago by jaddison

  • milestone 1.3 deleted
  • Severity set to Normal
  • Type set to Bug

comment:3 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:4 Changed 4 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:5 Changed 3 years ago by akaariai

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

comment:6 Changed 2 years ago by mbmohitbagga88@…

  • Cc mbmohitbagga88@… added
  • Owner set to anonymous
  • Status changed from new to assigned

comment:7 Changed 2 years ago by akaariai

Just a warning - this isn't at all easy to tackle. Basically if the query has more than one "multijoin" then aggregates must be done either as subselects or as subqueries.

An alternate is to just throw an error in such cases. Even this would be better than producing wrong results silently.

comment:8 Changed 20 months ago by terpsquared

Just ran into this. At the very least, the documentation should be updated to reflect this issue.

comment:9 Changed 3 months ago by timgraham

Confirmed it's still an issue as of dad8434d6ff5da10959672726dc9b397296d380b. Attaching a rebased test.

In the interim, documentation patches would of course be welcomed.

Changed 3 months ago by timgraham

comment:10 Changed 8 weeks ago by timgraham

  • Owner anonymous deleted
  • Status changed from assigned to new

comment:11 Changed 8 weeks ago by akaariai

What is happening here is that:

  • first annotate uses the first join it finds for authors, or generates a new one
  • the second filter call introduces another join on authors
  • the second annotate uses again the first join it finds for authors

It would likely be somewhat easy to make the second annotate use the last introduced join instead of the first join for authors. But, this has a couple of problems. It could break existing code, and it is likely that the results would be incorrect in any case (due to Django not handling multiple multivalued joins + annotate correctly).

I say we don't fix this issue. Instead we could add an explicit way to introduce joins. Something like:

qs = Book.objects.values("name").annotate(

Now it is explicit what happens. It is notable that the results are still incorrect. To get correct results something like this might work:

qs = Book.objects.values("name").annotate(
    n_authors2=Count("authors", subquery=True, filter=Q(authors__name__startswith="Adrian"))

Of course, we have a bit more work to do before this is possible. The point is I don't think we can be smart enough to automatically generate the correct query for the report's case.

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