Code

Opened 3 years ago

Closed 3 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:

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

Attachments (0)

Change History (1)

comment:1 Changed 3 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.