NaN can be stored in DecimalField but cannot be retrieved

[dennisvang]

Description

If, for whatever reason, a NaN value (either float('nan'), math.nan, or numpy.nan) is stored in a DecimalField using sqlite3, the object cannot be retrieved from the database.

Attempts to do so will raise TypeError: argument must be int or float

This issue also breaks e.g. the admin changelist view.

Steps to reproduce

1. Create a brand new project using python 3.8.10 and django 3.2.6 with the default sqlite3 backend (optionally with numpy 1.21.2).

2. Create a model with a DecimalField:

class MyModel(models.Model):
    value = models.DecimalField(max_digits=10, decimal_places=5)

3. Programmatically create a model instance with value=float('nan') (or math.nan, or numpy.nan), then try to retrieve the object from the database (or refresh from database).

obj = MyModel.objects.create(value=float('nan'))
# the following raises a "TypeError: argument must be int or float"
obj.refresh_from_db()  

4. Visiting the admin change view (or changelist view) for the model will also raise the error.

Traceback:

Internal Server Error: /nanbug/mymodel/1/change/
Traceback (most recent call last):
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/options.py", line 616, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/utils/decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/sites.py", line 232, in inner
    return view(request, *args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/options.py", line 1660, in change_view
    return self.changeform_view(request, object_id, form_url, extra_context)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/utils/decorators.py", line 43, in _wrapper
    return bound_method(*args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/utils/decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/options.py", line 1540, in changeform_view
    return self._changeform_view(request, object_id, form_url, extra_context)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/options.py", line 1561, in _changeform_view
    obj = self.get_object(request, unquote(object_id), to_field)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/contrib/admin/options.py", line 763, in get_object
    return queryset.get(**{field.name: object_id})
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/models/query.py", line 431, in get
    num = len(clone)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/models/query.py", line 262, in __len__
    self._fetch_all()
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/models/query.py", line 68, in __iter__
    for row in compiler.results_iter(results):
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1122, in apply_converters
    value = converter(value, expression, connection)
  File "/home/.../.local/share/virtualenvs/howto-GW7qAAiJ/lib/python3.8/site-packages/django/db/backends/sqlite3/operations.py", line 313, in converter
    return create_decimal(value).quantize(quantize_value, context=expression.output_field.context)
TypeError: argument must be int or float

[Keryn Knight]

Verified on main, problem is that the value returned from sqlite is NaN as a string, rather than float('nan'), and create_decimal_from_float is strict about it's accepted types. I've not verified whether it would affect any other backends, but presuming it doesn't, it shouldn't be too problematic to fix the converter given it'll only apply for the sqlite3 backend.

ipdb> converter
<function DatabaseOperations.get_decimalfield_converter.<locals>.converter at 0x1119105e0>
ipdb> type(value)
<class 'str'>
ipdb> value
'NaN'

[Mariusz Felisiak]

Replying to Keryn Knight:

Verified on main, problem is that the value returned from sqlite is NaN as a string, rather than float('nan'), and create_decimal_from_float is strict about it's accepted types. I've not verified whether it would affect any other backends, but presuming it doesn't, it shouldn't be too problematic to fix the converter given it'll only apply for the sqlite3 backend.

Storing NaN also doesn't work on MySQL and Oracle. Moreover DecimalValidator raises ValidationError on it. I don't think it's supported, I'd adjust DecimalField.to_python() to raise an exception in this case.

[dennisvang]

Unfortunately I don't have a postgresql database available at the moment to test the minimal example, but using postgresql in production, NaN values caused errors at a later stage, viz. during template rendering. For example:

TypeError: bad operand type for abs(): 'str'

in

... django/utils/numberformat.py", line 44, in format
    if abs(exponent) + len(digits) > 200:

[Mariusz Felisiak]

NaN values caused errors at a later stage, viz. during template rendering. For example: ...

Exactly, as far as I'm aware it has never been supported. That's why I'd adjust DecimalField.to_python() to raise an exception in this case.

[Chinmoy]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In b7fd668b:

Fixed #33033 -- Prevented models.DecimalField from accepting NaN values.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 6f31041:

[4.0.x] Fixed #33033 -- Prevented models.DecimalField from accepting NaN values.

Backport of b7fd668b37341fc92d67c4854c4f244e10895c9b from main