Thread: Crazy Multiplication Issue

Howdy Gents-

I'm sure I'm having an "oh duh!" moment, but can anyone tell me what is going on here.

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!

Using 7.2.1 on freeBSD

"Dave Horn" <selurevad@yahoo.com> writes:
> If I perform
> select col1 * col2, 213.1086 * .833 from tableA
> I get the following values
> 177.519464 and 177.583396

Works for me ... you *sure* it hasn't got something to do with the
datatypes you're using?  Specifically, float4 is only good to about
six digits, so the bogus result is unsurprising if any of the values
involved are float4.

            regards, tom lane

> 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