The Aggregation guide should warn that this feature does not work when using the sqlite backend and certain types

[Rigel Di Scala]

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:

>>> 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:

>>> 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 ​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 ​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.

[Rigel Di Scala]

Sorry, I made a mistake in the description. The master ticket is #18247, and not the unrelated #21179.

[Shai Berger]

Description corrected per comment:3.

To the point: Would this warning still be needed after #18247 is fixed? If not, it would just be documenting a bug.

Even if it were, is it really that "aggregation is not supported" or only that "comparisons against aggregates may not perform as expected"?