Opened 4 years ago

Last modified 16 months ago

#15049 assigned Bug

Using annotation before and after filter gives wrong results

Reported by: Alex Owned by: anonymous
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

Description

Basically:

qs = Book.objects.values("name").annotate(
    n_authors=Count("authors")
).filter(
    authors__name__startswith="Adrian"
).annotate(
    n_authors2=Count("authors")
)

Generates this SQL:

SELECT
    "aggregation_regress_book"."name",
    COUNT("aggregation_regress_book_authors"."author_id") AS "n_authors",
    COUNT("aggregation_regress_book_authors"."author_id") AS "n_authors2"
FROM
   "aggregation_regress_book"
   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")
WHERE
   T5."name" LIKE Adrian% ESCAPE '\'
GROUP BY
    "aggregation_regress_book"."name",
    "aggregation_regress_book"."name"
ORDER BY
    "aggregation_regress_book"."name" ASC

Which uses the same alias for both COUNTs.

Attachments (1)

django-t15049.diff (1.0 KB) - added by Alex 4 years ago.
Tests

Download all attachments as: .zip

Change History (9)

Changed 4 years ago by Alex

Tests

comment:1 Changed 4 years ago by Alex

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

comment:2 Changed 4 years ago by jaddison

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

comment:3 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:4 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:5 Changed 2 years ago by akaariai

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

comment:6 Changed 22 months ago by mbmohitbagga88@…

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

comment:7 Changed 22 months 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 16 months ago by terpsquared

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

https://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses

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