View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0005291||OXID eShop (all versions)||2.6. Administer orders||public||2013-07-12 19:27||2015-12-01 12:33|
|Product Version||4.7.6 / 5.0.6|
|Target Version||Fixed in Version|
|Summary||0005291: Order Overview shows too many decimal fractions for order amounts|
|Description||We'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.
|Tags||No tags attached.|
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/
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
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
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
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.
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.
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:
Not sure if smarty allows for such defaults.