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 On Wed, Jan 26, 2011 at 10:03 AM, Sergiy Kuzmenko <s.kuzme...@gmail.com> wrote: > 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>)`. > > > On Wed, Jan 26, 2011 at 9:28 AM, Stephen Burrows > <stephen.r.burr...@gmail.com> wrote: >> 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... >>> [3]http://www.postgresql.org/docs/9.0/static/sql-expressions.html >>> >>> Thanks >>> Sergiy >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers" group. >> To post to this group, send email to django-developers@googlegroups.com. >> To unsubscribe from this group, send email to >> django-developers+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/django-developers?hl=en. >> >> > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
