Replace decimal(12,4) with decimal(9,3) and decimal(13,3)

[colinmollenhour]

MySQL decimal storage uses a minimum of 4 byes and can store 9 digits in these 4 bytes. Going up to 6 bytes you can store 13 digits.

In most cases decimal(9,3) is sufficient and it would save 2 bytes per row/column. The maximum value would be 999,999.999 (1 million) instead of 99,999,999.9999 (100 million). Unless you are selling original artwork in Iranian Rial this should be sufficient. Also according to the ISO list of currencies there are no currencies which use 4 decimal places (but plenty that use 3, so 2 is not acceptable).

For aggregation tables the better storage type would be decimal (13,3) since still 4 decimals are not needed and 13 takes the same storage space as 12. So for the same storage you can store numbers 100 times larger.

It may seem like a minor optimization but in Magento 1 my sales_* tables currently have over 500 decimal(12,4) columns (some are third-party). 2 bytes * 500 columns * 1 million rows is almost 1GB of storage, not to mention the indexed columns. Not huge, but quite significant.

While you're at it, the report_event_types.event_type_id and report_event.event_type_id and report_event.subtype should be tinyint instead of smallint. Maybe these were removed or already changed in M2.. There are probably other columns that could be optimized as well. I think there is even a strong case to be made for changing store_id to "tinyint unsigned" throughout since operating over 256 stores is an edge case.

I realize the DBMS abstraction may be an impediment to this change but considering only MySQL is actually supported I think that is far less important than a database that isn't wasteful of resources.

Lastly, for anyone who is an edge case and decimal(9,3) is not sufficient it should be quite easy to install Magento, dump the database, use sed or a text editor to do a simple replacement and then re-import.

[ilol]

@colinmollenhour thanks. I put it into our development queue

[tzyganu]

@colinmollenhour @ilol
I'm butting in with something.
I cannot challenge the statement "according to the ISO list of currencies there are no currencies which use 4 decimal places" but I can tell you that at least for RON (Romanian currency) for the exchange rates to EUR, USD, GBP (and maybe others) 4 decimals are taken into consideration.
For example, at the time I'm writing this, the official exchange rate is 1 EUR = 4.4244 RON

[ilol]

So... (13,3) or (12,4)? IRR exchange rate 1 IRR = 0,0000283 EUR

[colinmollenhour]

So it looks like we have uncovered a separate issue.. Currently the *_to_*_rate columns are 12,4, but it sounds like they should instead be 13,7 to accommodate a wider range of exchange rates. If this is just now coming up then obviously this is an edge case, but maximizing the support for whack exchange rates with the same number of bytes sounds like a win-win to me. 👍

I am not experienced with multi-currency so perhaps the exchange rate being >3 decimals is a valid reason for using >3 throughout, but are not the final amounts rounded anyway? E.g. if my total is 1 EUR and I purchase in RON, will I actually be invoiced for 4.4244 RON or 4.424 RON? I am questioning if all of the totals really need >3. AFAIK my credit card company has never billed me for a fraction of a cent before, but maybe that differs in other countries?

Regardless, if a case can be made for 4 decimals then 13,4 would make the most sense (13 consumes the same storage as 12). 13,4 means 13 total digits, 4 of which are decimal places.

EDIT: I understand rounding issues during calculations and I am not talking about rounding to 3 decimal places for mathematical operations, just for storage.

[tzyganu]

@colinmollenhour I feel that my explanation was not complete. So here are the details.
I will use the same example for RON.
Even if the exchange rates use 4 decimal places, the prices always have 2 decimals.
So unless you sell EUROs or USDs on a website, 4 decimal places will not be needed for storing prices.
But I checked around and I found one currency that use 4 decimal places. COU (or UVR) used in Columbia: http://en.wikipedia.org/wiki/ISO_4217
The same page says that there are 7 currencies that use 3 decimal places.
So I guess the 4 decimals should stay in order to support every currency.

And here is an idea to avoid rounding issues.
Usually the shop owner knows what currencies he will be using. How about asking if they are going to use 3 or 4 decimal currencies when installing Magento? And then construct the decimal columns based on that selection?
I'm writing this very early in the morning and I admit I didn't think it through. But maybe someone can take this idea and turn it into something useful, or destroy it completely (I won't mind).

[ilol]

@tzyganu, intelligible explanation. It make sense.

[colinmollenhour]

Bah, I missed the UVR currency..

I do think that a dynamic solution would be nice. E.g. allow the user to select for the following:

• Amounts
• Exchange Rates
• Aggregation

I personally would then choose 9,2 - 9,5 and 13,2 but someone else may choose 13,4 - 13,7 and 16,4 for example.

[tzyganu]

I just found a flaw in my "unfinished idea".
If the user selects to have the decimal values stored as 9,2 because that's all he needs for prices, then all decimal attribute values are going to be stored as 9,2 because Magento stores for products all the decimal values in the same table. And the user may need 4 decimals for other attribute values. I guess if this approach is taken into consideration, there is a need for a catalog_product_entity_price table that would store all the attribute values for price type attributes.

Even if the dynamic solution for decimal columns is not considered, I still think that a separate table for price attribute values only is needed. Could make the indexing a bit faster. Should I open an other issue for this? or is it OK if I leave it here?

[FiveDigital]

When the decimals are changed from 4 to 3 oder 2 digits I think the rounding will be affected, too. Magento had decent rounding problems in the past and right now it looks like they're solved. Changing the decimal storage might bring back all the pain we had in the last years with PayPal, taxes, the missing cent and order export to backend systems.

[colinmollenhour]

@tzyganu That is a good point. However, in real world I don't think there is much use for decimal types other than monetary amounts. Text fields can store as many decimals as one needs. In one store I manage with 140 custom attributes, none of them are decimal type.

@FiveDigital So don't improve it because there might be regressions? Isn't that what the exhaustive unit tests are for?

[FiveDigital]

I just wanted to point this topic out! We had so much pain with this issue in the time between 1.4 and 1.8 and I'm sure we weren't the only ones. So nobody wants this problems again. I'm of course not against the change and covering the rounding issues with unit tests is a must have even if the decimal format doesn't change 👍

[piotrekkaminski]

@FiveDigital agreed. It is also really hard to cover all possible cases with tests (quite a lot of possibilities incl. price rules, shipping, coupons, promos, gift cards etc) so a change in this area can introduce undetected errors.

[barbazul]

Rounding errors are far from solved. The minute you set up taxes, a
notification shows up letting you know of that.

Decimal precision has already been discussed on a different thread. My
thoughts on the matter are: storage space is cheap, development hours are
not.

So use as many decimal places as possible to store prices and remove the
multiple hard-coded rounding calculations. Precission should be easily
changed for devs or maybe even for admins.

Use cases are plenty:

• currency conversion
• discount rules
• financing interests
• decimal qty items
• very small products meant to be sold in large quantities (like nails,
  balloons, etc)

All of the above are present in most B2B stores I have dealt with.
El 22/08/2014 12:52, "piotrekkaminski" [email protected] escribió:

@FiveDigital https://github.com/FiveDigital agreed. It is also really
hard to cover all possible cases with tests (quite a lot of possibilities
incl. price rules, shipping, coupons, promos, gift cards etc) so a change
in this area can introduce undetected errors.

—
Reply to this email directly or view it on GitHub
#636 (comment).

[DavidBruchmann]

Invoices from telecommunication providers often show 4 or six digits
after the point to show the price for single calls / sms. Even the
final value is rounded related to the currency's smallest unit you get
wrong sums if only calculation with 2 decimal precision.
So the assumption that digits provided by the currency are enough is
just wrong and depend on individual needs.

On 8/23/14, Barbazul [email protected] wrote:

Rounding errors are far from solved. The minute you set up taxes, a
notification shows up letting you know of that.

Decimal precision has already been discussed on a different thread. My
thoughts on the matter are: storage space is cheap, development hours are
not.

So use as many decimal places as possible to store prices and remove the
multiple hard-coded rounding calculations. Precission should be easily
changed for devs or maybe even for admins.

Use cases are plenty:

• currency conversion
• discount rules
• financing interests
• decimal qty items
• very small products meant to be sold in large quantities (like nails,
  balloons, etc)

All of the above are present in most B2B stores I have dealt with.
El 22/08/2014 12:52, "piotrekkaminski" [email protected] escribió:

@FiveDigital https://github.com/FiveDigital agreed. It is also really
hard to cover all possible cases with tests (quite a lot of possibilities
incl. price rules, shipping, coupons, promos, gift cards etc) so a change
in this area can introduce undetected errors.

—
Reply to this email directly or view it on GitHub
#636 (comment).

---

Reply to this email directly or view it on GitHub:
#636 (comment)

[sIiiS]

As @ilol said, the IRR exchange rate in Iran for EURO is "1 IRR = 0.0000283999 EUR" and for USD is "1 IRR = 0.0000376100 USD" ... So I think even (20,10) will make sense for Iranians

[colinmollenhour]

@barbazul You seem to be equating storage with calculations.. Magento does all calculations in the PHP code which has no bearing whatsoever on storage. Only if Magento was using SQL queries with multiplications and divisions would the number of decimal places need to support more than the final result that is stored.

@DavidBruchmann Magento is not designed for telecoms and has never been and almost certainly will never be used by telecoms for usage billing. I will concede this point if a real, common case is given whereby merchants make sales in fractions smaller than their currency physically allows as per the ISO standards. Supporting cases like this when there is no good reason to at the detriment of all other merchants doesn't make sense to me, especially when the issue can be worked around easily enough by those with special needs (mysqldump | sed | mysql).

[DavidBruchmann]

@colin Mollenhour
Assumed you sell a product with the smallest fraction of a weak currency.
Then you decide to sell it in a country with strong currency too. The
product is there getting more expensive now because exact calculations are
not possible?
The telcom-example is comparable with the before mentioned nail- or
balloon-problems and just showing that smallest fraction of a currency is
not always related to real price-calculations.

[barbazul]

Collin: those are not separate issues. For instance, sales tables store all
totals in decimal 12,4. Those totals are the result of the mentioned
calculations that also happen to be stored in the DB causing all kind of
rounding errors when displaying the orders and also when querying those
tables for reports or when integrating with external services.

As for the requested example, check out www.maxiconsumo.com which is a B2B
that sells to small supermarkets in Argentina. Prices need to use 3 decimal
places though the currency (ARS) usually uses 3.
El 26/08/2014 01:44, "DavidBruchmann" [email protected] escribió:

@colin Mollenhour
Assumed you sell a product with the smallest fraction of a weak currency.
Then you decide to sell it in a country with strong currency too. The
product is there getting more expensive now because exact calculations are
not possible?
The telcom-example is comparable with the before mentioned nail- or
balloon-problems and just showing that smallest fraction of a currency is
not always related to real price-calculations.

—
Reply to this email directly or view it on GitHub
#636 (comment).

[acharrex]

If you configure your products with prices excluding taxes, you need to be able to define them with 4 decimals to get a price including taxes on 2 decimals.

Example:
VAT: 8%
Price excluding taxes: 115.8796
Price including taxes: 125.15

[colinmollenhour]

@acharrex I see your point, perhaps, but your example is flawed because 115.880 * 1.08 still rounds to 125.150. :)

[DavidBruchmann]

@colin, you always find arguments for avoiding changes concerning the
digits and keeping your random opinion. You even excluded already a whole
branch with the words they don't use magento anyway. I think like this you
give a good reason for forking the project or just using something else.

On Tue, Sep 9, 2014 at 9:09 PM, Colin Mollenhour [email protected]
wrote:

@acharrex https://github.com/acharrex I see your point, perhaps, but
your example is flawed because 115.880 * 1.08 still rounds to 125.150. :)

—
Reply to this email directly or view it on GitHub
#636 (comment).

[choukalos]

@DavidBruchmann , @barbazul , @FiveDigital, @tzyganu , @colinmollenhour from my perspective it sounds like there are several inter-related threads:

1. How many digits of precision to store in DB
2. Currency Conversion calculation/precision
3. B2C/B2B/Telecom pricing needs ( i.e. service for x digits of precision * qty and not break rounding )
4. Tax calculation (aka rounding issues)

For item 1 please note we need to store more details for orders to properly calculate sales/shipping tax. That work might impact how many digits we have to store ( issues #444 ). As @ilol stated we'll look into this but it's tied up with the other item's which compound the matter.

For item 2 - currency conversion. Isn't that just saying we should store a # of significant digits for currency conversation ratios (i.e. just 1 table; for the most part)? This seems pretty reasonable to adjust

For item 3 B2C - we really only support 2 digits of precision; so per ISO 4217 we're really not supporting those currencies using 3 or 4 digits of precision (i.e. COU, LYD, XBT [ I thought there was a bitcoin craze going on... heck there's 8 digits there ] ; of which majority of currencies are 2 digits of precision ). To support this we'd need to enable a configuration for merchants to change from say 2 digits to 8 digits (bit coin; 4 if we stick to non-digital currencies) of precision and show that precision on all prices in Magento. This would also impact storage and tax calculations. This seems pretty risk for what is a handful of countries with a small share of the e-commerce market. Which e-commerce sites are located/used in Tunisia, Oman, Libya, Colombia...? If one were to customize this behavior is it do able given the price template work and consolidated tax logic? What else would need to be done to help support community efforts here?

For item 3 B2B support - that tends to look more like digits of precision for a product * some amount/rounded to the currency precision as a line total. This should be easier to customize with the recent price template refactoring and consolidated tax calculation logic work. At present our focus is B2C.

For item 4 - @FiveDigital is totally right; I wouldn't feel comfortable making a change for item 3 until we've done more work around automated testing ( considerably more work ).

[barbazul]

Regarding item 3.

I don't know about many of those countries but I have worked in 2 Magento
websites for Colombia. I am pretty sure I wasn't lucky enough to stumble
upon the only 2 ecommerce sites in that country (I am not colombian and I
have never been to Colombia).

Also, they have an annual ecommerce day conference that.claims to be the
largest in America Latina http://www.ecommerceday.co/2014/

So i'd dare say Colombia has a significant ecommerce activity.

More to the precision matter:
I recently started working in a Magento store for Chile and was surprised
to find out that they have no decimals in their currency. Check out this
website: http://www.falabella.com/falabella-cl

So again, I will have to hack my way around all those hardcoded
number_format(2) that could so easily be configurable...
El 15/09/2014 13:14, "Charles Choukalos" [email protected]
escribió:

@DavidBruchmann https://github.com/DavidBruchmann , @barbazul
https://github.com/barbazul , @FiveDigital
https://github.com/FiveDigital, @tzyganu https://github.com/tzyganu ,
@colinmollenhour https://github.com/colinmollenhour from my perspective
it sounds like there are several inter-related threads:

1. How many digits of precision to store in DB
2. Currency Conversion calculation/precision
3. B2C/B2B/Telecom pricing needs ( i.e. service for x digits of precision

• qty and not break rounding )
• Tax calculation (aka rounding issues)

For item 1 please note we need to store more details for orders to
properly calculate sales/shipping tax. That work might impact how many
digits we have to store ( issues #444
#444 ). As @ilol
https://github.com/ilol stated we'll look into this but it's tied up
with the other item's which compound the matter.

For item 2 - currency conversion. Isn't that just saying we should store a

of significant digits for currency conversation ratios (i.e. just 1

table; for the most part)? This seems pretty reasonable to adjust

For item 3 B2C - we really only support 2 digits of precision; so per ISO
4217 we're really not supporting those currencies using 3 or 4 digits of
precision (i.e. COU, LYD, XBT [ I thought there was a bitcoin craze going
on... heck there's 8 digits there ] ; of which majority of currencies are 2
digits of precision ). To support this we'd need to enable a configuration
for merchants to change from say 2 digits to 8 digits (bit coin; 4 if we
stick to non-digital currencies) of precision and show that precision on
all prices in Magento. This would also impact storage and tax calculations.
This seems pretty risk for what is a handful of countries with a small
share of the e-commerce market. Which e-commerce sites are located/used in
Tunisia, Oman, Libya, Colombia...? If one were to customize this behavior
is it do able given the price template work and consolidated tax logic?
What else would need to be done to help support community efforts here?

For item 3 B2B support - that tends to look more like digits of precision
for a product * some amount/rounded to the currency precision as a line
total. This should be easier to customize with the recent price template
refactoring and consolidated tax calculation logic work. At present our
focus is B2C.

For item 4 - @FiveDigital https://github.com/FiveDigital is totally
right; I wouldn't feel comfortable making a change for item 3 until we've
done more work around automated testing ( considerably more work ).

—
Reply to this email directly or view it on GitHub
#636 (comment).

[pljspahn]

I'm putting in my $0.02000 here because I am currently faced with implementing 5 digit precision in a Magento 1.9 build.

For those who believe there are no good use cases for this, they are ignoring real-world businesses that expect this sort of precision. "Our old system allowed us to do this!"

As previously pointed out, consider the site that sells things in large quantity where each unit has a small price. Nails, screws, etc were mentioned, but also think about pricing per linear inch / square inch / etc.

What I need currently amounts to:

• Simple product with 5 digits of precision on the price (ie. 0.02125)
• In cart, quantity is calculated as a simple W*H formula, units in square inches.
• An item that is 36" x 30" should cost $22.95 ( 36 * 30 * 0.02125 )
• With only three digits of precision the price instead is $22.68, with four it's now $23.04
• Consider the merchant has high volume where this critically impacts their business model
• Also consider that there may be 8-10 (or more!) items in the cart that all use this pricing formula

TL;DR - Merchants need to be able to decide how many digits of precision they will use. In the case I provided, we need 5 (maybe even 6 for some products). I agree that merchants who only require 2 digits shouldn't have to bear the brunt of the extra precision.

Maybe the best solution is to provide an option during installation where pricing precision can be chosen by the admin/developer/merchant.

[colinmollenhour]

@pljspahn Thanks for the input, that is a good scenario to consider. Some thoughts on the matter:

Magento does support decimal quantities, so using larger units and decimal quantities could be a solution that would work even with current limitations. That is if units are currently square inches, store prices in price per square feet or per 100 square inches. This might make sense anyway since as a customer $3.06/sqft is arguably more readable than $0.02125/sqin. Then if they order 36" x 30" that would simply be 3' x 2.5' = 7.5 units so the example problem would be supported just fine by existing limitations. Template updates can always be used to convert to smaller units if necessary. Just a thought.

As I said before, even if the merchant absolutely must store the product price using higher precision, the order totals and similar do not need higher precision since they will be rounded to exchangeable monetary amounts and they account for probably 90% of the decimal columns in the database.

I agree that having the merchant choose precision configuration upon installation would be a good solution. Another possibility that may be more EAV friendly would be to take the middle-ground and support two decimal types: low precision and high precision that are either fixed or configurable. Low precision would be for monetary amounts and high for things like tiny unit prices or currency exchange rates

[DavidBruchmann]

Configuration of high or low precision had to be done on base of the
currency perhaps.
So if several currencies are used, should the option exist for every
currency?
The configuration of precision might still differ from configuration of
display-length. So I could configure precision of 7 digits but display only
5, 3 or even 2 or 0.
In general the question is arising then why I can configure in-precise
calculation but that might be a feature related with rounding.

On Sat, Dec 6, 2014 at 12:26 AM, Colin Mollenhour [email protected]
wrote:

@pljspahn https://github.com/pljspahn Thanks for the input, that is a
good scenario to consider. Some thoughts on the matter:

Magento does support decimal quantities, so using larger units and decimal
quantities could be a solution that would work even with current
limitations. That is if units are currently square inches, store prices in
price per square feet or per 100 square inches. This might make sense
anyway since as a customer $3.06/sqft is arguably more readable than
$0.02125/sqin. Then if they order 36" x 30" that would simply be 3' x 2.5'
= 7.5 units so the example problem would be supported just fine by existing
limitations. Template updates can always be used to convert to smaller
units if necessary. Just a thought.

As I said before, even if the merchant absolutely must store the product
price using higher precision, the order totals and similar do not need
higher precision since they will be rounded to exchangeable monetary
amounts and they account for probably 90% of the decimal columns in the
database.

I agree that having the merchant choose precision configuration upon
installation would be a good solution. Another possibility that may be more
EAV friendly would be to take the middle-ground and support two decimal
types: low precision and high precision that are either fixed or
configurable. Low precision would be for monetary amounts and high for
things like tiny unit prices or currency exchange rates

—
Reply to this email directly or view it on GitHub
#636 (comment).

[choukalos]

@pljspahn @colinmollenhour @DavidBruchmann Currently I'm tracking two feature requests (in our backlog; TBD) - Bulk Pricing, and controlling degrees of pricing precision. For the latter all the permutations concern me - I'd think it'd be safer to just configure pricing/rounding for 0 or 2 or 4 digits (maybe add 3 for the middle east countries depending on testing cost). @barbazul did you create automated tests - could you share those?

[okorshenko]

Internal Ticket ID: MAGETWO-27587

[choukalos]

Update on this issue:

• Currency Conversion rates are stored as decimal (24,12) in the table
• We still have the database using decimal (12,4) in the system ( prices, orders, tax ) but in reality as @barbazul pointed out we're rounding to 2 digits before storing. As I understand from our core dev there are only a few places now in the code that have that hard coded rounding.
• Looks like near term we'll be adding a bulk price feature to Magento ( i.e. as @pljspahn described ) where you can have some qty x amount value expressed in > 2 digits of precision that after those values are entered they'd appear as a 2 digits of precision rounded amount. This is our internal ticket MAGETWO-36192

Asks:

• @pljspahn & others :
  -- what's the commonly accepted way to handle currency conversion for a bulk price feature that you described. Ie I'm buying 4.015 kg of gummy bears at 6.0045 euro's/kg on the store but want to see that in GBP with a current exchange rate of 1.345 Euros to GBP .... Do you round after calculation and rounding the price into store currency (Euros, Current Magento - Friendly to Merchant's accountants) before doing the conversion or do all at once (Friendlier to customer)
  -- How many digits of precision in the above example would be reasonable to use/allow? 4, 8, ????
  ** Note assuming; this should all be easy to customize per points above. Just looking for commonly accepted as our defaults.
• @barbazul if you send in a PR that consolidates our hard coded rounding to 2 digits of precision in a central location so it's easy to customize for you (only accessible by code) I'll make sure it gets in core.

Thanks!

[peterukena]

I know this is maybe the wrong place, but I think I have a solution for the tax/discound rounding issues.
I post here, because this is the thread I found while researching this topic - if you like my idea, please refer me to the correct place for this.

So, in my understanding the problem is the way magento calculates discounts, tax and totals. It calculates them all separately and also rounds them separately which is wrong.

Example:

Item price: 4,30 €
Discount: 5%

Magento does:
Discount amount: 0,2150 €
New item price: 4,085€

Magento displays both sums rounded up, which causes:
Discount: 0,22 €
Item price: 4,09 €
Old item price: 4,30 € - MISSMATCH.

What should have happened is:
Magento calulates the discount amount, rounds it first and THEN substracts that from the item price.
You would then end up with:

Item price: 4,30 €
Discount: 0,2150 ~ 0,22 €
New item price: 4,30 € - 0,22 = 4,08 €.

What I did to "solve" this: I changed the column definitions from the following fields from DECIMAL(12,4) to DECIMAL(12,2)

sales_flat_order_item.discount_amount
sales_flat_order_item.base_discount_amount
sales_flat_order_item.discount_invoiced
sales_flat_order_item.base_discount_invoiced

sales_flat_quote_item.discount_amount
sales_flat_quote_item.base_discount_amount
sales_flat_quote_item.tax_amount
sales_flat_quote_item.base_tax_amount

This forces magento to use an already rounded discount/tax to calculate the final price. I know its not ideal, but for the current circumstances I deem this a way to do it.

This only works, if your tax setting is set to have taxes already included in the product price (which it is in our usecase). Not really sure how much impact that is going to have on the rest of the shop - my initial tests showed good results. More testing will happen tomorrow with different discounts (sales_rules as well) and different tax settings (values and display settings).

I would really like if the calculation for the final discounted and taxed prices would be done properly in magento2 - like I described above. Rounding first on the discounts and then calculating the final price from that instead of the way this is done now.

Cheers!

[choukalos]

@kortwotze Thanks! I'll pass this insight along to our Tax Architect @tang-yu and have him look at it. Likely he won't be able to provide feedback on this for a few weeks. We had a similar issue with discounts that was found earlier in our develop branch for Magento 2 and I believe corrected. Are you still seeing your example on a recent Magento 2 develop branch site?

[peterukena]

To be perfectly honest - as I stated in the beginning: This might be in the wrong place, since I am using Magento 1.9.2.0 for that and my research for that brought me to my conclusion above. I thought, you might be able to use that in Mage2, because in Mage1 its still really messed up in that way. I looked into Mage2 just because I thought I´d find another approach to solve this issue. Then I found this thread and saw, there was still discussion on how to store decimals (which decimals and taxes are) and I found my "solution" beeing close to this topic (though for a different Magento, I agree fully).

So, if this does not belong here, please redirect me to a proper place - otherwise, please feel free to tell me, what you think :)

I did some other tweaks that might contribute to finally fixing rounding issues on taxes and discounts in Mage1 which I will write about soon(tm).

[choukalos]

@kortwotze Cool. We rewrote the tax engine in Magento 2 and have some minor functional differences.

What you're describing in Magento 1 is a recently identified issue ( discount rounding issues when interacting with other rounding points - item tax, shipping, FPT ) and has a slightly different fix in M1 code which mirrors our engine changes in M2 ( we carry both - and + delta-rounds rather than only + ones ). We're making some tweaks to our M2 tax engine to address them and will effectively be porting those fixes to a future M1x release. @piotrekkaminski is aware of this work and tracking in our M1.x backlog.

[tang-yu]

@kortwotze , I agree with you that most of the rounding issue was caused by rounding prices separately and then adding prices together. We changed the calculation logic so that we round tax or discount first and subtract tax to get price excluding tax. We did this in the code, e.g., https://github.com/magento/magento2/blob/develop/app/code/Magento/Tax/Model/Calculation/UnitBaseCalculator.php#L56 and https://github.com/magento/magento2/blob/develop/app/code/Magento/Tax/Model/Calculation/UnitBaseCalculator.php#L81.

[peterukena]

@tang-yu @choukalos Thank you very much for this input. I am very glad to see the way, Mage2 is handling this (I did not look into this until now, because we are not bothering with Mage2 for now). The main question right now is: When will this backport fix be released for Magento 1.9.x.x? I dont really want to go live with those database hacks for our new project, but we have a deadline and I will use my approach to secure that deadline. :sadface:

Anyway, thanks a lot for this input :) I was very scared of rewriting Mage_Tax and Mage_Sales for that, because it is sooo much. :D

[piotrekkaminski]

@kortwotze we have plans to improve this in 1.x but it will take time - the next bugfix release is expected next year.

[peterukena]

Thanks for the reply. Though its sad, that the fix is expected next year, it is good, that the fix eventually will be released. I really appreciate it :)

[barbazul]

After a complete year away from the M2 project I am finally forced to get back to it due to its recent release and @benmarks fundamentals course that I am currently taking.

I'll play around with M2 a little bit longer, getting the hang of it and then I'll send my PR (if this ihas not been done already by someone else)

[piotrekkaminski]

Thank you for your submission.

We recently made some changes to the way we process GitHub submissions to more quickly identify and respond to core code issues.

Feature Requests and Improvements should now be submitted to the new Magento 2 Feature Requests and Improvements forum (see details here).

We are closing this GitHub ticket and have moved your request to the new forum.

[ctadlock]

@magento-engcom-team Is this scheduled to be in in 2.3?

[Nix-id]

@ctadlock @magento-engcom-team
quote and sales_order tables still (12,4) in M2.3.0

[DanielRuf]

Imo we should keep 4 as many shops need this precision. Don't change this.

[Nix-id]

this is not usable if you want add BTC as Currency to shop.
i found some different solutions how add BTC as currency - but shop have many problems with work with BTC because tables fields have decimal(12,4)

also hardocded precision = 2
for example

./vendor/magento/module-directory/Model/PriceCurrency.php
/**
     * Round price
     *
     * @param float $price
     * @return float
     */
    public function round($price)
    {
        return round($price, 2);
    }

[DanielRuf]

You can apply patches if you need a different logic.