annotating fields with null=True

[Sergiy Kuzmenko]

Hi there!

Annotating a nullable foreign field with Count seems to always return
the count of null values as zero (at least in MySQL environment). A
quick look into this problem reveals that the corresponding SQL clause
is generated as `count(<field_name>)` [1]. This causes to exclude null
values from annotation in MySQL [2]. I believe the same applies to
PostgreSQL [3] and likely to other backends.

In my mind, current behaviour is bit of a bug (it is definitely quirky
in MySQL). But it is possible that not counting null values was
intentional. In this case there should be at least a way for user to
specify that null values must also be counted. Perhaps something like:

Count(field_name, count_nulls=True)

[1] http://code.djangoproject.com/browser/django/trunk/django/db/models/sql/aggregates.py#L76
[2] http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count
[3] http://www.postgresql.org/docs/9.0/static/sql-expressions.html

Thanks
Sergiy

[Stephen Burrows]

Perhaps I'm missing something, but if you count all the defined
foreign keys AND all the null values, won't you just end up with a
count of the parent model? Or are you saying that you explicitly want
to count how many values are null *instead of* defined?

On Jan 25, 2:39 pm, Sergiy Kuzmenko <s.kuzme...@gmail.com> wrote:
> Hi there!
>
> Annotating a nullable foreign field with Count seems to always return
> the count of null values as zero (at least in MySQL environment). A
> quick look into this problem reveals that the corresponding SQL clause
> is generated as `count(<field_name>)` [1]. This causes to exclude null
> values from annotation in MySQL [2]. I believe the same applies to
> PostgreSQL [3] and likely to other backends.
>
> In my mind, current behaviour is bit of a bug (it is definitely quirky
> in MySQL). But it is possible that not counting null values was
> intentional. In this case there should be at least a way for user to
> specify that null values must also be counted. Perhaps something like:
>
> Count(field_name, count_nulls=True)
>

> [1]http://code.djangoproject.com/browser/django/trunk/django/db/models/s...
> [2]http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#functi...

[Sergiy Kuzmenko]

I want to be able to count how many times each value (including nulls)
is present. I think that exactly what count is for. -:)

Upon a closer look the problem is not limited to foreign keys. When
django generates count clauses in `count(<name>)` form here's what
happens (in MySQL at least):

SELECT foo, count(foo) FROM test group by foo;
+-------+------------+
| foo | count(foo) |
+-------+------------+
| NULL | 0 |
| one | 1 |
| three | 3 |
| two | 2 |
+-------+------------+

This is what MySQL outputs. Django simply takes these rows and outputs
them in a list of dictionaries to the user. The problem here is not
that nulls are not counted but that nulls are included in the output.
Looking at these results one may conclude that there are no null
values, right? Well actually wrong. Using `count(*)` form produces
quite different results:

SELECT foo, count(*) FROM test group by foo;
+-------+----------+
| foo | count(*) |
+-------+----------+
| NULL | 1 |
| one | 1 |
| three | 3 |
| two | 2 |
+-------+----------+

Now we see the whole picture and the sum of all individual counts is
equal to MyModel.objects.count()

This inclines to believe (lack of experience with other back ends
prevents me from asserting this) that current behaviour is a bug and
that django should generate `count(*)` clauses instead of
`count(<name>)`.

> --
> You received this message because you are subscribed to the Google Groups "Django developers" group.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to django-develop...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
>
>

[Sergiy Kuzmenko]

The same problem occurs with PostreSQL as well: nulls (if present) are
always returned with zero count in aggregates.

Since my post did not stir much of a commotion I conclude that there
are no strong opinions among django developers as to which form of
count should be used in aggregates. So I'll go ahead and will file a
bug in track.

Cheers