id,summary,reporter,owner,description,type,status,component,version,severity,resolution,keywords,cc,stage,has_patch,needs_docs,needs_tests,needs_better_patch,easy,ui_ux
22128,The Aggregation guide should warn that this feature does not work when using the sqlite backend and certain types,zr,,"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:
{{{
#!python
>>> len(list(cursor.execute(""SELECT * from thing_thing WHERE thing_thing.cost > '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) > 0"")))
3
>>> len(list(cursor.execute(""SELECT * from thing_thing WHERE thing_thing.cost > '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) > '0'"")))
0
}}}
This can be problematic when using the `decimal.Decimal` type, as the it will be coerced to a string by the `django.db.backends.utils.rev_typecast_decimal` adapter. For example:
{{{
#!python
>>> query = ""SELECT * from thing_thing WHERE thing_thing.cost > '0' GROUP BY thing_thing.cost HAVING SUM(thing_thing.cost) > ?""
>>> len(list(cursor.execute(query, (0.0,))))
3
>>> len(list(cursor.execute(query, ('0.0',))))
0
>>> from decimal import Decimal
>>> len(list(cursor.execute(query, (Decimal('0.0'),))))
0
}}}
Therefore, any queryset instantiated by a sqlite3 backend that uses the aggregation features described in the Django documentation [https://docs.djangoproject.com/en/dev/topics/db/aggregation/ Aggregation guide] will most probably return incorrect results. The problem has been time consuming to debug, as the origin is not immediately clear.
The master ticket for this issue appears to be #18247. The duplicated #22117 was recently open, but it is expected that more will come.
Therefore, I recommend that a warning message be included after the first few paragraphs of the [https://docs.djangoproject.com/en/dev/topics/db/aggregation/ Aggregation guide], that provides an explanation of the problem.
An example message could be:
{{{
.. 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.
}}}",Cleanup/optimization,closed,Documentation,master,Normal,duplicate,sqlite,bmispelon zr shai,Unreviewed,0,0,0,0,1,0