Specific DecimalField with integer part longer than 15 digits can be stored but not retrieved on SQLite

[Orazio]

Similar to https://code.djangoproject.com/ticket/33954 but different exception:

Given this model

class Apple(models.Model):
	weight = models.DecimalField(max_digits=16, decimal_places=0)

Trying to store 9999999999999999 (16 digits)

$ python manage.py shell
7 objects imported automatically (use -v 2 for details).

Python 3.12.3 (main, Feb  4 2025, 14:48:35) [GCC 13.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> test = Apple(weight="9999999999999999")
>>> test.full_clean()
>>> test.save()
>>> Apple.objects.last()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/user/test_project/django/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/test_project/django/django/db/models/query.py", line 1105, in last
    for obj in queryset[:1]:
  File "/home/user/test_project/django/django/db/models/query.py", line 383, in __iter__
    self._fetch_all()
  File "/home/user/test_project/django/django/db/models/query.py", line 1923, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/test_project/django/django/db/models/query.py", line 122, in __iter__
    for row in compiler.results_iter(results):
  File "/home/user/test_project/django/django/db/models/sql/compiler.py", line 1540, in apply_converters
    value = converter(value, expression, connection)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/test_project/django/django/db/backends/sqlite3/operations.py", line 346, in converter
    return create_decimal(value).quantize(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
decimal.InvalidOperation: [<class 'decimal.InvalidOperation'>]
>>>

I made sure to test with the latest commit

$ pip list
Package  Version               Editable project location
-------- --------------------- -------------------------
asgiref  3.8.1
Django   6.0.dev20250306010223 /home/user/test_project/django
pip      24.0
sqlparse 0.5.3

Exception is raised here: ​https://github.com/django/django/blob/bad1a18ff28a671f2fdfd447bdf8f43602f882c2/django/db/backends/sqlite3/operations.py#L346

As you might imagine this also fails when say max_digits is 20 and storing 20 nines, and so on. Shouldn't this raise an error when running full_clean() on the object before storing?

[Hridesh MG]

Was able to reproduce this issue, also noticed that it triggers on a specific number - i.e. test = Apple(weight=9999999999999994) seems to work fine but the moment weight becomes 9999999999999995 it starts to throw the error

[Simon Charette]

I'm not sure if this relates to a particular version of Python or SQLite (I tested with Python 3.12.3 and sqlite3.sqlite_version of 3.46) and if it now implements the decimal data type differently but it appears to be returning an int instance when there are no floating values (like it's the case for decimal_places=0) and in this case the quantization is completely unnecessary. Here's a demonstration of how the code could be adapted to take that into consideration.

django/db/backends/sqlite3/operations.py

@@ -342 +342 @@
             )
 
             def converter(value, expression, connection):
-                if value is not None:
+                if isinstance(value, int):
+                    return decimal.Decimal(value)
+                elif value is not None:
                     return create_decimal(value).quantize(
                         quantize_value, context=expression.output_field.context
                     )
@@ -350 +352 @@
         else:
 
             def converter(value, expression, connection):
-                if value is not None:
+                if isinstance(value, int):
+                    return decimal.Decimal(value)
+                elif value is not None:
                     return create_decimal(value)
 
         return converter

Note that I haven't put much thoughts into it but it seemed adequate to only apply float quantization when provided float instances?

I'm unsure what the purpose of using DecimalField(decimal_places=0) over an IntegerField is though. Assuming there is one I assume the fact it's pretty niche why this issue has flew under the radar for so long.

Could you elaborate on why this should be supported in the first place?

[Orazio]

I omitted the decimal part because it didn’t seem relevant but it actually occurs when the integer part has more than 15 digits, such as in the following model as well:

class Apple(models.Model):
	weight = models.DecimalField(max_digits=21, decimal_places=5)

While storing the same number 9999999999999999

[Simon Charette]

I omitted the decimal part because it didn’t seem relevant but it actually occurs when the integer part has more than 15 digits, such as in the following model as well:

Makes sense, thanks for the clarification.

[Orazio]

By the way even DecimalField(decimal_places=0) makes sense to me in case the number to be stored might be bigger than (2^63)-1.

[Hridesh MG]

Hi there, I'm new to django contributions (and the ORM internals in general) but what I've gathered so far from my testing is that the quantize() operation fails because the number it is trying to quantize has a precision larger than the context variable passed to it allows.

Model Definition:

class Apple(models.Model):
    weight = models.DecimalField(max_digits=16, decimal_places=2)

Relevant block of code in sqlite3/operations.py

    def get_decimalfield_converter(self, expression):
        # SQLite stores only 15 significant digits. Digits coming from
        # float inaccuracy must be removed.
        create_decimal = decimal.Context(prec=15).create_decimal_from_float
        if isinstance(expression, Col):
            quantize_value = decimal.Decimal(1).scaleb(
                -expression.output_field.decimal_places
            )

            def converter(value, expression, connection):
                if value is not None:
                    return create_decimal(value).quantize(
                        quantize_value, context=expression.output_field.context
                    )

        else:

            def converter(value, expression, connection):
                if value is not None:
                    return create_decimal(value)

        return converter

The context variable passed to quantize() (expression.output_field.context):

Context(prec=16, rounding=ROUND_HALF_EVEN, Emin=-999999, Emax=999999, capitals=1, clamp=0, flags=[], traps=[InvalidOperation, DivisionByZero, Overflow])

---

When decimal_places is 0 and the input is 9999999999999999 (16 digits) the result of the quantization is 10000000000000000 (17 digits), this will raise an InvalidOperation exception, see - ​https://docs.python.org/3/library/decimal.html#decimal.Decimal.quantize. I assume this rounding occurs because of SQLite's quirk of only preserving the first 15 significant bits.

I hope the above made sense. I'm not sure what approach I should follow to fix this issue, does it make sense to increase the precision? Any help would be appreciated.

[Hridesh MG]

I've written the following test to reproduce this issue, it can be added to tests/model_fields/test_decimalfield.py

    @skipUnless(
        connection.vendor == "sqlite",
        "Only applies due to SQLite not having a native Decimal type",
    )
    def test_sqlite_precision_rounding_edge_case(self):
        """Test that DecimalField can retrieve values when precision exceeds SQLite's 15-digit limit."""
        edge_value = Decimal("9" * HighPrecision._meta.get_field("d").max_digits)
        obj = HighPrecision(d=edge_value)
        obj.full_clean()
        obj.save()

        obj.refresh_from_db()
        self.assertIsInstance(obj.d, Decimal)

Corresponding model in tests/model_fields/models.py

class HighPrecision(models.Model):
    d = models.DecimalField(max_digits=16, decimal_places=0)