Opened 9 years ago

Closed 3 years ago

#4287 closed Bug (fixed)

FloatField will not handle infinity values

Reported by: oBeattie (oliver@… Owned by: dlanger
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: nan, infinity, mysql, float
Cc: oliver@…, calmez, maxime.bargiel@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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/", line 104, in sync_for_location
  File "/opt/local/lib/python2.4/site-packages/django/db/models/", line 243, in save
    ','.join(placeholders)), db_values)
  File "/opt/local/lib/python2.4/site-packages/django/db/backends/", line 12, in execute
    return self.cursor.execute(sql, params)
  File "/opt/local/lib/python2.4/site-packages/MySQLdb/", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/opt/local/lib/python2.4/site-packages/MySQLdb/", 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?)

Attachments (3)

4287_tests.diff (1.4 KB) - added by Ian Kelly 9 years ago.
Test cases for storing inf, -inf, nan in a FloatField
inf-bug.patch (1.2 KB) - added by calmez 5 years ago.
inf-bug.2.patch (3.3 KB) - added by calmez 5 years ago.

Download all attachments as: .zip

Change History (16)

comment:1 Changed 9 years ago by Malcolm Tredinnick

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedAccepted

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.

Changed 9 years ago by Ian Kelly

Attachment: 4287_tests.diff added

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

comment:2 Changed 9 years ago by Ian 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.

comment:3 Changed 5 years ago by Łukasz Rekucki

Severity: Normal
Type: Bug

comment:4 Changed 5 years ago by 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?


Changed 5 years ago by calmez

Attachment: inf-bug.patch added

comment:5 Changed 5 years ago by calmez

Has patch: set

comment:6 Changed 5 years ago by Luke Plant

Easy pickings: unset
Needs tests: set

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.

comment:7 Changed 5 years ago by calmez

Cc: calmez added
UI/UX: unset

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.

Changed 5 years ago by calmez

Attachment: inf-bug.2.patch added

comment:8 Changed 5 years ago by 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.

comment:9 Changed 4 years ago by maxime.bargiel@…

Cc: maxime.bargiel@… added
Keywords: nan added

There was a similar issue with DecimalField validation (see 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.

comment:10 Changed 3 years ago by dlanger

Owner: changed from nobody to dlanger
Status: newassigned

comment:11 Changed 3 years ago by dlanger passes on 2.7 with sqlite and MySQL.

comment:12 Changed 3 years ago by dlanger

Needs tests: unset

comment:13 Changed 3 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In cc957cb16cfdad7e6c9e97dc885fc415abbf5eaa:

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

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

Note: See TracTickets for help on using tickets.
Back to Top