Incorrect Decimal handling in Sum queries

[Jurica Železnjak]

When doing Sum aggregate or annotate query on a DecimalField the result is a close-to-zero float (scientific notation) when it should be zero.

For example: 8 records, with following values: 7.85, -7, 19.85, -7, -5, -7, -0.85, -0.85
Aggregate Sum query on this column should equal Decimal('0.00'), but I'm getting Decimal('1.77635683940025E-15').

Before Django 2.1.0 this worked properly.

[Carlton Gibson]

Before Django 2.1.0 this worked properly.

Any chance you could put that into a test case and bisect the regression?

[Jurica Železnjak]

Minimal test case:

# models.py
from django.db import models


class TestModel(models.Model):
    decimal_field = models.DecimalField(max_digits=10, decimal_places=2)

# tests.py
from decimal import Decimal

from django.db.models import Sum
from django.test import TestCase

from decimal_test.models import TestModel


class DecimalTest(TestCase):
    @classmethod
    def setUpClass(cls):
        super().setUpClass()
        TestModel.objects.bulk_create([
            TestModel(decimal_field=7.85),
            TestModel(decimal_field=-7),
            TestModel(decimal_field=19.85),
            TestModel(decimal_field=-7),
            TestModel(decimal_field=-5),
            TestModel(decimal_field=-7),
            TestModel(decimal_field=-0.85),
            TestModel(decimal_field=-0.85)
        ])

    def testSum(self):
        sum = TestModel.objects.all().aggregate(sum=Sum('decimal_field'))['sum']
        print(sum)
        assert sum == 0, 'Should be zero'
        assert sum == Decimal(0.00)

FYI. Just tested with mysql and sqlite as db, same issue.

[Tim Graham]

Bisected to ebc4ee3369694e6dca5cf216d4176bdefd930fd6. The test only fails on SQLite for me, not on MySQL, PostgreSQL, or Oracle.

[Simon Charette]

Confirmed the test passes on MySQL and PostgreSQL for me as well.

[Claude Paroz]

I think that this new behavior is correct and related to float-to-decimal conversion. The difference stems from the way you initialize the decimal fields. You should use strings (decimal_field='7.85') instead of floats (decimal_field=7.85).

For example:

>>> from decimal import Decimal
>>> Decimal(7.85)
Decimal('7.8499999999999996447286321199499070644378662109375')
>>> Decimal('7.85')
Decimal('7.85')
>>> 

[Tim Graham]

I had the same thought, however, the issue persists after updating the test to use Decimals.

[Sergey Fedoseev]

The problem is that SQLite doesn't really have decimal type, so I'm not sure if this issue can be really fixed. Even before ebc4ee3369694e6dca5cf216d4176bdefd930fd6 on SQLite you'll have this:

In [5]: vals = ['7.85', '-7', '19.85', '-7', '-5', '-7', '-0.85', '-0.85']

In [6]: DecimalModel.objects.values_list(sum(Value(Decimal(val), output_field=DecimalField()) for val in vals), flat=True).first()                                                                                  
Out[6]: Decimal('0.000000')

In [7]: DecimalModel.objects.values_list(sum(Value(Decimal(val), output_field=DecimalField()) for val in vals)*10**15, flat=True).first()                                                                           
Out[7]: Decimal('2.886580')

and this on PostgreSQL:

In [5]: DecimalModel.objects.values_list(sum(Value(Decimal(val), output_field=DecimalField()) for val in vals), flat=True).first()
Out[5]: Decimal('0.00')

In [6]: DecimalModel.objects.values_list(sum(Value(Decimal(val), output_field=DecimalField()) for val in vals)*10**15, flat=True).first()
Out[6]: Decimal('0.00')

So I'm open to any ideas on what can be improved here.

[Claude Paroz]

The fact that SQLite has no real Decimal type and internally convert values to NUMERIC (8-byte IEEE floating point number) could be added to the database notes under ​https://docs.djangoproject.com/en/2.1/ref/databases/#sqlite-notes

Then ​https://docs.djangoproject.com/en/2.1/ref/models/fields/#decimalfield could contain a reference to that same note too.

In other words, if you care about decimal precision, you should NOT use SQLite.

[Jurica Železnjak]

And what about testing? We generally use inmemory-sqlite as a database when running tests. Does that mean that we can't use it anymore?
Also... can't this just be forced when converting back to Python. Something like Decimal(whatever_sqlite_returns).quantize(Decimal('.01') (or whatever decimal places is applicable to the current field/column).

[Claude Paroz]

Documentation ​PR.

[Nick Pope]

Replying to Jurica Železnjak:

And what about testing? We generally use inmemory-sqlite as a database when running tests.

You should really be testing using the same database backend that you use in production.

Otherwise there are any number of subtle bugs that you could encounter such as this one.

[Mariusz Felisiak <felisiak.mariusz@…>]

In b8dff52f:

Fixed #29823 -- Doc'd limitation of DecimalField on SQLite.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c2057205:

[3.0.x] Fixed #29823 -- Doc'd limitation of DecimalField on SQLite.

Backport of b8dff52f440adfb78b40e19ee8bff45373ca2501 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In f452d42:

[2.2.x] Fixed #29823 -- Doc'd limitation of DecimalField on SQLite.

Backport of b8dff52f440adfb78b40e19ee8bff45373ca2501 from master