[Django] #17854: DecimalField problem
Django
---------------------------------+------------------------------
Reporter: anonymous | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 1.3
Severity: Release blocker | Keywords: DecimalField bug
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+------------------------------
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;
--
Ticket URL: <https://code.djangoproject.com/ticket/17854>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
----------------------------------+--------------------------------------
Type: Bug | Status: new
Component: Uncategorized | Version: 1.3
Needs tests: 0 | Patch needs improvement: 0
Changes (by anonymous):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
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)
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:1>
Django
----------------------------------+--------------------------------------
Type: Bug | Status: new
Component: Uncategorized | Version: 1.3
Comment (by anonymous):
Replying to [comment:1 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)
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage:
Keywords: DecimalField bug | Unreviewed
Changes (by anonymous):
* component: Uncategorized => Database layer (models, ORM)
Comment:
Replying to [comment:2 anonymous]:
> Replying to [comment:1 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)
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage:
Keywords: DecimalField bug | Unreviewed
Has patch: 0 | Needs documentation: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:4>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage:
Keywords: DecimalField bug | Unreviewed
Has patch: 0 | Needs documentation: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:5>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Has patch: 0 | Needs documentation: 0
Changes (by ramiro):
* severity: Release blocker => Normal
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:6>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by tmitchell):
* stage: Unreviewed => Accepted
Comment:
This is not specific to the admin, as the attached patch shows. This
patch works under postgres but fails under sqlite3.
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:7>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (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)
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:8>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
> 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;
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
properly for low precision values (example - .987654321001234) - but for
larger precision values (probably 15 decimal_places or more) it results in
loss of precision)
* python 2.6.6;
* linux;
--
Comment (by ramiro):
Would be acceptable to "fix" this by documenting the limitations of the
three database engine/adaptors in the DB notes document?
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:9>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: wdoekes (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:10>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (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, then comparison fails.
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:11>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: DecimalField bug | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by wdoekes):
Never mind my comments. They belong in a different ticket: #19625
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:12>
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by wdoekes):
* Attachment "django-1.1.x-bug17854-decimalfield-casts.patch" removed.
REMOVED, IRRELEVANT
Django
sqlite3 backend
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by wdoekes):
* Attachment "django-1.1.x-bug17854-decimalfield-casts.patch" removed.
Possible fix against very old django.
Django
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:11>
Django
DecimalField max_digits, decimal_places
--------------------------------------+------------------------------------
Reporter: anonymous | Owner: nobody
Type: New feature | Status: new
Component: Core (System checks) | Version: master
Severity: Normal | Resolution:
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* type: Bug => New feature
* version: 1.3 => master
* component: Database layer (models, ORM) => Core (System checks)
Comment:
I think we could add database-specific checks (e.g.
`django/db/backends/mysql/validation.py`) for the maximum values of
`max_digits`, `decimal_places` supported by each database.
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:12>
Django
DecimalField max_digits, decimal_places
Reporter: anonymous | Owner: Yu Chen
| Lee
Type: New feature | Status: assigned
Component: Core (System | Version: dev
checks) |
Severity: Normal | Resolution:
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by Yu Chen Lee):
* has_patch: 0 => 1
* owner: nobody => Yu Chen Lee
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:13>
Django
DecimalField max_digits, decimal_places
-------------------------------------+-------------------------------------
Reporter: anonymous | Owner: Yu Chen
| Lee
Type: New feature | Status: assigned
Component: Core (System | Version: dev
checks) |
Severity: Normal | Resolution:
Keywords: DecimalField bug | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Changes (by Sarah Boyce):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/17854#comment:14>