FloatField will not handle infinity values

[oBeattie (oliver@…]

When saving an object with an infinity float in a FloatField, the database throws an error (in MySQL at least):

Traceback (most recent call last):
  File "<console>", line 2, in ?
  File "/Django/obeattie/../obeattie/generic/managers.py", line 104, in sync_for_location
    condition.save()
  File "/opt/local/lib/python2.4/site-packages/django/db/models/base.py", line 243, in save
    ','.join(placeholders)), db_values)
  File "/opt/local/lib/python2.4/site-packages/django/db/backends/util.py", line 12, in execute
    return self.cursor.execute(sql, params)
  File "/opt/local/lib/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/opt/local/lib/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'inf' in 'field list'")

While I realize that while this may not be a problem with Django itself, it certainly is a flaw, as Python does support infinite floats? Would it be possible to work around this at all (without resorting to 999… values?)

[Malcolm Tredinnick]

Since FloatField isn't a true float field at all, rather a fixed-precision field, this is all a bit moot. We will shortly be renaming FloatField to DecimalField and probably introducing a real FloatField.

Leaving this open until FloatField is implemented (or rejected) so that we can remember to test that infinity is supported for those database backends that can do it.

[Erin Kelly]

Test cases for storing inf, -inf, nan in a FloatField

[Erin Kelly]

Running the test cases against each of the backends produces these results:

• postgresql: passes
• postgresql_psycopg2: fails -- the insert sql appears to be incorrect
• sqlite3: passes
• mysql: fails -- the insert sql appears to be incorrect
• oracle: fails

Oracle 10g includes new BINARY_FLOAT and BINARY_DOUBLE types, and the tests pass if I modify the Oracle type mapping to use BINARY_DOUBLE rather than the current DOUBLE PRECISION. However, we're trying to also support Oracle 9i, which lacks those types, so I'm a bit hesitant to go ahead and do that.

[calmez]

I looked at this with django from trunk and could reproduce the bug (working in sqlite3, not working in mysql).
I had a look at mysql an found out that it is not possible to store infinity values in DOUBLE columns because "Standard SQL's FLOAT (not just MySQL's) is *not* an IEEE754 float" (1).
I patched it by using the smallest (-2147483648) and the biggest (2147483647) float value as -inf and inf.

Would it be okay to fix that bug that way?

---

(1) ​http://forums.mysql.com/read.php?39,220571,220573#msg-220573

[Luke Plant]

As discussed on django-devs, the approach in this patch is certainly not one we would consider. We will not work around lack of support for this feature in the database backend, and certainly not with magic values.

So, given that the original bug is basically fixed for DBs that support it, the only thing left to do for this ticket, as Malcolm said, is to add tests for the DBs that do support it.

So I'm marking as 'needs tests' for that reason, and so that this ticket does not appear on the 'patches needing review' report - but not to say that the current patch would be appropriate and just needs tests.

[calmez]

I did another patch that adds the feature to enable saving infinity values on FloatFields. After enabling this a check happens if the used database is supporting this. By default this support is disabled. I enabled it for the sqlite backend since it can store infinity values in float columns.
Now the user can decide to be able to store infinity. If the user's setup does not allow storing infinity the reaction to that will be more appropriate.

[Adam Nelson]

As of !MySQL 5.1.24, infinite values ​produces a NULL result instead of +/-inf. I'm not sure if this changes the test at all and I'm presuming !MySQL < 5.1.24 isn't much supported anyway - which would be consistent with !Postgres 8.1 no longer being supported.

[maxime.bargiel@…]

There was a similar issue with DecimalField validation (see https://code.djangoproject.com/ticket/7777) that has been fixed by simply rejecting float('inf') and float('nan') values during validation.

In Django 1.4.2, custom validation needs to be implemented by the developers to exclude NaN and Inf, otherwise the error described in this ticket will occur. Nothing leads me to believe it's different in Django 1.5. Is there a reason why FloatFields should behave differently than DecimalFields with regards to NaN/Inf? While I understand that it might be interesting to have support for NaN/Inf saved in the DB, I think it's more important to have a robust system until this feature is supported.

If necessary, I'll open a new ticket.

[dlanger]

​https://github.com/django/django/pull/1559 passes on 2.7 with sqlite and MySQL.

[Tim Graham <timograham@…>]

In cc957cb16cfdad7e6c9e97dc885fc415abbf5eaa:

Fixed #4287 -- Fixed NaN and +/- Infinity handling in FloatField

NaN, +Inf, and -Inf are no longer valid values for FloatFields.