Opened 14 years ago

Closed 14 years ago

#15183 closed (wontfix)

Annotation count by value always returns zero count for nulls

Reported by: Sergiy Kuzmenko 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: no UI/UX: no

Description

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

Change History (1)

comment:1 by Russell Keith-Magee, 14 years ago

Resolution: wontfix
Status: newclosed

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