Oracle backend treats DecimalFields as floats somewhere, losing precision

[Matt Boersma]

This is really a cx_Oracle limitation. One workaround Ian Kelly and I discussed is to set cursor.numbersAsStrings = True and then Do The Right Thing in converting from strings. (The cx_Oracle rowfactory callback mechanism could be a nice way to handle this.)

>>> from foo.models import Address
>>> from decimal import Decimal
>>> a = Address(latitude=Decimal('-105.2513837814331'))
>>> a.save()
>>> a.latitude
Decimal("-105.2513837814331")
>>> b = Address.objects.get(pk=a.pk)
>>> b.latitude
Decimal("-105.25138378143301000000000000000000000")

Nothing in the test suite catches this! We need to add a test for this before creating the fix.

[Matt Boersma]

Apparently we get a similar failure in the sqlite3 backend. I would assume this is considered an error there as well, but if this is somehow expected behavior please add a comment here to that effect.

[Philippe Raoult]

#5079 has patch which includes a similar test. There's also a fix in there, which may or may not resolve your issue :) If you have an oracle DB handy and want to run the tests on it be my guest.

[Erin Kelly]

As I commented at #5079, the patch there doesn't fix this problem, so there may actually be multiple bugs where Oracle is concerned.

[Richard Davies <richard.davies@…>]

For reference in case it is useful, I had a similar problem specifically with the MySQL backend, and have fixed my issue with a patch to MySQLdb

​http://sourceforge.net/tracker/index.php?func=detail&aid=2051833&group_id=22307&atid=374934

[Karen Tracey]

This problem (for Oracle) relates to retrieval of the data, whereas #5079 relates to storing it in the first place.

In #5079 the data goes through a float conversion before being sent to the DB in a string format. As a result the actual values stored in the database may be incorrect due to float limitations. Testing (via attempting to save Decimal("0.7") shows #5079 currently affects MySQL, PostgreSQL, and Oracle, but not sqlite.

Fixing #5079 does not fix this problem for Oracle, because when retrieving the data from Oracle it is coming back from the DB as a float. You can see that by using raw sql queries under a manage.py shell -- the type for the data in the tuples returned is float. However if you look at the data using the Oracle web browser, you can see it has been stored in the DB correctly (when #5079 is fixed), it is just getting mangled on retrieval. I don't know how to get the db connector to use Decimals instead of floats for retrieval of this kind of data.

sqlite (as noted above) also fails on this case. But the reason is different. sqlite doesn't suffer from #5079, exactly, but maybe a variant. It seems that any values stores are rounded to 15 significant digits. So if I store:

Decimal(".251383781433984125")
I see:  0.251383781433984 via sqlite3 shell and I get back the same as a Decimal on a fetch.

It is rounding, not truncating, since if I change the ending "4125" to "4525" the stored value ends in 5 instead of 4.

I'll hopefully fix #5079 soon, this ticket should probably be used to fix the fact that the Decimals coming back from Oracle come in as floats (I have no idea how to do that) and we probably need a 3rd ticket (if there isn't already one) for Decimals getting rounded to 15 digits for storage on sqlite. I also have no idea where that is happening.

[Erin Kelly]

Prevents float conversions by pulling in numeric data as strings and casting them by hand. Testing is covered by the regression test for #5079.

[Erin Kelly]

Patch that doesn't remove field-based number formatting of decimal objects.

[Erin Kelly]

(In [9751]) [1.0.X] Fixed #6767: changed the way the Oracle backend fetches numbers to prevent decimals being returned as floats. Backport of [9750] from trunk.

[tlow]

Decimal module doesn't exist in Python 2.3. Use Django's decimal module as fallback.

-from decimal import Decimal
+try:
+       from decimal import Decimal
+except ImportError:
+       from django.utils import _decimal as Decimal

[tlow]

Sorry, this way:

-from decimal import Decimal
+try:
+       from decimal import Decimal
+except ImportError:
+       from django.utils._decimal import Decimal

[Erin Kelly]

(In [9761]) Fixed #6767: corrected a 2.3 compatibility issue in [9750]. Thanks to tlow.

[Erin Kelly]

(In [9762]) [1.0.X] Fixed #6767: corrected a 2.3 compatibility issue in [9750]. Thanks to tlow. Backport of [9761] from trunk.

[GitHub <noreply@…>]

In f32ee6d:

Refs #6767 -- Added test for fetching decimal values without rounding error on Oracle.