Model instances are dropped by annotate when reverse foreign key is empty.

[Ian Foote]

I ran across an odd interaction between annotate, Sum, Case, When and a reverse ForeignKey. I've prepared a ​minimal testcase. I expect the Post I created to be in the queryset, annotated with 0, but instead the queryset is empty.

The query generated by the annotate looks like:

SELECT "bug_post"."id", "bug_post"."created", SUM(CASE WHEN "bug_comment"."created" > ("bug_post"."created") THEN 1 ELSE 0 END) AS "unread_comments" FROM "bug_post" INNER JOIN "bug_comment" ON ( "bug_post"."id" = "bug_comment"."post_id" ) GROUP BY "bug_post"."id", "bug_post"."created"

I initially ran into this on postgres, but my minimal testcase also shows it on sqlite.

[Ian Foote]

Actually, I think this is a duplicate of #24753.

[Shai Berger]

It is an exact duplicate of #24766, but I agree #24753 looks closely related.

Edit: My bad. Didn't notice that Sum is involved here. The right comment is:

#24766 is also closely related