Opened 3 years ago

Last modified 3 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 3 years ago.
Test illustrating behavior
test_ticket17854.2.patch (1.3 KB) - added by tmitchell 3 years ago.
Test illustrating problem
django-1.1.x-bug17854-decimalfield-casts.patch (2.9 KB) - added by wdoekes 3 years ago.
Possible fix against very old django.
django-1.1.x-bug17854-decimalfield-casts.patch​ (20 bytes) - added by wdoekes 3 years ago.
REMOVED, IRRELEVANT

Download all attachments as: .zip

Change History (16)

comment:1 follow-up: Changed 3 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 3 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 3 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)
http://lockerdome.com/blog

Last edited 3 months ago by johnhomes (previous) (diff)

comment:4 Changed 3 years ago by anonymous

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

comment:5 Changed 3 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 3 years ago by ramiro

  • Severity changed from Release blocker to Normal

Changed 3 years ago by tmitchell

Test illustrating behavior

comment:7 Changed 3 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 but fails under sqlite3.

Version 2, edited 3 years ago by tmitchell (previous) (next) (diff)

Changed 3 years ago by tmitchell

Test illustrating problem

comment:8 Changed 3 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 3 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 3 years ago by wdoekes

Possible fix against very old django.

comment:10 Changed 3 years ago by wdoekes

  • Cc wdoekes added

comment:11 Changed 3 years 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 3 years ago by wdoekes (previous) (diff)

comment:12 Changed 3 years ago by wdoekes

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

Changed 3 years ago by wdoekes

REMOVED, IRRELEVANT

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