- Feature Articles
-
CodeSOD
- Most Recent Articles
- What a More And
- Hall of Mirrors
- Magical Bytes
- Contact Us
- Plugin Acrobatics
- Recursive Search
- Objectified
- Secondary Waits
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Actually, there is nothing wrong with counting whole pennies only. Is kinda nice when you want to ensure that you don't lose any. The problem is that somebody decided to use integers, without knowing how to use them. I mean (amount115)/100 + ( (amount115) %100 > 49 : 1 ? 0) is not the prettiest, but the behavior is very clear.
Admin
For the record, SQL Server definitely has a fixed-precision type that works for this:
set @price = 10
select @price
set @price = @price * 1.0001
select @price
set @price = @price + 0.001
select @price
yields
10.00
10.00
How much precision is up for debate :-)
Admin
WHAT?! My calculations might be off by 0.0000000001% if I use floating point numbers?! That's a horrible, horrible tragedy! I will use integers for everything I ever do in the future. Thanks for setting me on the enlightened path.
Admin
The degree of WTF depends on tool support and context. It's quite possible that the data representation is not a WTF.
In particular, on the DATE side (since it's been getting the least critical attention):
The YYYYMMDD format is actually the internal representation of a date in Teradata. (Well, almost; there'a actually a 1900-year offset in there somewhere...) Of course, this is in theory "behind the scenes", with the DBMS standing between you and the "internal" representation so that it can enforce type checks and date-sanity checks; but then again TD will let you operate directly on the date as an integer.
The WTF is the unnecessarily-complex routine for performing a simple operation, which is not caused by the integer representation.
Admin
Having a multiply function is perfectly acceptable...
if you are writing assembly...
on an architecture which doesn't have a multiplication operation.
Admin
I think that was sarcasm.[|-)]
Admin
Within a function, sure; but, the result of this calculation is only accurate to two decimal places (the precision of the least precise number). There's an exception when both numbers are known to be exact; but, returning 4 decimal digits would be misleading in most applications, although spreadsheets do it every day.
For anyone who needs a demonstration of how painful floating point numbers can be, type this into IE's address bar, and hit Enter:
javascript: alert(0.1 * 0.1)
In the end, I think there's a WTF in the implementation; but, the idea itself is solid.
Admin
That would be "if you ARE compelled" [:)]
Admin
This looks to me like sarcasm. If so, in the imortal words of Napoleon Dynamite... "IDIOT!"
In any system that tracks real money you never store that money in a floating point variable. Never, never, never! It would be a WTF well beyond the stupid garbage that is the topic of this today's message. Values of $.01 (very common in most financial apps) will usually cause problems, and if you process any large transactions things can get really screwy.
Money needs to be stored in a form that is precise. Integers work (although I'd go with longs over integers), for the most part, foreign currency can make things interesting. Many programming languages have a Decimal type to deal with this for you. Most DB's have a Decimal type in which the precision can be set. Worst case you can store the values as char arrays or strings.
That being said, (unless I'm missing something about the FLOAT cast), the code in question destroys the precision we assume they were aiming for with the FLOAT cast.
The Date Integer is a lesser WTF, but most of the problems with that have been mentioned already.
Admin
What about the case where you need 7 $0.005 cent transactions?
Admin
He said that IE did this too ---- "And whoever did it in IE too"
Admin
No, sorry, "it" is a pronoun.
Admin
In accounting that is a serious problem because debit and credit do not match and you cannot close your books. Doing your math is not enough, you also must look carefully at your rounding to avoid that negative numbers round the same way as positive numbers.
These difference no matter how small are a serious problem in accounting systems.
Admin
No, not 0.0000000001%. After rounding your calculation is off by one cent. Businesses that record millions of financial calculations per day care about that. Seriously.
Admin
Show me a case where rounding would cause an error of one cent -- without having very large numbers which push the limits of float/double value's precision.
Admin
An error of one cent may be difficult to reproduce, but one that's not is checking balances. If you subtract one float from another, but one of the floats is a result of some calculations that make it's value slightly different, and then you compare the subtraction result to 0.0, you're going to have a problem.
E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00
Admin
And tack a late fee onto that bill, and you'll really have a problem.
Admin
Didn't that happen in Superman III?
:)
Admin
A number of DBMSs have a DECIMAL datatype, which is FIXED POINT and thus does not have the problems related to floating-point math and money.
Likewise, quite a few DBMSs have date, time, and combined date and time stamps for datatypes. I can't imagine why you'd want to avoid them except in some weird case where you can't figure out the timezone issues.
Admin
If you cap off using the money field and get a precision of 4 decimal places versus converting everything to longs as someone else suggested wouldn't that really only make a difference in very large calculations or percentage/tax calculations that exceed the standard length of most tax percentages?
I would think this would be entirely situational. If you have tax rates that exceed 4 decimal places (6.753938%) then you would want to match the precision of the most precise calc.
In reality, however, tax rates etc do not exceed 4 precision points, so I would say that the money field would work just fine. The chance of you being off by a penny doesn't seem all that likely considering the previous sql example posted rounded correctly which means it didn't just "chop off" the decimals in the back end, it likely took it out to a relevant precision and then rounded to convert to money.
Admin
Not really, time_t is a signed integer (not in the standard, though). Getting too far away from the epoch is a problem.
Admin
There apparently have been cases of people being dunned for $0.00 with a threat to send the amount to collection if it is not paid.
We can laugh about it, but about the RW impact? Customers can be lost over such stupid mistakes. If I got such a bill, I would likely switch to a more clueful company.
Sincerely,
Gene Wirchenko
Admin
When I first saw this, my first thought was that it was done by programmers who were used to representing dates like this:
10 STRING[8] DATE
15 STRING[4] YEAR
15 STRING[2] MONTH
15 STRING[2] DAY
(Note: My syntax is probably off, it's been about 10 years since I used this. The point is being able to directly access the component parts of the date - i.e DATE="20060426";DAY=TOSTRING(TOINT(DAY)+1) results in DATE equal to "20060427").
Admin
From other newsgroups, I understand that in Europe, the requirement by law in currency conversion is six decimal places.
Sincerely,
Gene Wirchenko
Admin
There are also currencies that have no decimal places and round to the nearest 5. (103 => 105, 102 => 100)
As soon as you get into the realm of foreign currency things get really interesting, as you have to deal with conversions, rounding, precision, and the mountains of legal rules. I happen to be a programmer in the international shipping world. Yea for me. ;)
By the way. All the legal rules for dealing with money is the "real" WTF. :)
Admin
Dates stored as encoded integers? Aah! I've inherited a database where a timestamp is stored as a presentation string in DD-MMM-YYYY HH:MM:SS.SSS format and a screen that selected records within date ranges. The SQL to do this was hideous. As you can guess, the performance sucked and an index couldn't be added. But at least the field could be displayed as is.
Oh alright, encoding a timestamp as an integer would allow range selection and working index, making it better than string. Still more painful than using DATE for no benefit.
Admin
Try representing 167 772.17 as a float. For a double it goes to 90 071 992 547 409.93 which seems pretty high but still is not secure (eg. if you are obliged by law to pay four-digits-of-precision tax on total assets of a large company).
Admin
In China, the yuan is divided into 10 jiao and the jiao into 10 fen. When I was in China, I only once received conversion from the Bank of China to the nearest fen. The other times, it was to the nearest jiao (or 1/2 jiao -- Memory fades). I only once ever saw a price expressed using fen and that was for something that would usually be paid using a card system.
Sincerely,
Gene Wirchenko
Admin
This forum has me thinking -- a great many WTFs stem from the resolving of already solved problems. (E.G. refusal to use perfectly good, tested, built-in datatypes like DATETIME, for dubious reasons, leading inevitably to the resolving of useful things like DATEADD(), then refusing to admit that the re-solution might not actually be as good as the already existing solution, etc. etc.)
But lemons to lemonade, right? I'm starting the Society for the Reinvention of the Wheel to try to profit from this. Dues can be paid to me using any level of fixed or floating precision (I'm flexible) in any currency.
Admin
quotequotequotequotequotequotequote
E.g. BALANCE = 10.5900000000001 PAYMENT = 10.59, resulting BALANCE is still greater than 0.0, and someone could get sent a bill for $0.00
quotequotequotequotequotequotequote
This why 3D engines use a "Epsilon" value to test against zero.
if ( dx < EPSILON){
draw( "hit!" );
}
Funny that business software also care about float precision.
Admin
Didn't that happen in Office Space? ;)
Admin
isn't that the office space/ superman III scam? 1/3 ==.33 cents and a third of a cent into my bank account!
Admin
First, the epsilon comparison is more resource-intensive than a simple check against 0. I really don't want to install an "accounting accelerator" or somesuch.
Second, the results of an incorrect calculation in a 3D engine is a slight error in the rendering of one pixel, which might persist for a frame or two and will likely go unnoticed. Epsilon is set to minimize errors, but that doesn't mean that no errors occur, and that's ok because the consequence of an error is small. The same does not hold for money-handling apps.
Admin
How you handle money is your system is completely up to the bank auditors. The funny thing is if you round 2.5 cents in Excel it becomes 2. If you round 2.5 cents in a VBA formula on your excel sheet, you get 3!
Wait.. this has nothing to do with the quote ?!?!
Admin
Good old banker's rounding....
http://en.wikipedia.org/wiki/Banker%27s_rounding
If we adopt the peso we will have none of these rounding problems, for the value of one peso to one dollar is of far greater precision than any of our data types.
Admin
When an owl eats a rat or a mouse or a beetle, it is unable to digest all of the bones and fur and shell parts and whatnot. A couple times a day it regurgitates all of this into a little packet of goods that can be found around the bases of trees. This little packet is called a CASTING. It seems some parallels could be drawn between that and the thought process of the developer of today's code.
Admin
If someone I hired wrote code like this, they'd be needing just as many CASTs for all the places I broke their legs.
Just kidding, I wouldn't really break their legs. I'd murder them.
Admin
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%.
Admin
Admin
Admin
It is still possible to get in trouble. In C, "08" is an illegal octal constant.
Sincerely,
Gene Wirchenko
Admin
It is amazing how many people who read this site probably write crap like this all day. From the 1000 "me 2 me2" comments about floating point to all those who have never actually written sql, the comments are as bad as the code.
It wasn't till the second page that someone actually figured out what the sql data type 'decimal' was. Or the guy who didn't seem to know the difference between money and decimal. It's mostly in the size ;)
Admin
As to that multiplication, multiplying two dollar amounts will yield a quantity whose dimensions are square dollars--which is pretty useless in most situations. So I think that is outside the scope of the issue. (Not to mention that if the quantities weren't known to be exact, you actually have only one decimal--the "least precise" number should be measured by significant digits.)
And your float*float demonstration works in Firefox and Safari too.
Admin
So, it's finally official: size matters
Admin
And even if you do want to store the number as an integer (something I have to deal with in writing, say, gymnastics-scoring software, where a third of a hundredth can be important to keep precise--fixed-precision decimals are no help there), it's still a WTF to call the column Dollars.
Admin
Actually, using a table with pre-calculated date data is a standard practice in data warehouses. When you're dealing with tables that have billions of rows, joining to a date table is much faster than doing a date calculation on every row. Perhaps the person who designed your system used to work with data warehouses and didn't understand that this approach doesn't make much sense outside a data warehousing environment.
Admin
Regarding the DATETIME vs INT field issue, here are possible justifications (my company stores dates as VARCHAR):
If you choose an INT field, of course you will loose DATETIME (proprietary) functions (DATEADD, DATEDIFF, etc), but most of the time anyway, your application should be responsible for such computation.
Feel free to challenge any of the points above :)
SerGioGioGio
Admin
you shouldn't have to worry about sending a bill to a customer for $0.00. unless the people designing your software are idiots (and I know many are) you would never send a bill out for less than $0.39 (or whatever the cost of a stamp is where you live) and you would probably not want to send out a bill for less than $0.50-$0.60 because of printing, paper, labor costs associated with sending out a bill. and if you really were using floating point numbers for price (I agree, is stupid) and wanted to send bills to people with any balance then you should use if (balance >= .01) not if (balance != 0.00). checking equality of floating point numbers is most always a WTF.
Admin
Absolutely right about the portability issue. If you see that it might be possible that you could be changing database types, then the portability of the date time field needs to be considered. As for getting records for a particular day, or month, or whatever, that can easily be done with datetime type using the proprietary functions available, at least I know I can in mysql (gasp!).
If you want to store only the date, then you are not thinking very forward. Space is cheap nowadays, and you have to consider if there is a possibility that you could want the time of the sale as well. Adding the time functionality later is a lot of extra work than just setting it all up for both later.
As for the DATEADD functions and such, I hate to lose that functionality, but I never really trusted them for anything but short time steps (I deal with weather data archival, with the possibility of information going back to the 1900s).
Someone else also mentioned the data validation issue. Being a mysql person, I feel that my programs should be sending my database valid dates. Also, this does make it possible to bring in a NaN analogue, NaD (Not a Date). :-P
The choice of how to represent dates is one not to be made lightly...
Admin
See, that would be the logical thing for a company to do...
However, I once purchased an item at a jewelry shop on credit. I paid off the balance the following week at the shop. A couple of months later, I got a bill for less than 10 cents that was supposedly left on my balance. I suspect that the cashier entered the cent portion wrong when I paid off the balance, but why the heck did they send me a bill for less than 10 cents?! It was a jewelry store! Are they really that hard-pressed for those measly cents to actually pay more to get it?
Sent back an envelope with some pennies...