0005291: Order Overview shows too many decimal fractions for order amounts - OXID eShop bugtrack

ID	Project	Category	View Status	Date Submitted	Last Update

0005291	OXID eShop (all versions)	2.6. Administer orders	public	2013-07-12 19:27	2023-11-20 14:59

Reporter	vschmi	Assigned To
Priority	normal	Severity	minor	Reproducibility	sometimes
Status	confirmed	Resolution	reopened
Product Version	4.7.6 / 5.0.6

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.

Theme	Both
Browser	All
PHP Version	any
Database Version	any

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	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.