Django: Ticket #22128: The Aggregation guide should warn that this feature does not work when using the sqlite backend and certain typeshttps://code.djangoproject.com/ticket/22128
<p>
When using aggregation clauses like "HAVING SUM" in sqlite3, SUM is a function and therefore does not have affinity. Therefore, the correct type must be used in the statement, or it might yield incorrect results:
</p>
<div class="wiki-code"><div class="code"><pre><span class="o">&gt;&gt;&gt;</span> <span class="nb">len</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span>cursor<span class="o">.</span>execute<span class="p">(</span><span class="s2">"SELECT * from thing_thing WHERE thing_thing.cost &gt; '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) &gt; 0"</span><span class="p">)))</span>
<span class="mi">3</span>
<span class="o">&gt;&gt;&gt;</span> <span class="nb">len</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span>cursor<span class="o">.</span>execute<span class="p">(</span><span class="s2">"SELECT * from thing_thing WHERE thing_thing.cost &gt; '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) &gt; '0'"</span><span class="p">)))</span>
<span class="mi">0</span>
</pre></div></div><p>
This can be problematic when using the <code>decimal.Decimal</code> type, as the it will be coerced to a string by the <code>django.db.backends.utils.rev_typecast_decimal</code> adapter. For example:
</p>
<div class="wiki-code"><div class="code"><pre><span class="o">&gt;&gt;&gt;</span> query <span class="o">=</span> <span class="s2">"SELECT * from thing_thing WHERE thing_thing.cost &gt; '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) &gt; ?"</span>
<span class="o">&gt;&gt;&gt;</span> <span class="nb">len</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span>cursor<span class="o">.</span>execute<span class="p">(</span>query<span class="p">,</span> <span class="p">(</span><span class="mf">0.0</span><span class="p">,))))</span>
<span class="mi">3</span>
<span class="o">&gt;&gt;&gt;</span> <span class="nb">len</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span>cursor<span class="o">.</span>execute<span class="p">(</span>query<span class="p">,</span> <span class="p">(</span><span class="s1">'0.0'</span><span class="p">,))))</span>
<span class="mi">0</span>
<span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">decimal</span> <span class="kn">import</span> Decimal
<span class="o">&gt;&gt;&gt;</span> <span class="nb">len</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span>cursor<span class="o">.</span>execute<span class="p">(</span>query<span class="p">,</span> <span class="p">(</span>Decimal<span class="p">(</span><span class="s1">'0.0'</span><span class="p">),))))</span>
<span class="mi">0</span>
</pre></div></div><p>
Therefore, any queryset instantiated by a sqlite3 backend that uses the aggregation features described in the Django documentation <a class="ext-link" href="https://docs.djangoproject.com/en/dev/topics/db/aggregation/"><span class="icon">​</span>Aggregation guide</a> will most probably return incorrect results. The problem has been time consuming to debug, as the origin is not immediately clear.
</p>
<p>
The master ticket for this issue appears to be <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a>. The duplicated <a class="closed ticket" href="https://code.djangoproject.com/ticket/22117" title="#22117: Bug: Filtering on an annotation returns no reults in sqlite3 when it should ... (closed: duplicate)">#22117</a> was recently open, but it is expected that more will come.
</p>
<p>
Therefore, I recommend that a warning message be included after the first few paragraphs of the <a class="ext-link" href="https://docs.djangoproject.com/en/dev/topics/db/aggregation/"><span class="icon">​</span>Aggregation guide</a>, that provides an explanation of the problem.
</p>
<p>
An example message could be:
</p>
<pre class="wiki">
.. warning::
Aggregation is not currently supported by the Django sqlite backend when using types that coerce
to strings. One example is :class:`decimal.Decimal`, as instances of this type will be converted
to `str` instead of `float`, in order to preserve their arithmetic precision.
</pre>en-usDjangohttps://www.djangoproject.com/s/img/site/hdr_logo.gifhttps://code.djangoproject.com/ticket/22128
Trac 1.2Rigel Di ScalaSun, 23 Feb 2014 14:45:55 GMTowner, status changedhttps://code.djangoproject.com/ticket/22128#comment:1
https://code.djangoproject.com/ticket/22128#comment:1
<ul>
<li><strong>owner</strong>
changed from <em>nobody</em> to <em>Rigel Di Scala</em>
</li>
<li><strong>status</strong>
changed from <em>new</em> to <em>assigned</em>
</li>
</ul>
TicketRigel Di ScalaSun, 23 Feb 2014 14:47:37 GMThttps://code.djangoproject.com/ticket/22128#comment:2
https://code.djangoproject.com/ticket/22128#comment:2
<p>
Sorry, I made a mistake in the description. The master ticket is <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a>, and not the unrelated <a class="closed ticket" href="https://code.djangoproject.com/ticket/21179" title="#21179: Cleanup/optimization: How-to output CSV from Django should suggest using `StreamingHttpResponse` (closed: fixed)">#21179</a>.
</p>
TicketRigel Di ScalaSun, 23 Feb 2014 15:19:01 GMTstatus changed; owner deletedhttps://code.djangoproject.com/ticket/22128#comment:3
https://code.djangoproject.com/ticket/22128#comment:3
<ul>
<li><strong>status</strong>
changed from <em>assigned</em> to <em>new</em>
</li>
<li><strong>owner</strong>
<em>Rigel Di Scala</em> deleted
</li>
</ul>
TicketShai BergerMon, 24 Feb 2014 02:25:04 GMTdescription changedhttps://code.djangoproject.com/ticket/22128#comment:4
https://code.djangoproject.com/ticket/22128#comment:4
<ul>
<li><strong>description</strong>
modified (<a href="/ticket/22128?action=diff&amp;version=4">diff</a>)
</li>
</ul>
<p>
Description corrected per <a class="ticket" href="https://code.djangoproject.com/ticket/22128#comment:3" title="Comment 3">comment:3</a>.
</p>
<p>
To the point: Would this warning still be needed after <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> is fixed? If not, it would just be documenting a bug.
</p>
<p>
Even if it were, is it really that "aggregation is not supported" or only that "comparisons against aggregates may not perform as expected"?
</p>
TicketRigel Di ScalaMon, 24 Feb 2014 08:52:17 GMThttps://code.djangoproject.com/ticket/22128#comment:5
https://code.djangoproject.com/ticket/22128#comment:5
<blockquote class="citation">
<p>
Would this warning still be needed after <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> is fixed? If not, it would just be documenting a bug.
</p>
</blockquote>
<p>
No, but fixing <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> may take some time. Also, this isn't really a Django bug, but an integration problem between the Python sqlite bindings and decimal.Decimal. I think the correct wording is "Django does not support the aggregation feature using the sqlite backend" because Django does not attempt to handle this situation in the ORM. A possible way to handle this would be to coerce the decimal.Decimal into a float, but this would lose precision, so it isn't a straightforward decision to make.
</p>
<blockquote class="citation">
<p>
Even if it were, is it really that "aggregation is not supported" or only that "comparisons against aggregates may not perform as expected"?
</p>
</blockquote>
<p>
More like the latter, but then again, the problem stems from the fact that Django ORM does not attempt to resolve the problem for you. In a sense, it does not support this use-case.
</p>
<p>
I think a warning message on the page would save users much grief and frustration, while a decision is being taken on how to best resolve the issue.
</p>
TicketShai BergerMon, 10 Mar 2014 22:18:53 GMTcc, status changed; resolution sethttps://code.djangoproject.com/ticket/22128#comment:6
https://code.djangoproject.com/ticket/22128#comment:6
<ul>
<li><strong>cc</strong>
<em>Shai Berger</em> added
</li>
<li><strong>status</strong>
changed from <em>new</em> to <em>closed</em>
</li>
<li><strong>resolution</strong>
set to <em>duplicate</em>
</li>
</ul>
<p>
Replying to <a class="ticket" href="https://code.djangoproject.com/ticket/22128#comment:5" title="Comment 5">zr</a>:
</p>
<blockquote class="citation">
<blockquote class="citation">
<p>
Would this warning still be needed after <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> is fixed? If not, it would just be documenting a bug.
</p>
</blockquote>
<p>
No, but fixing <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> may take some time.
</p>
</blockquote>
<p>
We usually avoid documenting bugs, long-standing bugs included. A warning might be fitting in the database-specific warnings (rather than aggregation guide), if <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> was wontfix'd. However, this does not seem likely at this point.
</p>
<blockquote class="citation">
<blockquote class="citation">
<p>
Even if it were, is it really that "aggregation is not supported" or only that "comparisons against aggregates may not perform as expected"?
</p>
</blockquote>
<p>
More like the latter, but then again, the problem stems from the fact that Django ORM does not attempt to resolve the problem for you. In a sense, it does not support this use-case.
</p>
</blockquote>
<p>
I think you are mixing behavior with implementation. When you document that something is "not supported" it usually means "intentionally unsupported", not "we just neglected to do anything about it".
</p>
<p>
I sympathize with your aggravation over the hard debugging, but I don't think this is a good way to mitigate it.
</p>
<p>
I will add a comment about this to <a class="closed ticket" href="https://code.djangoproject.com/ticket/18247" title="#18247: Bug: Filtering on aggregate annotations with a Decimal value doesn't work (closed: fixed)">#18247</a> -- this bug, then, will also be made a duplicate of it. Feel free to reopen if you can come up with new arguments.
</p>
Ticket