Django: Ticket Queryhttps://code.djangoproject.com/query?status=!closed&keywords=~aggregate&order=type
The Web framework for perfectionists with deadlines.en-USDjangohttps://www.djangoproject.com/s/img/site/hdr_logo.gifhttps://code.djangoproject.com/query?status=!closed&keywords=~aggregate&order=type
Trac 1.2.2https://code.djangoproject.com/ticket/25937
https://code.djangoproject.com/ticket/25937#25937: Failure when using expressions.DateTime on NULL values and aggregatingMon, 14 Dec 2015 15:28:47 GMTThomas Recouvreux<p>
Running query like <code>qs.annotate(time=DateTime('removed', 'month', timezone.UTC())).values('time').annotate(Count('pk'))</code> fails with <code>&lt;repr(&lt;gill.contrib.sales.models.TransactionRowQuerySet at 0x10cc99320&gt;) failed: ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?&gt;</code>. I noted the failure on MySQL and PostgreSQL, Django 1.8.X and 1.9.X. I did not try on other db backends or other Django versions.
</p>
<p>
To reproduce the behaviour:
</p>
<ol><li>create a model:
<pre class="wiki">class Potato(models.Model):
..
removed = models.DateTimeField(null=True, blank=True)
</pre></li></ol><ol start="2"><li>Insert some of them
<pre class="wiki">Potato.objects.create(removed=timezone.now())
Potato.objects.create(removed=None)
..
</pre></li></ol><ol start="3"><li>Fire the request
<pre class="wiki"># Failure
Potato.objects.annotate(time=DateTime('removed', 'month', timezone.UTC())).values('time').annotate(c=Count('pk'))
# Success
Potato.objects.filter(removed__isnull=False).annotate(time=DateTime('removed', 'month', timezone.UTC())).values('time').annotate(c=Count('pk'))
</pre></li></ol><p>
Note: I printed the raw sql generated by Django and fired it directly in a db shell without any error, so the problem seems to come from the code Django uses to convert the result from the backend to python code, especially the line with NULL coming from the aggregation:
</p>
<pre class="wiki">+---------------------+-----------+
| time | pk__count |
+---------------------+-----------+
| NULL | 17 |
| 2015-01-01 00:00:00 | 7 |
..
</pre>Resultshttps://code.djangoproject.com/ticket/25937#changeloghttps://code.djangoproject.com/ticket/10929
https://code.djangoproject.com/ticket/10929#10929: Support a default value for Sum (and possibly other aggregation functions)Sat, 25 Apr 2009 22:16:17 GMTnolan<p>
By default, annotate(sum_field = Sum(...)) results in sum_field being NULL if there were no values to sum. In most cases, 0 would be a better option here, for proper sorting in a later order_by, and for comparisons using lt/gt/gte/lte.
</p>
<p>
A monkeypatch to implement default values for NULL using COALESCE is available here:
<a class="ext-link" href="http://stackoverflow.com/questions/553038/treat-null-as-0-in-django-model"><span class="icon">​</span>http://stackoverflow.com/questions/553038/treat-null-as-0-in-django-model</a>
</p>
Resultshttps://code.djangoproject.com/ticket/10929#changeloghttps://code.djangoproject.com/ticket/26650
https://code.djangoproject.com/ticket/26650#26650: Automatically apply Cast based on output_fieldMon, 23 May 2016 01:34:06 GMTMatt C<p>
Given the following model:
</p>
<pre class="wiki">import django.db.models
class TempModel(django.db.models.Model):
field1 = django.db.models.DecimalField(max_digits=12, decimal_places=2)
</pre><p>
...when I run a query like so:
</p>
<pre class="wiki">TempModel.objects.all().aggregate(
my_sum=django.db.models.Sum(
'field1', output_field=django.db.models.DateTimeField()
)
)
</pre><p>
...the result produces a <code>Decimal</code> result, no matter what <code>output_field</code> is specified.
</p>
<p>
I came across this issue by simply wanting to specify the number of decimal places that would get output in queries such as this.
That led me to find <a class="closed ticket" href="https://code.djangoproject.com/ticket/23941" title="#23941: Bug: Aggregating over decimal field regression (closed: fixed)">#23941</a>, which shocked me in seeing that the "sane" behaviour/implementation was replaced with something that breaks down/invalidates the API.
</p>
<p>
Then I tried the query above, with all different kinds of <code>output_field</code> values and realised the problem is more systemic and severe, in the sense that specifying <code>output_field</code> is often pointless/futile.
</p>
<p>
Why can't the implementation actually honour the <code>output_field</code> parameter and construct SQL to cast/coerce fields into the appropriate DB field types?
</p>
<p>
The only other means (in which I am aware of) for ensuring that a query gets compiled to SQL that honours the clients' field type specifications is to use <code>.extra()</code> or <code>.raw()</code>, but then that is no longer DB agnostic.
I would have thought it was the job of <code>output_field</code> to achieve this in a DB agnostic manner, utilising the power of the ORM.
</p>
Resultshttps://code.djangoproject.com/ticket/26650#changelog