Comment On Functional Multiplication

f1rst

Alex Papadimoulis:

One of the more interesting bad ideas the consultants had was to use only integers to store all non-textual data.

"We got both kinds of data, INTs and VARCHARs!"

Nowhere during the time they were writing out all of these castings did they ever ask themselves



"Is there a better way to do this?"



Sitting in a corner, crying...

A 15% fee on a dollar amount is still the same as a 15% fee on a cent amount. That's the real WTF right there.

You could always round it to the nearest hundred if you needed it in dollars...which means you save NOTHING by storing the value as an INT (presumably to avoid rounding problems.)

So what year causes integer overflow? :)  That's probably the worst way to store dates.  They could have stored it as 3 columns to aid in parsing...

</sarcasm>

This may be a stupid question, but - isn't it better to store such information in integers (or longs) instead of doubles? Wouldn't that guarantee better accuracy? Or is there some other option? Please enlighten me.

Anonymous:

So what year causes integer overflow?
:)  That's probably the worst way to store dates.  They could
have stored it as 3 columns to aid in parsing...

True, they do avoid the dreaded Y2.038K bug that plauge many other systems...

WeatherGod:

Nowhere during the time they were writing out all of these castings did they ever ask themselves



"Is there a better way to do this?"



Sitting in a corner, crying...

I'm sure there is, but if it was easy for the full-time employees to maintain, then how would the consultants get hired back to fix stuff later ;)

Forgetting the obvious absurdity, if you're going to do stuff like this, at least use a couple of local temp variables in the proc so it's at least readable by the next person, and make sure that the variable names are encrypted ;)

I'm not sure about SQL, but in C++ it's quite easy to calculate p percents of integer:

inx x,p;// windows variables?

(x*p)/100

If you want to round it up (ceil), it's still easy:

(x*p+99)/100

If you want to round it if it reaches >0.5 then probably:

(x*p+50)/100

If you want to round it to the whole dolars and x is in cents it should be something like:
(((x*p)/100)/100)*100 = (x*p)/10000*100

etc...what are all those IF's for?

Everything in integers?  Yet another example of where BrainF*** would be at its prime.

BOSH!!!!

Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys.  One for the date and one for the time.  The reason why this code is embedded within a SQL
Function is to abstract this logic from the clueless FTEs that take over the project once the talent leaves.   

tdog 

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

As for the date garbage, why wouldn't they just use a Unix timestamp?  Seems much easier to me...

Anonymous:

BOSH!!!!

Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys. 

Because otherwise when you multiply 10am by last friday, your resulting date (its still a date, right?) as a floating point number has lost its precision?

Or because when your application really needs to do some intensive date-arithmetic at the core of a N^3 loop, you can't afford the floating point overhead?

I'm not sure I understand .... but I do like how I can have 2006-18-50!

I don't think this guy was looking for the best way to store data.
If he was looking for the best way (or had his eyes open at all) he could have found it.
He was obviously in a dark, dark room with only a morse code-based input device (instead of a keyboard), and no food or water. And he was blind, and deaf as well.
He also should have stored his VARCAHR data in GIF files, then saved the image files in a BLOB field. Everyone knows that's way faster, and prettier one you add all the special effects and lighting.

"(1 + CAST(15 AS FLOAT)/100)"

Is it just me, or is this stupider than normal? What's wrong with typing "1.15" ?

This is not to mention the fact that you could replace the entire function with this:
SET @retvalue = CAST(CAST(@amount AS FLOAT) * 0.0115 AS INT)
RETURN @retvalue

Anonymous:

BOSH!!!!

Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys.  One for the date and one for the time.  The reason why this code is embedded within a SQL
Function is to abstract this logic from the clueless FTEs that take over the project once the talent leaves.   

tdog 

Quotes don't like me, so I repeat :)

Because otherwise when you multiply 10am by last friday, your resulting date (its still a date, right?) as a floating point number has lost its precision?

Or because when your application really needs to do some intensive date-arithmetic at the core of a N^3 loop, you can't afford the floating point overhead?

I'm not sure I understand .... but I do like how I can have 2006-18-50!

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.

Anonymous:

BOSH!!!!

Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys.  One for the date and one for the time.  The reason why this code is embedded within a SQL
Function is to abstract this logic from the clueless FTEs that take over the project once the talent leaves.   

tdog 

Umm, are you retarded?

PHP Posible explanation:

- - - - - - -
http://es2.php.net/floor
- - - - - - -
jolyon at mways dot co dot uk
10-Aug-2004 06:41
Beware of FLOAT weirdness!

Floats have a mind of their own, and what may look like an integer stored in a float isn't.

Here's a baffling example of how floor can be tripped up by this:


$price = 79.99;

print $price."\r\n"; // correct result, 79.99 shown

$price = $price * 100;

print $price."\r\n"; // correct result, 7999 shown

print floor($price); // 7998 shown! what's going on?


The thing to remember here is that the way a float stores a value makes it very easy for these kind of things to happen. When the 79.99 was multiplied by 100, the actual value stored in the float was probably something like 7998.9999999999999999999999999999999999, PHP would print out 7999 when the value is displayed but floor would therefore round this down to 7998.

THe moral of this story - never use float for anything that needs to be accurate! If you're doing prices for products or a shopping cart, then always use an integer and store prices as a number of pence, you'll thank me for this later :)

- - - - - - -

The "pennies as ints" approach isn't so bad but what really puzzles me is 

(1 + CAST(15 AS FLOAT)/100)

Isn't that always 1.5? I'm no SQL expert but it seems like it has floating point constants, doesn't it? But then that would be leaving the spirit of Integerness.

 

Re-post


Post found on the PHP manual.



10-Aug-2004 06:41

Beware of FLOAT weirdness!



Floats have a mind of their own, and what may look like an integer stored in a float isn't.



Here's a baffling example of how floor can be tripped up by this:



$price = 79.99;



print $price."\r\n"; // correct result, 79.99 shown



$price = $price * 100;



print $price."\r\n"; // correct result, 7999 shown



print floor($price); // 7998 shown! what's going on?



The thing to remember here is that the way a float stores a value makes it very easy for these kind of things to happen. When the 79.99 was multiplied by 100, the actual value stored in the float was probably something like 7998.9999999999999999999999999999999999, PHP would print out 7999 when the value is displayed but floor would therefore round this down to 7998.



THe moral of this story - never use float for anything that needs to be accurate! If you're doing prices for products or a shopping cart, then always use an integer and store prices as a number of pence, you'll thank me for this later :)

Alex Papadimoulis:

The "Sale Date" column really stored the year number multiplied by 10,000 plus the month number multiplied by 100 plus the day number (e.g. today would be 20,060,426).

This isn't really all that bad, it's just stated in an way to make it sound more complicated than it is.  If you write that number without commas, it's much clearer what is meant by 20060426.  Which is actually a valid ISO 8601 timstamp (http://en.wikipedia.org/wiki/ISO_8601).  You can still sort by date correctly, and you can get rows with dates in a given range.

This still has its problems--math can't be simply performed on the numbers (adding 10 days would give 20060436, for example), and there are a lot of invalid dates--but it's much better than, say, 04262006.  Plus it conforms to a standard at least (even if by accident).

kipthegreat:

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

As long you never need to refer to less than 1cent or a fraction of a cent this method is fine. For example, what is the unit price of a washer if 100 washers cost $2.49? 

OOps, 1.15 is what I meant.

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.

First time I read this WTF, I thought "That's what you have to do if your language doesn't support proper decimal rounding."



Then I saw the casts to float. :|

/// Quoted post

Let me be the first on this post to claim NO WTF.  Considering performance and data integrity, you should always convert things such as dates to two integer keys.  One for the date and one for the time.  The reason why this code is embedded within a SQL
// end quoted post

Convert dates to two integer keys?   Is there a SQL database implementation out there that does not have some sort of Date dataype?  It would seem simpler and cleaner to just use a built-in date type and leave the two-column integer splitting for the consultants who need things to be enterprisy.

Or were you being sarcastic?  If so, you need to use more emoticons.

 

Dooku

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

So use a fixed decimal type.  COBOL can handle it.  Can your language of choice handle it?

As for the date garbage, why wouldn't they just use a Unix timestamp?  Seems much easier to me...

It is troublesome when dates before the epoch are to be represented.

Sincerely,

Gene Wirchenko

Yeah... storing dollar values as a double is a bad bad bad idea. 
You end up getting crap like 204.59 - 204.59 = 0.000000000121212. 
I do believe, at least in sql server land, storing them as a decimal is
fine though.

The real WTF is Mozilla using the backspace key as a shortcut for "go back.."



Really, the person who did that had to know it was retarded. And whoever did it in IE too.

//Dude, this bulletin board software sucks... // Quote starts

Yeah... storing dollar values as a double is a bad bad bad idea.  You end up getting crap like 204.59 - 204.59 = 0.000000000121212.  I do believe, at least in sql server land, storing them as a decimal is fine though.
// quite ends

I hope that no one suggested they store their money values as floats, that's pretty obviously a bad idea.  But once again, don't databases usually come with a MONEY type, and allow you to specify the number of decimal points of precision?  Or are we using home-rolled flat file databases that only allow us ints, floats, and varchars?

Of course, an especially cool way would be to store all money values as strings in the DB: "$15.99", and then write routines in code to convert those to money objects.  Or maybe save values as gifs (in BLOBS) and use OCR to convert back from GIF to numeric values in code. 

 

Word,

Dooku

Anonymous:

kipthegreat:

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

As long you never need to refer to less than 1cent or a fraction of a cent this method is fine. For example, what is the unit price of a washer if 100 washers cost $2.49? 

And good luck if you ever need to represent the price of a litre of gas (or a gallon in less enlightened countries), which around here is 99.9 cents today.

The whole issue of accuracy and precision in numerical computations is more complex than it appears at first glance.    Definitely don't use (binary) floating point to represent decimal (fractional) numbers if accuracy is at all important to you, which it usually is.  Either integers or decimal fractional data types are much better.  But equally important is to keep track of how many digits of precision you have in your computation, since most decimal (fractional) numbers we encounter in day-to-day usage are inexact, including prices.  (But not including dates!)

I am trying to have this discussion with some of my co-workers and it's an uphill battle.  "What do you mean by 'inexact', exactly??  Aren't all numbers exact??  I'm just going to get around the problem by arbitrarily defining all these decimal values in the database as being exact so I don't have to worry about it."

Yes, SQL Server does have a MONEY datatype. It caps things off at 4 decimal places.

I admit that I have also built a system that stored money as cents instead of euros.



However if I had to make a database design for one now I would probably use Decimal, it seems a bit more intuitive.



Does anybody have a good argument one way or the other, though?

"Cliché" is not an adjective. It is a noun. The adjective is "clichéd", if you really feel compelled.

Sarcasm precedes emoticons and will outlast them.

Oh come on!

Integers are for the tax to be correct:

Example:

You get a NET value of a thing say $61.1930(a discount o some wierd caclulation)

Round it and dislplay it into a bill: $61.19

Add VAT(20%): $12.2386 - round it and add to bill $12.23

Add NET+VAT and round it: $73.43!.(Now explain that to the client,and the tax inspector)

And then try to figure out what hell do we in telco industry have to go through just to get the correctess of a 1C call with VAT 18%?????(Or something else non standart)

In what sense is that code "functional"?

Edgar Plonk:

"Cliché" is not an adjective. It is a noun. The adjective is "clichéd", if you really feel compelled.

Hey, this is a programming WTF forum.  Not a grammatical WTF forum. If you want to discuss grammar, get your own WTF forum.  I mean really, WTF!

This is really a pretty good piece of obfuscation.

I have to assume that the calculation always rounds downward, and he's somehow transferring the fractional pennies to a secret bank account somewhere. But I just can't figure out how he's doing it.

It's probably hidden in the line where he compares the float value to the result of the float-to-int-to-float conversion.

Just put this into sql server 2005:

SELECT [money] = $340.33 * $1.155,
[decimal] = 340.33 * 1.155

money = 393.0812
decimal = 393.08115

It rounded correctly to precision of 4 decimal places when US currency is only 2. Would not the proper procedure to be to keep the extra decimals around as long as possible and only round them for the final currency amount spit out to the UI/File/Bill/Whatever?

JAlexoid:

Oh come on!

Integers are for the tax to be correct:

Example:

You get a NET value of a thing say $61.1930(a discount o some wierd caclulation)

Round it and dislplay it into a bill: $61.19

Add VAT(20%): $12.2386 - round it and add to bill $12.23

Add NET+VAT and round it: $73.43!.(Now explain that to the client,and the tax inspector)

And then try to figure out what hell do we in telco industry have to go
through just to get the correctess of a 1C call with VAT 18%?????(Or
something else non standart)

Um... 12.2386 rounded = 12.24



61.19 + 12.24 = 73.43

>> storing dollar values as a double is a bad bad bad idea

No.... Calculating dollar values as a double is a bad idea.

However, as far as I know all major sql database (ie, both Oracle & MS SQL) store numeric data as BCD (http://en.wikipedia.org/wiki/Binary-coded_decimal), which is pretty much the same as storing it as an int, except SQL takes care of the decimal point for you.

 

I do not intend to start an argument, but his comment does have some merit.

I disagree about their date storage based solely on data integrity. It is possible to store date/time values as ticks (or any other unit of measure) since the beginning of time (00:00:00 Jan 01, 1753 for SQL Server if my memory serves me correctly) without losing the integrity of the data or any computational performance. It might even speed things up if the computations were done outside of the database, but who really uses tiers anyway?

As for the dollar amount, my problem starts 3 years from now when the application goes international and the units of currency do not always break down into 1/100 increments, but then again, who really implements international currencies properly in databases. I have no problem with the storage as integers because it saves space and computational overhead. If implemented properly you typically perform integer arithmetic which is much faster and less error-prone than floating-point arithmetic. It's really a matter of unit of measure(i.e. dollars vs. cents) and if that unit of measure can contain fractional pieces.

As for the decision to use this design for all data, that is just stupid. Data types were created for exactly that reason. This borders on creating the system within a system from a few days ago. If they want to do that, then just store serialized objects as binary and completely screw over the DBA.

Code Monkey:

my problem starts 3 years from now when the application goes international

You obviously have more confidence in this company than I do.  :)

Anonymous:

Convert dates to two integer keys?   Is there a SQL database implementation out there that does not have some sort of Date dataype?  It would seem simpler and cleaner to just use a built-in date type and leave the two-column integer splitting for the consultants who need things to be enterprisy.

Or were you being sarcastic?  If so, you need to use more emoticons.

Dooku

Hey Dooku,

No actually, I'm not being sarcastic.  Once you get a database that is in the hundreds of millions of rows, you will find that comparing dates becomes a very big deal.  And what's wrong with emoticons?  JK

Interestingly enough, most currency exchange places use precision of 6 to convert.

Of course, if you're not storing the money centrally and have servers located at various international operations facilities it's going to be compiled and converted in a process outside the database anyways. For practical reasons I'd think you'd want to keep the local box in the native currency and only convert if you are reporting or transferring the money.

That sounds nice, but in the real world the money has to be tracked in the originating currency. Exchange rates fluctate. That means storing all values in the local currency would cause historical values would change over time. The only way around that is to store a historical record of exchange rates and convert the values at runtime based on the exchange rate used at the time of entry. I have worked on a similar system and I believe it qualifies as a WTF.

Anonymous:

The real WTF is Mozilla using the backspace key as a shortcut for "go back.."

Really, the person who did that had to know it was retarded. And whoever did it in IE too.

[^o)]

IE works the same way!

Anonymous:

Anonymous:

The real WTF is Mozilla using the backspace key as a shortcut for "go back.."

Really, the person who did that had to know it was retarded. And whoever did it in IE too.

[^o)]

IE works the same way!

 

Uhhh, never mind... I just finished reading the entire post!