View Issue Details

IDProjectCategoryView StatusLast Update
0005291OXID eShop (all versions)2.6. Administer orderspublic2015-12-01 12:33
Reportervschmi 
PrioritynormalSeverityminorReproducibilitysometimes
Status acknowledgedResolutionreopened 
Product Version4.7.6 / 5.0.6 
Target VersionFixed in Version 
Summary0005291: Order Overview shows too many decimal fractions for order amounts
DescriptionWe're using OXID with decimal fractions enabled. In the order overview, we noticed some strange order amounts, for example 3.3000000000000003, 13.049999999999999 or 7.999999999999999.

This is because the field type is "double", which is unsuitable to represent data like money or amounts. I recommend to change this to "DECIMAL".

As a temporary workaround, one can change list_order.php in _buildSelectString to include round() for oxamount as well.
TagsNo tags attached.
ThemeBoth
BrowserAll
PHP Versionany
MySQL Versionany

Relationships

duplicate of 0005292 resolvedLinas Kukulskis OXID uses double for monetary values, leading to problems all over the shop 

Activities

svetlana

2013-07-15 09:50

reporter   ~0008906

Reminder sent to: vschmi

Hi, unfortunately we can not reproduce it, could you please tell us more details, about this bug? Can you reproduce this bug on our demo shop: http://demoshop.oxid-esales.com/beta/community-edition/

vschmi

2013-07-15 10:22

reporter   ~0008907

Please use a shop installed on a MySQL 5.5 server. I was not able to reproduce this on the test shop, assuming that it runs on MySQL 5.1.

It's very easy to reproduce even without a running OXID shop:

CREATE TABLE IF NOT EXISTS `test` (
  `test` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`test`) VALUES
(0.4),
(1),
(0.2),
(0.5),
(1.65),
(0.5),
(0.4),
(1),
(0.45);

SELECT SUM(test) FROM `test` WHERE 1

The SQL above gives 6.1000000000000005 on MySQL 5.5, which is due to their change to dtoa for floating point numbers, which is expected.

In other words: DOUBLE and FLOAT are approximate value data types; you shouldn't be using them at all, not for storing monetary values and even less for quantities.

Further Reading: http://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

Linas Kukulskis

2013-07-16 11:40

reporter   ~0008915

all calculations are done in PHP side. for that use oxprice, oxpriceList etc class.
double filed is good , because you can enter and store article price with precision that customer need 2, 3 or etc number after comma

vschmi

2013-07-17 10:03

reporter   ~0008920

Last edited: 2013-07-17 11:08

View 2 revisions

You aren't correct in either of your both statements.

You're using SUM() of the database at least in the order summary view (list_order.php). A quick seach over the OXID code reveals that you're using a database SUM() in various files.

The precision should be controlled by the presentation layer, not your data type. You will never want to store monetary values in FLOATING POINT. Please read http://stackoverflow.com/questions/4834390/how-to-use-mysql-decimal

Also, please read the big fat warning box on http://php.net/manual/en/language.types.float.php

To make it clear: FLOATING POINT NUMBERS ARE NOT EXACT. YOU AND YOUR CUSTOMERS WILL RUN INTO TROUBLE USING THEM SOONER OR LATER!

This is why Doctrine maps a database DECIMAL to a PHP string and not floating point, because it would be wrong (refer to http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#doctrine-mapping-types ).

I didn't open the bug because I noticed: "Oh, they're using double all over the place". I opened the bug because we had an actual issue with it.

Linas Kukulskis

2013-07-17 12:03

reporter   ~0008921

Yes, you are write, i look over the code and found couple of places related with order where used SUM in DB layer, it should be replaced. Sorry, mist this stuff from the first look.
If the question is what to use decimal or double? of course there are advantages of using decimal. but for now we need double (reason is in first my comment). in future it will be improved with other db performance improvements.

vschmi

2013-07-17 14:10

reporter   ~0008923

You could use DECIMAL(20,10), which would represent values from -9999999999.9999999999 up to 9999999999.9999999999.

It is perfectly okay to use SUM() on the database layer - that's what the database is there for!

If your concern is that a value of 1234 would be returned as 1234.0000000000: Let the user configure the precision (default to 2 decimal places) and use number_format with the configured precision to output numbers.

I am aware of that this isn't an easy task, because this essentially goes through the whole system.

If you used twig instead of smarty, you could configure defaults:

http://twig.sensiolabs.org/doc/filters/number_format.html

Not sure if smarty allows for such defaults.