#22128: The Aggregation guide should warn that this feature does not work when
using the sqlite backend and certain types
--------------------------------------+--------------------
Reporter: zr | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: master
Severity: Normal | Keywords: sqlite
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
--------------------------------------+--------------------
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 #21179. 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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/22128>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/045.5bd8f5b6927f0fd31913c228d7d732b6%40djangoproject.com.
For more options, visit https://groups.google.com/groups/opt_out.
