Opened 5 years ago

Closed 5 years ago

#15183 closed (wontfix)

Annotation count by value always returns zero count for nulls

Reported by: shelldweller Owned by: nobody
Component: Uncategorized Version: 1.2
Severity: Keywords:
Cc: s.kuzmenko@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


How to replicate

Run count annotation on a field that has nulls in it:

ann = MyModel.objects.values("my_field").annotate(Count("my_field"))
for a in ann: print a
{'my_field__count': 0, 'my_field': None}

The count of null values always comes as zero regardless of their actual number.


The generated SQL statement uses count clause in the form of count(<field_name>) which does not count nulls. Unfortunately (at least MySQL and PostgreSQL) backends include null values into output even though nulls are not counted! Which leaves the user with a false impression that there are no null values at all.


The generated SQL statement must be in the form count(*) instead of count(<field_name>) . This will result in correct count of nulls.

Backward compatibility concerns

This solution may introduce a small backward incompatibility for users (if there are such) relying on the fact that null counts are always zero. If the proposed change is implemented those users will have to apply filtering if counting null values is not desired:

ann = MyModel.objects.filter(~Q(my_field=None)).values("my_field").annotate(Count("my_field"))

Change History (1)

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

I'm not convinced that what you're proposing is consistent with the ORM is trying to achieve. There's a certain degree of conceptual leakage going on here, but in context, "None" isn't a value -- so it shouldn't be counted.

However, if you feel strongly that I'm wrong, feel free to make your case on django-dev.

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