Annotation count by value always returns zero count for nulls

[Sergiy Kuzmenko]

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.

Cause

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.

Solution

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"))

[Russell Keith-Magee]

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.