• (cs) in reply to 08

    Anonymous:
    Maurits:
    Otto:
    "(1 + CAST(15 AS FLOAT)/100)"
    Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?


    It's not as bad as it looks.  The execution plan will very likely compile that to a constant anyway.

    There is a slim justification in the expanded version because you can easily do a search-and-replace for "15" to "8" and the expanded version will still be correct.  1.15 on the other hand would become 1.8, which is a hike of 80% instead of 8%.


    That's just plain Stupid

    I gotta figure 64 bit architectures with m atching date libraries will be ubiquitous by then. Then again, the Cobol stuff written in 1960 will probably still be running, so who knows ;)

     

  • SQuirreL (unregistered)

    sh*t, just use the 'money' or 'decimal' type for the data. Or whatever your database provides to get round the FP/decimal problem. If your database only provides floats - get a proper one!

  • (cs)

    In a previous job we stored all money in thousandths of a penny (0.001 cents) when dealing code-side (Java BigDecimal and BigInteger). In the database it was stored using the MS money type (4 decimals). The industry was gambling so you can be assured that if money that disappears end up with people in worse places than the US ClubFed (considering we sold to Latin America and Eastern Bloc countries).

    The reason was is that our cash register/terminal had to take in money from several different currencies (Bolivars, US Dollars, Euros, Rubles, Mexican Pesos, Yen, etc.) convert that to USD to purchase against a US Company - often within the same purchaing transaction (say 10.50 USD + 350 PEN). All winnings were in USD and payouts would be in USD or EUR.

    At the end of the day we had to balance our drawer. The final amount was based upon what we owned the US Company (only available in US Pennies), plus any extras tacked on by the company taking the money (held in our database), plus a small fractional file that kept millionths of pennies lost or gained during each transaction so that the drawer would balance out.

    I'm the decider

  • (cs) in reply to 08
    Anonymous:
    Gene Wirchenko:
    Anonymous:
    Maurits:
    Otto:
    "(1 + CAST(15 AS FLOAT)/100)"
    Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?


    It's not as bad as it looks.  The execution plan will very likely compile that to a constant anyway.

    There is a slim justification in the expanded version because you can easily do a search-and-replace for "15" to "8" and the expanded version will still be correct.  1.15 on the other hand would become 1.8, which is a hike of 80% instead of 8%.
     
    you have two options for searhc and replace
    1) replace 1.15 with 1.08 or replace 15 with '08' eitherway there is no problem keep in mind
    search and replace works on stings it dosn't care that the string is a number and will not trim leading zeros.


    It is still possible to get in trouble.  In C, "08" is an illegal octal constant.


    Actually, "08" is a string.

    Not in the language that I posted in (English).  '"08"' is a respresentation of a C string constant.  "08" is the quoting of two characters.

    Sincerely,

    Gene Wirchenko

  • (cs) in reply to Otto

    Otto:
    Just to back up some stuff here, I've worked at more than one major retailer, and the backend prices are *always* stored as integer values. It's actually required in most of the requirements documents that deal with it. Prices as floats is bad, bad, bad.

    Still, the code shouldn't be complex to deal with it. This code is just needlessly overcomplicated because it was written by monkeys, not because of the price being an int.

    Let's hear it for languages that support a BCD datatype.  It's perfect for dollar amounts.   

  • Gabriel (unregistered) in reply to kipthegreat

    kipthegreat: Except for being named "Dollars", isn't this how you're supposed to do this?

    Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.

  • John Hensley (unregistered) in reply to Gabriel
    Anonymous:
    > kipthegreat: > Except for being named "Dollars", isn't this how > you're supposed to do this? Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.

    Patterns of Enterprise Application Acrhitecture is talking out of its ass.

  • (cs) in reply to JCmay
    Anonymous:
    Would not "0.0115 AS INT" equate to zero?


    No, CAST(expression AS type) doesn't bind that way.

    CAST(x * 0.0115 AS INT) is interpreted as
    CAST( (x * 0.0115) AS INT)
    not CAST(x * (0.0115 AS INT) ), which would be a syntax error.
  • (cs)

    Task:  add a 15-percent fee.

    Required: to know the precision of the inputs, the precision of the datatypes, the idosyncracies of the programming language (and the compiler!), etc.  For example, does 1 + .15 equal .15 + 1 ?  (Some languages yes, some no.)  When does the compiler perform implied datatype conversions for me, and what does it do about precision?  (e.g. trying to compare a float and an int)  If I perform the identical calculation multiple times in the code, will the compiler re-use the previous result, or will it re-do the calculation?  Does the treatment of negative numbers match my requirements?

    For today's WTF, we also need to understand the storage space implications: if a datatype were twice as big, it'll take up twice the space, take twice as long to back up, etc.  Sometimes, that's a problem.

  • Loren Pechtel (unregistered)

    After some battles with accounting types I've become a firm believer in storing money as pennies.  Note that the 4-fixed-place currency types are NOT suitable if you plan to do any multiplicaiton (say, percents) with them.  The problem with them is that you can get fractional pennies that end up adding up to more than a penny.  So long as there is only one way to calculate something this doesn't cause a problem so long as you apply the percents as late as possible--the guy checking the math with a calculator will do the same thing.  However, what if there is a detail mode where the percentages must be applied line by line?

  • Anonymous (unregistered) in reply to doh!
    Anonymous:

    And adding 15% ?

    pennies = (pennies*115)/100

    Don't make me puke!


    pennies += pennies*3/20;
  • (cs) in reply to John Hensley
    Anonymous:
    Anonymous:
    > kipthegreat: > Except for being named "Dollars", isn't this how > you're supposed to do this? Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.

    Patterns of Enterprise Application Acrhitecture is talking out of its ass.

    And you are... ?
  • (cs) in reply to Gabriel
    Anonymous:
    > kipthegreat: > Except for being named "Dollars", isn't this how > you're supposed to do this?

    Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.


    Yeah, but in Analysis Patterns (http://martinfowler.com/ap2/quantity.html), he uses BigInteger and says:
    MartinFowler:
    Notice that I use a BigInteger. In Java I could equally well use a BigDecimal, but in many languages an integer is the only decent option, so using an integer seems best for the explanation.

  • BtM (unregistered) in reply to John Hensley

    Anonymous:
    Anonymous:
    > kipthegreat: > Except for being named "Dollars", isn't this how > you're supposed to do this? Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.

    Patterns of Enterprise Application Acrhitecture is talking out of its ass.

    Why?  Integer arithmetic is exact; floating-point arithmetic is not.  Given that rather innocuous and common decimal values such as 0.1 and 0.7 cannot be represented exactly as binary fractions, you're going to run into rounding errors pretty quickly, and the more operations you perform, the more the error propagates into the higher-order bits.  Losing or gaining a cent here and there may not sound like a big deal at first, but when you're talking about hundreds of thousands to millions of transactions a day, it winds up being enough money to worry about. 

     

  • John Hensley (unregistered) in reply to dserodio
    dserodio:
    Anonymous:
    Anonymous:
    > kipthegreat: > Except for being named "Dollars", isn't this how > you're supposed to do this? Patterns of Enterprise Application Acrhitecture says that you should indeed treat money as integral numbers of the smallest unit (pennies). This is to ensure that you don't lose pennies to rounding errors, etc.

    Patterns of Enterprise Application Acrhitecture is talking out of its ass.

    And you are... ?

    1000 gallons of gas sold at $3.199. What's the total revenue?

    Two $1.00 sales taxed at 8.5%. How much tax do you owe?

    Sure, you can say "Add up everything before multiplying." But then each application has to be specially written so that it does operations in the right order, and the accountants won't have the numbers that they want. Decimal types really are the Right Thing.

  • Oliver Townshend (unregistered) in reply to JAlexoid

    Well in Australia we'd calculate the GST on the sum of the taxable transactions, not the sum of the tax on the individual transactions.  Which makes the calculation pretty easy.

  • any (unregistered) in reply to Jalf


    The precission that a float can give (remember, no doubles in 3d engines) is enough for all purposes.


    So it doesn't bother you if your bank is unable to represent the amount $0.1?


    It's doesn't matter as 0.1 can be represented with an error less than 0.000000001, which, to all extent, rounding properly, is enough. Of course you could use double instead of float. Then the value is represented EXACTLY. In fact, every number using only two decimal can be represented EXACTLY. Humm seems to be enough for banking... but that's noto all, is exact until



    On the second, Epsilon value is used not to minimize, but to eliminate completely errors

    Never ever write code fort me. Please!
    You use epsilon to provide a range within which you assume "it's good enough". It might be, or it might not be. You have no guarantee, and moreover, it only helps you with comparisons.


    As you've said, if you give a good precission, let's say till decimal place 10,... get conclusions on your own.



    But for representing money, a simple integer datatype works fine. You only have a set number of decimals, no matter what you do. And the final result is always rounded to a fixed (and small) number of decimals, so it's pretty easy to guarantee precision.

    This thread has scared the life out of me. If the people reading *this* site of all things, can't tell the difference between a float and an int, the world is a scary place.
    The "real" WTF is that so many people here would prefer to use floating point datatypes for money, or that they're arguing that "You can't avoid precision loss", or "It doesn't matter with a few small rounding errors".
    That is just scary. Really really scary. Please tell me if any of you get any contracts in my part of the world. If you do, I'm moving.


    It works, but hey, you are all right, floating point numbers and standard IEEE 754 are only entertainments made by people who had absolutely no idea, and had nothing more interesting to do. Of course, altough you are probably a not very skilled programmer but with a head full of yourself, you are probably right and people using floating point have no idea what they're doing.

    What is really worth to laugh at, is the fact that, altough banking needs to make high precission calculations, decimal places involved are a few, so no problem with proper understanding of what's going on. But instead of reading, it's better falling into the realm of the arcane an say it's preferably to use integer for decimal fixed point calculations. I would understand fixed point if the language provide such, and you had nothing more to do, as Ada does for example, but if you have to implement something else, floating point is not only enough, but very enough. And the frightened it's me, as it is this kind of thinking ("humm i know more than anyone so let's reinvent the wheel in a squared fashion") that leads to misinformation later, and arcane thinking of how are things.

     

  • BtM (unregistered) in reply to any
    Anonymous:


    The precission that a float can give (remember, no doubles in 3d engines) is enough for all purposes.


    So it doesn't bother you if your bank is unable to represent the amount $0.1?


    It's doesn't matter as 0.1 can be represented with an error less than 0.000000001, which, to all extent, rounding properly, is enough. Of course you could use double instead of float. Then the value is represented EXACTLY.


    No, it isn't. Values like 0.1 cannot be represented exactly as a binary fraction, period. You get a helluva lot closer with a double as opposed to a float, but it's still not exact.
    The problem is not whether you can represent a single number to within a given precision; the problem is that because the values aren't exact, every operation introduces some roundoff error, and those errors accumulate. After doing enough transactions, the error becomes significant.
    For example, take this innocuous little loop:
    float sum=0.0; for (i = 0; i < 10000000; i++) { sum += 0.1; }
    The result should be 1000000.00000, right? After all, 0.1 is accurate enough to within a bunch of decimal places, right? Well, after running this, the result I get is 1087937.0000000. Typing sum as double does help a bit: then I get 999999.9998390.
    IEEE 754 is good for what it does, but when you're processing hundreds of thousands to millions of transactions a day, and your results have to be accurate to the penny (or even fractions of a penny), then "good enough" isn't good enough.
  • BtM (unregistered) in reply to BtM

    God DAMN it, I hate IE-centric sites!!!

    Let's try this again:

    float sum = 0.0; for (i = 0; i < 10000000; i++) { sum += 0.1; }

    Expected result: 1000000.0000000 Actual result: 1087937.0000000

    Typing sum as double: 999999.9998390

    Again, the problem isn't representing individual values; the problem is accumulation of error over a large number of transactions.

  • justin (unregistered) in reply to BtM

    Anonymous:
    God DAMN it, I hate IE-centric sites!!! Let's try this again: float sum = 0.0; for (i = 0; i < 10000000; i++) { sum += 0.1; } Expected result: 1000000.0000000 Actual result: 1087937.0000000 Typing sum as double: 999999.9998390 Again, the problem isn't representing individual values; the problem is accumulation of error over a large number of transactions.

    Seems you are using VC++ compiling for Debug... use Release...

    Don't think there will be ten million pennies getting added anywhere... but you never know [:D]

  • BtM (unregistered) in reply to justin

    gcc, not VC++, and that was a release target (not that it would make a difference).

    Again, the point is inexact numbers + large number of transactions = significant error in results.

    And do you think we could have a slightly longer timeout for captcha validation?

  • justin (unregistered) in reply to BtM

    Anonymous:
    gcc, not VC++, and that was a release target (not that it would make a difference).

    In fact it does...because of code generated, but dependes entirely on your compiler...

  • (cs) in reply to Anonymous Cow
    Anonymous:
    Hmm. I read how they did the date (leads to the number 20060426) and thought, "Hey! Cool idea!" ... Is that a bad sign? Really, is that so bad, for storing an 8 digits of a date value?

    Also if you need to query by date range and you code something like

    WHERE somecol BETWEEN 20051201 AND 20060101

    the optimizer will think there are 8,900 days in the range. That kind of thing can really mess with your execution plans.
  • BtM (unregistered) in reply to justin

    Which just argues even further against using floating-point types for financial calculations; if the result you get depends on the build environment, then you have real problems.

    But, okay, I built it under VC++, in release mode, and I got the result 999999.999839.  Changing the constant to 0.1f gives the result 1000000.014901. 

    Good enough for government work?  Not quite. 

  • (cs) in reply to doh!

    So, under the system you propose, 100 washers costing $2.49 yields pennies = 249, right? So what's the cost of a washer? Of course you ignored that part of the poster's reply. Here's a hint 249/100 w/ int division is 2. But each washer doesn't really cost 2 pennies, does it? It costs about two and a half pennies.

  • Nameless but brave (unregistered) in reply to Xargon
    Xargon:
    kipthegreat:
    Alex Papadimoulis:

    The "Dollars" column, despite it's name, really stored pennies.



    Except for being name "Dollars", isn't this how you're supposed to do this?  That's what I was taught in like CompSci 101 anyway.  It's never been an issue for me, and avoids questions like "Why is the sum of seven $0.01 transactions less than $0.07" (or something like that which might be seen in the real world).

    The problem is that most fractions cannot be exactly represented in binary.  For example, to be precise in your example, we have:

    >>> 0.01*7
    0.070000000000000007

    Most of the time, an intelligent application of rounding is good enough.  But yes, you could use integers to avoid this.


     Of course fractions (of limited length) can be exactly represented in binary. 0.07 (dec) =  0.0001001(bin) 8-|

    Or what did you mean ?
  • (cs) in reply to Nameless but brave
    Anonymous:
    Of course fractions (of limited length) can be exactly represented in binary. 0.07 (dec) =  0.0001001(bin) 8-|

    Or what did you mean ?


    Some people do not appear to know what "exactly" means.  Valuable Clue: It does not mean "approximately".  0.0001001 base 2 = 9/128 = 0.0703125.  This is not exactly 0.07, is it?

    Sincerely,

    Gene Wirchenko

  • BtM (unregistered) in reply to prehnRA

    prehnRA:
    So, under the system you propose, 100 washers costing $2.49 yields pennies = 249, right? So what's the cost of a washer? Of course you ignored that part of the poster's reply. Here's a hint 249/100 w/ int division is 2. But each washer doesn't really cost 2 pennies, does it? It costs about two and a half pennies.

    Which is why you pick a scale appropriate to the problem.  In this case, you're dealing with hundredths of pennies.  $2.49 == 24900 hundredths of pennies.  So 1 washer costs 249 hundredths of cents.

    The drawback to this is that you can't just naively use a native int or long datatype; at that fine a scale, the largest dollar amount you can represent in a 16-bit int is $6.53 + 36/100. 

  • NoClue (unregistered) in reply to BtM
    //Some crap cooked up in C# Express and release build:
    //(execution time ~2 seconds)
     
     
     
    string init = "0,000000000000000000001";
    ulong iter = 10000000;
    Console.WriteLine(iter + " additions");
    Console.WriteLine(ulong.MaxValue + " ulong max");
    float fsum = float.Parse(init);
    double dsum = double.Parse(init);
    decimal msum = decimal.Parse(init);
     
    for (ulong i = 0; i < iter; i++)
    {
    fsum += 0.1F;
    dsum += 0.1D;
    }
    Console.WriteLine(fsum / iter);
    Console.WriteLine(dsum / iter);
     
    for (ulong i = 0; i < iter; i++) {
    msum += 0.1M;
    }
    Console.WriteLine(msum / iter);
     
     
    <FONT color=#000000>Output:</FONT>
    <FONT color=#000000></FONT> 
    <FONT color=#000000>10000000 additions
    18446744073709551615 ulong max
    0,1087937
    0,0999999999838975
    0,1000000000000000000000000001    </FONT>
    <!-- END syntax hilighted text -->
  • Gerard Byrne (unregistered) in reply to JAlexoid

    There should be no taxes (whatsoever) on 1c individual charges. Taxes are computed as the applicable percentage against the aggregate of items at the given tax rate/code. This is why I shudder when I see tax fields in line details (e.g. items in invoices) rather than in the header (or a separate tax summary).

    Integers are better than unconstrained real/floats. But is there a presupposed 2 decimals - lots of telco land transactions are required to 5 decimals with rounding to cent values at appropriate aggregation points. E.g. transactions for interconnect/roaming charges denominated in XDR.

    And if you use integers what do you do with non-decimalised currencies such as HUF? Do these get multiplied by 100?

    Life ain't simple. I would fire the programmer who wrote the original code on the spot. He/she is destined to a special career in comedy.

  • Teemu Teekkari (unregistered)

    <FONT face=Arial>I wish this classic paper was made mandatory reading for every computer science student:</FONT>

    <FONT color=#003366><FONT face=Arial></FONT><FONT face=Arial>What Every Computer Scientist Should Know About Floating-Point Arithmetic</FONT></FONT>

    <FONT face=Arial>I myself would have saved a couple of months extra work converting doubles to longs in my first 'real job'. (Nothing to do with money, though. It was a desktop-publishing sort of application, computing areas and such.)</FONT>

  • (cs)

    Don't see the mind set?  Have you ever worked with BASIC in the 1960's?  What  were the data types like in some of the "Open Source" mini-BASICs for the 8080, 8008 and Z-80?  Integers and strings!

    I am also trying to figure out why they used FLOAT for money calculations instead of following EU and GAAP rules. 

    Or how you write a constraint for the dates to keep them valid.  Oh yeah!

  • (cs) in reply to Gerard Byrne
    Anonymous:
    There should be no taxes (whatsoever) on 1c individual charges. Taxes are computed as the applicable percentage against the aggregate of items at the given tax rate/code. This is why I shudder when I see tax fields in line details (e.g. items in invoices) rather than in the header (or a separate tax summary).

    Most retailers handle this sort of thing by having a separate entry on each line item that contains the taxable amount of the line item. The reason for this is that there's plenty of customers that can buy certain items tax-free, depending on local laws and such. And this can vary customer to customer. So you keep the taxable amount of each line item (which you usually look up in some form of table), then charge taxes on that total instead of the actual total. This makes things work.

Leave a comment on “Functional Multiplication”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article