Code

Opened 2 years ago

Last modified 18 months ago

#17854 new Bug

Problem with DecimalField and big vlues of max_digits, decimal_places, sqlite3 backend

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords: DecimalField bug
Cc: wdoekes Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by ramiro)

For a model field created as

models.DecimalField(max_digits = 200, decimal_places = 100, blank = False, null = False)

While using admin interface to insert a record involving such a DecimalField, the format changes (loss of precision and it uses scientific notation (even in the database)) (Please note - it works properly for low precision values (example - .987654321001234) - but for larger precision values (probably 15 decimal_places or more) it results in loss of precision)

  • django version 1.3.1 and 1.4c1 (don't know about older versions);
  • python 2.6.6;
  • linux;

Attachments (4)

test_ticket17854.patch (1.4 KB) - added by tmitchell 2 years ago.
Test illustrating behavior
test_ticket17854.2.patch (1.3 KB) - added by tmitchell 2 years ago.
Test illustrating problem
django-1.1.x-bug17854-decimalfield-casts.patch (2.9 KB) - added by wdoekes 18 months ago.
Possible fix against very old django.
django-1.1.x-bug17854-decimalfield-casts.patch​ (20 bytes) - added by wdoekes 18 months ago.
REMOVED, IRRELEVANT

Download all attachments as: .zip

Change History (16)

comment:1 follow-up: Changed 2 years ago by anonymous

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

The database is sqlite3

The database field (for sqlite3) is 'decimal' (when the field type in the database is changed to say varchar(n) where n > (values to be stored (in this case probably 202)) - everything works perfectly.

The problem seems to be that django emits the column type as decimal for sqlite3 (for other databases this problem is untested) instead of varchar(max_length + probably 2)

comment:2 in reply to: ↑ 1 ; follow-up: Changed 2 years ago by anonymous

Replying to anonymous:

The database is sqlite3

The database field (for sqlite3) is 'decimal' (when the field type in the database is changed to say varchar(n) where n > (values to be stored (in this case probably 202)) - everything works perfectly.

The problem seems to be that django emits the column type as decimal for sqlite3 (for other databases this problem is untested) instead of varchar(max_length + probably 2)

typo resolution for the above line == instead of varchar(max_digits + probably 2)

comment:3 in reply to: ↑ 2 Changed 2 years ago by anonymous

  • Component changed from Uncategorized to Database layer (models, ORM)

Replying to anonymous:

Replying to anonymous:

The database is sqlite3

The database field (for sqlite3) is 'decimal' (when the field type in the database is changed to say varchar(n) where n > (values to be stored (in this case probably 202)) - everything works perfectly.

The problem seems to be that django emits the column type as decimal for sqlite3 (for other databases this problem is untested) instead of varchar(max_length + probably 2)

typo resolution for the above line == instead of varchar(max_digits + probably 2)

if does not create any side effect(s) a change to this might be the solution === django/db/backends/sqlite3/creation.py

also similar change to respective backends might be the solution (in case a database has limits (unlike python Decimals) on the scale, precision for its corresponding column type)

comment:4 Changed 2 years ago by anonymous

  • Summary changed from DecimalField problem to DecimalField problem (please see a possible solution in comments)

comment:5 Changed 2 years ago by ramiro

  • Summary changed from DecimalField problem (please see a possible solution in comments) to Problem with DecimalField and big vlues of max_digits, decimal_places, sqlite3 backend

comment:6 Changed 2 years ago by ramiro

  • Severity changed from Release blocker to Normal

Changed 2 years ago by tmitchell

Test illustrating behavior

comment:7 Changed 2 years ago by tmitchell

  • Triage Stage changed from Unreviewed to Accepted

This is not specific to the admin, as the attached test shows. Test passes under postgres and mysql but fails under sqlite3.

Last edited 2 years ago by tmitchell (previous) (diff)

Changed 2 years ago by tmitchell

Test illustrating problem

comment:8 Changed 2 years ago by anonymous

This problem exists for postgres and mysql too (along with sqlite3).

for postgres - limit is up to 131072 digits before the decimal point; up to 16383 digits after the decimal point (from postgres website - http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE)

similarly docs on the mysql site mention that a decimal field has a limit of 65 decimal places (but I do not know anything else as I haven't used mysql)

comment:9 Changed 2 years ago by ramiro

  • Description modified (diff)

Would be acceptable to "fix" this by documenting the limitations of the three database engine/adaptors in the DB notes document?

Changed 18 months ago by wdoekes

Possible fix against very old django.

comment:10 Changed 18 months ago by wdoekes

  • Cc wdoekes added

comment:11 Changed 18 months ago by wdoekes

I was assuming the problem is the same as the one I encountered. But perhaps this belongs in a different ticket.

The decimal type is cast as string when constructing a query. But for larger values, MySQL doesn't cast the string to decimal properly. Instead it becomes a lossy float.

That means you get this:

mysql> select 12345678901234567 as value, convert('12345678901234567' + 0.0, decimal(31,0)) as truncated_value;
+-------------------+-------------------+
| value             | truncated_value   |
+-------------------+-------------------+
| 12345678901234567 | 12345678901234570 | 
+-------------------+-------------------+
1 row in set (0.01 sec)

When looking up a value, the comparison fails.

Last edited 18 months ago by wdoekes (previous) (diff)

comment:12 Changed 18 months ago by wdoekes

Never mind my comments. They belong in a different ticket: #19625

Changed 18 months ago by wdoekes

REMOVED, IRRELEVANT

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


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

 
Note: See TracTickets for help on using tickets.