Re: [Django] #36233: Specific DecimalField with integer part longer than 15 digits can be stored but not retrieved on SQLite

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------
Reporter: Orazio | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:5>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------
Reporter: Orazio | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:6>

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------
Reporter: Orazio | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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])
}}}

----

As we can see, the context variable passed to quantize() expects the
result of the quantization to have a precision of 16, however, if the
input is 9999999999999999 (16 digits), the actual number of significant
digits after the quantize operation becomes 18 (9999999999999999.00). This
will raise an InvalidOperation exception, see -
[https://docs.python.org/3/library/decimal.html#decimal.Decimal.quantize]

In the scenario where decimal_places is 0 and the input is
9999999999999999 (16 digits) the result of the quantization is
10000000000000000 (17 digits), i assume this is because of SQLite's quirk
of only preserving the first 15 significant bits. So the root cause is the
same for both errors.

----

I hope the above made sense. I'm not sure what approach I should follow to
fix this issue, should we prevent the user from entering a value which has
more significant digits than SQLite can handle or should we modify the
precision that quantize() expects? Any pointers would be appreciated!
--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:7>

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------

Reporter: Orazio | Owner: Hridesh
| MG
Type: Bug | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Hridesh MG):

* owner: (none) => Hridesh MG
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:8>

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------
Reporter: Orazio | Owner: Hridesh
| MG
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0

Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Hridesh MG):

* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:9>

[Django]

#36233: Specific DecimalField with integer part longer than 15 digits can be stored
but not retrieved on SQLite
-------------------------------------+-------------------------------------
Reporter: Orazio | Owner: Hridesh
| MG
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite, orm, | Triage Stage: Accepted
decimalfield, invalidoperation |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Hridesh MG):

* needs_tests: 1 => 0

Comment:

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

{{{#!python
@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`
{{{#!python
class HighPrecision(models.Model):
d = models.DecimalField(max_digits=16, decimal_places=0)

}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36233#comment:10>