Code

Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#6767 closed (fixed)

Oracle backend treats DecimalFields as floats somewhere, losing precision

Reported by: mboersma Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: oracle Oracle decimal float precision
Cc: ikelly, richard.davies@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (2)

6767.diff (4.1 KB) - added by ikelly 5 years ago.
Prevents float conversions by pulling in numeric data as strings and casting them by hand. Testing is covered by the regression test for #5079.
6767-2.diff (3.3 KB) - added by ikelly 5 years ago.
Patch that doesn't remove field-based number formatting of decimal objects.

Download all attachments as: .zip

Change History (14)

comment:1 Changed 6 years ago by mboersma

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 6 years ago by ikelly

  • Cc ikelly added; ian.g.kelly@… removed

comment:3 Changed 6 years ago by PhiR

#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.

comment:4 Changed 6 years ago by ikelly

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

comment:5 Changed 6 years ago by Simon Greenhill

  • Triage Stage changed from Unreviewed to Accepted

comment:6 Changed 6 years ago by Richard Davies <richard.davies@…>

  • Cc richard.davies@… added

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

comment:7 Changed 6 years ago by kmtracey

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.

Changed 5 years ago by ikelly

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

Changed 5 years ago by ikelly

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

comment:8 Changed 5 years ago by ikelly

  • Resolution set to fixed
  • Status changed from new to closed

(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.

comment:9 Changed 5 years ago by tlow

  • Resolution fixed deleted
  • Status changed from closed to reopened

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

comment:10 Changed 5 years ago by tlow

Sorry, this way:

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

comment:11 Changed 5 years ago by ikelly

  • Resolution set to fixed
  • Status changed from reopened to closed

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

comment:12 Changed 5 years ago by ikelly

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.