Decimal error when upgrading from Django 3.1 to Django 3.2

Attempting to upgrade from Django 3.1 to Django 3.2 caused one of our Decimal handling unit tests to fail.

We run the unit tests against two backends: MySQL and SQLite. Interestingly, the failure only occurred against the MySQL backend.

Narrowing down the failure §

The failing test was quite complex, so we created additional simpler tests to narrow down the exact failure. In essence, it boiled down to:

import decimal

# Calculate 979 / 176 in decimal, save to DB, then read back from DB.
save_to_db(decimal.Decimal("979") / decimal.Decimal("176"))
value = read_from_db()

#   Backend: | MySQL  SQLite
# -----------+--------------
# Django 3.1 | Pass   Pass
# Django 3.2 | Fail   Pass    <-- Uh oh! 💥
assert value == decimal.Decimal("5.562")

We were expecting 5.562, but with Django 3.2 and MySQL were getting 5.563.

You might have already spotted that 979 / 176 = 5.5625, not 5.562. However, 5.562 was expected because we have decimal_places=3 in our model:

value = models.DecimalField(
    null=True,
    decimal_places=3,
    max_digits=12,
)

So it appeared that under Django 3.1 the decimal calculation was always being rounded down, and under Django 3.2 with a MySQL backend it was being rounded up. However…

Checking the Django 3.2 release notes §

Nothing in the Django 3.2 release notes seemed to mention this change!

The only mentions of anything “decimal” related is:

Value() expression now automatically resolves its output_field to the appropriate Field subclass based on the type of its provided value for bool, bytes, float, int, str, datetime.date, datetime.datetime, datetime.time, datetime.timedelta, decimal.Decimal, and uuid.UUID instances. As a consequence, resolving an output_field for database functions and combined expressions may now crash with mixed types when using Value(). You will need to explicitly set the output_field in such cases.

And:

• The new DatabaseFeatures.introspected_field_types property replaces these features:
  • …
  • can_introspect_decimal_field
  • …

Neither of these changes obviously call out any changes to decimal rounding. So we do some more searching.

The culprit §

Django bug #33232 describes the exact same situation, except with the Postgres backend rather than MySQL. In the given example, the value Decimal("1.125") is being saved to the database in a model with decimal_places=2. The subsequent query being executed under Django 3.1 is:

INSERT INTO "myapp_foo" ("amount") VALUES ('1.12') RETURNING "myapp_foo"."id"

Whereas under Django 3.2 it changes to:

INSERT INTO "myapp_foo" ("amount") VALUES (1.125) RETURNING "myapp_foo"."id"

This shows that under Django 3.1, the decimal value is first converted to a string which is truncated to the corresponding number of decimal places; while under Django 3.2 the whole decimal value is stored in the database (and is then presumably rounded to the correct number of decimal places upon reading).

This difference is confirmed by Mariusz Felisiak, who says:

This behavior was changed in 9c5c9bd7091b60fdccc405890dc4f44a8010e954 however I believe that the current one is correct, because we longer cast Decimals to strings. If you need to round differently I recommend to do so before passing values to the field.

Commit 9c5c9bd7091b60fdccc405890dc4f44a8010e954 (mentioned in the quote) stops adapting DecimalField values to strings with PostgreSQL. With some digging, we find commit bf7148772c68c6543b4fcc75c61d0313eb700066 which appears to do the same but for MySQL. However, there is no such change for SQLite.

So in short:

• Django 3.1 converts the decimal to string and truncates before saving to database.
• Django 3.2 saves to the database as decimal and then rounds when reading (except for SQLite, which uses the old behaviour).

Why is SQLite different? §

The question remains: why is SQLite treated differently?

This is answered by Simon Charette, who says:

SQLite is also not a stellar example of ambiguous data type handling as detailed in their documentation. Since it doesn’t have a proper type to store decimal data the rounding happens on the Django side.

This lead me to read the SQLite documentation about Type Affinity, which included some surprising points:

The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data.

As this answer demonstrates, marking a SQLite column as DECIMAL(10, 5) is therefore more of a suggestion than any form of constraint or validation.

So with the SQLite backend, the truncation still occurs because Django still converts the decimal to a string before saving, due to the nuances of how SQLite handles decimals.

How does this fit in the release notes? §

The commit that Mariusz Felisiak mentioned belongs to PR #13184, which is part of PR #11359, which resolves issue #30446, which is titled “Automatically resolve Value’s output_field for stdlib types”.

That seems to bring us full circle, since that appears to be the issue in the Django 3.2 release notes that begins:

Value() expression now automatically resolves its output_field to the appropriate Field subclass based on the type of its provided value […]

Though the connection wasn’t obvious!

Solution(s) §

In our case, the new behaviour is actually preferable, so we updated our unit tests to expect the rounding behaviour, e.g.:

#   Backend: | MySQL  SQLite
# -----------+--------------
# Django 3.1 | Fail   Fail    <-- Can ignore
# Django 3.2 | Pass   Fail
assert value == decimal.Decimal("5.563")

We can ignore the failures under Django 3.1 since we are switching to Django 3.2; however, this still leaves the failure under SQLite, which continues to perform truncation and so expects 5.562.

Since we do not use SQLite in production, and only actually use it for a subset of our unit tests, we took the action of adding an exception for the unit tests under SQLite to expect the truncating behaviour, e.g.:

#   Backend: | MySQL  SQLite
# -----------+--------------
# Django 3.1 | Fail   Pass    <-- Can ignore
# Django 3.2 | Pass   Pass    <-- All good!
if backend == "SQLite":
    # <Comment explaining why we handle SQLite differently>.
    assert value == decimal.Decimal("5.562")
else:
    assert value == decimal.Decimal("5.563")

The perfectionist in me would really like Django + SQLite to have the exact same decimal handling behaviour as Django + MySQL, but it’s just not necessary in our particular case.

If I did need the exact same behaviour, I might suggest:

• (Short term) Store 1 more decimal place than you need in the model, then perform the rounding when you read. For example, if you need 3 decimal places, use decimal_places=4 in the model, then round to 3 decimal places upon read.
• (Long term) Submit a pull request to fix the issue upstream in Django, by rounding before converting to a string and truncating with the SQLite backend.

Conclusion §

I agree that the new behaviour of rounding (rather than truncating) is correct and preferable, though it is rather surprising to find it works significantly different in Django + SQLite.

However, I am grateful to the Django developers and community for providing the well connected public papertrail of issues and conversations that helped explain what is going on.