# New Error in 4.2 when querying sql db for too large decimals

**URL:** <https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943>\
**Category:** ORM\
**Created:** [May 15, 2023, 2:27pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943 "2023-05-15T14:27:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mcoder](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mcoder/32/13717_2.png) [@mcoder](https://forum.djangoproject.com/u/mcoder)\
**Post date:** [May 15, 2023, 2:27pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/1 "2023-05-15T14:27:27Z")

</div>

I am unsure if this qualifies as a bug, but it is a change in behavior from django 4.1 to 4.2, caused by the changes in db.backends for psycopg3 [https://code.djangoproject.com/ticket/33308](https://code.djangoproject.com/ticket/33308). It happens for an SQL db but not in oracle.

Example:  
I have DecimalField with `max_digits=5`. Previously, querying for something larger (e.g. 123456) would execute the sql and return ObjectNotFound. Now in 4.2, it throws a decimal.InvalidOperation error, as it tries to quantize the value to have 5 digits.

```auto
Model.objects.get(id=123456)

```

```auto
Traceback (most recent call last):
  File "lib/python3.10/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "lib/python3.10/site-packages/django/db/models/query.py", line 633, in get
    num = len(clone)
  File "lib/python3.10/site-packages/django/db/models/query.py", line 380, in __len__
    self._fetch_all()
  File "lib/python3.10/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "lib/python3.10/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
  File "lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1547, in execute_sql
    sql, params = self.as_sql()
  File "lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 762, in as_sql
    self.compile(self.where) if self.where is not None else ("", [])
  File "lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 544, in compile
    sql, params = node.as_sql(self, self.connection)
  File "lib/python3.10/site-packages/django/db/models/sql/where.py", line 145, in as_sql
    sql, params = compiler.compile(child)
  File "lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 544, in compile
    sql, params = node.as_sql(self, self.connection)
  File "lib/python3.10/site-packages/django/db/models/lookups.py", line 357, in as_sql
    return super().as_sql(compiler, connection)
  File "lib/python3.10/site-packages/django/db/models/lookups.py", line 225, in as_sql
    rhs_sql, rhs_params = self.process_rhs(compiler, connection)
  File "lib/python3.10/site-packages/django/db/models/lookups.py", line 126, in process_rhs
    return self.get_db_prep_lookup(value, connection)
  File "lib/python3.10/site-packages/django/db/models/lookups.py", line 254, in get_db_prep_lookup
    else [get_db_prep_value(value, connection, prepared=True)],
  File "lib/python3.10/site-packages/django/db/models/fields/ __init__.py", line 1761, in get_db_prep_value
    return connection.ops.adapt_decimalfield_value(
  File "lib/python3.10/site-packages/django/db/backends/base/operations.py", line 574, in adapt_decimalfield_value
    return utils.format_number(value, max_digits, decimal_places)
  File "lib/python3.10/site-packages/django/db/backends/utils.py", line 304, in format_number
    value = value.quantize(
decimal.InvalidOperation: [<class 'decimal.InvalidOperation'>]

```

I understand that it doesn’t make sense to query for a larger number, but the error that occurs was pretty confusing to me. Also, it is not as easy to check in my application, because I don’t have easy access to the `max_digits` parameter of the field.  
So my question is: Should this be reported as a bug, since the behavior changed without documentation?

In my opinion, the backend should either accept larger values and always return “not found”, or the error should be more descriptive, so that it can be caught specifically.

This is my first post, I hope the formatting is ok.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [May 16, 2023, 6:05pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/2 "2023-05-16T18:05:16Z")

</div>

If you can confirm this is a regression in 4.2 then I would open a ticket for it. [If you can bisect the exact commit that caused the regression that would be appreciated](https://docs.djangoproject.com/en/4.2/internals/contributing/triaging-tickets/#bisecting-a-regression)

We have mechanism in place today that [allows us to even reach to the database if provided an invalid value](https://github.com/django/django/commit/dde2537fbb04ad78a673092a931b449245a2d6ae) so I assume a similar approach could be taken here.

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [May 17, 2023, 8:40pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/3 "2023-05-17T20:40:16Z")

</div>

> [@charettes](#):
>
> We have mechanism in place today that [allows us to even reach to the database if provided an invalid value](https://github.com/django/django/commit/dde2537fbb04ad78a673092a931b449245a2d6ae) so I assume a similar approach could be taken here.

I understand you meant: “We have a mechanism in place today that allows us to even skip reaching to the database if provided an invalid value”. That’s neat.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [May 17, 2023, 9:38pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/4 "2023-05-17T21:38:04Z")

</div>

🤦‍♂️ yep you deciphered my answer correctly

---

<div class="post-metadata">

**Author:** ![mcoder](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mcoder/32/13717_2.png) [@mcoder](https://forum.djangoproject.com/u/mcoder)\
**Post date:** [May 22, 2023, 1:17pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/5 "2023-05-22T13:17:53Z")

</div>

Thanks for the quick responses. Issue created here: [#34590 (Regression in 4.2 when querying db for too large decimals) – Django](https://code.djangoproject.com/ticket/34590)

Bisecting returned "Refs [#33308](https://code.djangoproject.com/ticket/33308) – Improved adapting DecimalField values to decimal. " as the culprit.

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [May 22, 2023, 1:31pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/6 "2023-05-22T13:31:23Z")

</div>

link to commit: [Refs #33308 -- Improved adapting DecimalField values to decimal. · django/django@7990d25 · GitHub](https://github.com/django/django/commit/7990d254b0af158baf827fafbd90fe8e890f23bd)

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [May 22, 2023, 1:42pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/7 "2023-05-22T13:42:28Z")

</div>

Hi mcoder,

btw which database were you using? Trying out the test in the ticket with postgres shows it working as expected, but there is an issue with sqlite. I didn’t try mysql or oracle yet.

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [May 22, 2023, 2:01pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/8 "2023-05-22T14:01:19Z")

</div>

thanks for your efforts in reporting that and bisecting the commit btw, much appreciated! 🏆❤

---

<div class="post-metadata">

**Author:** ![mcoder](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mcoder/32/13717_2.png) [@mcoder](https://forum.djangoproject.com/u/mcoder)\
**Post date:** [May 22, 2023, 2:42pm UTC](https://forum.djangoproject.com/t/new-error-in-4-2-when-querying-sql-db-for-too-large-decimals/20943/9 "2023-05-22T14:42:45Z")

</div>

I only tried it with sqlite. The changed file also only seems to affect the sqlite backend.