Re: Crazy Multiplication Issue

> Howdy Gents-

Howdy to everyone else.

> I am multiplying two columns it doesn't matter what their data type is
> not does it seem to matter if I explicitly cast these values to some
> other data type every time they are multiplied I am getting an
> incorrect result.
>
> col1 = 213.1086
> col2 = 0.833
>
> If I perform
>
> select col1 * col2, 213.1086 * .833 from tableA
>
> I get the following values
>
> 177.519464 and 177.583396
>
> What I am doing wrong!?!??? I need to get the expected 177.583396
> value. Thanks for any assistance!

I think the exact product is 177.5194638 (try SELECT 2131086 * 833), so
the column product =is= closer to correct than your "expected value".
Neither 213.1086 nor 0.833 is exactly representable in floating poit,
so you shouldn't expect any particular approximation, unless you're
sure of IEEE arithmetic, etc. Perhaps you want to use a NUMERIC type,
where you can declare the precision you require, e.g., NUMERIC(20, 10)
should do exact arithmetic to 10 decimal places (I think).

- John D. Burger
MITRE