Re: Function trunc() behaves in unexpected manner with different data types

On Thu, Feb 24, 2011 at 8:03 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Thu, Feb 24, 2011 at 7:31 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> the root issue I think here is that the string version of the double
>> precision math is approximated:
>
> No, it's simpler than that, all double precision math is approximated.
> The root issue is that 2183.67 is not representable in a floating
> point binary number. Just like 1/3 isn't representable in base 10
> (decimal) numbers many fractions aren't representable in base 2
> (binary) numbers. The result are repeated decimals like 0.3333... if you
> multiply that by three you get 0.99999 and if you truncate that you
> get 0 insted of 1.
>
> It's the trunc() that's exposing the imprecision because like "=" it
> depends on the precise value of the number down to the last digit.
> Though depending on the arithmetic you can always make the precision
> expand beyond the last digit anyways -- when you multiply by 100 you
> magnify that imprecision too.

right -- in understand how floating point works -- but are you are
saying that you are ok with the fact that (for example) a table with a
floating point unique key could dump and not restore? more
specifically, a binary dump would restore but a text dump would not.
I think this is a problem with our implementation -- not all versions
of 2183.68 as outputted from the server are the same internally.

put another way, text output from the server should unambiguously
match what sourced the text. in the case of floating point, it does
not...there are N versions of internal data that can match particular
text output. I am speculating that the rounding is happening in the
wrong place maybe.

merlin