BUG #14138: Inconsistent rounding behavior in float4 coercion

cooper.charles.m@gmail.com writes:

Based on my reading of the documentation
(http://www.postgresql.org/docs/9.5/static/datatype-numeric.html), float4
only supports up to 6 decimal places of precision (even if the number fits
comfortably in a 4-byte float). This is fine but I am finding it difficult
to predict which way the float will truncate to:

I think you misunderstand floating-point arithmetic entirely. It's
rounding to the nearest so-many-digits binary value, which makes the
change in the last decimal digit hard to predict. The business about
"6 decimal digits" is only meant to indicate that you can expect that
many decimal digits to be reproduced reliably; whether and how digits
after the 6th change is a complicated business.

You can see more nearly what's going on by increasing extra_float_digits
so that more digits are printed. For example, at extra_float_digits = 3
it'll print a 9-decimal-digit rather than 6-decimal-digit approximation
to the underlying binary value, so:

regression=# select 15422.55 :: float4;
float4
------------
15422.5498
(1 row)

regression=# select 15422.5501 :: float4;
float4
------------
15422.5498
(1 row)

regression=# select 15422.5502 :: float4;
float4
------------
15422.5498
(1 row)

regression=# select 15422.5503 :: float4;
float4
------------
15422.5508
(1 row)

...

regression=# select 15422.5512 :: float4;
float4
------------
15422.5508
(1 row)

regression=# select 15422.5513 :: float4;
float4
------------
15422.5518
(1 row)

So adjacent binary values are about .001 apart in this range of values,
but they don't correspond exactly to multiples of .001. Near the
other value you cited:

regression=# select 11143.15 :: float4;
float4
------------
11143.1504
(1 row)

...

regression=# select 11143.1508 :: float4;
float4
------------
11143.1504
(1 row)

regression=# select 11143.1509 :: float4;
float4
------------
11143.1514
(1 row)

They're still about .001 apart, but now a little closer to the decimal
value below rather than the decimal value above, which accounts for the
different rounding behavior when showing fewer digits than this.

Regardless of the default rounding mode (up / down / half even /
you-name-it) I would expect these two to round in the same direction -- or
at least the rounding algorithm to be specified and documented.

It's not really Postgres' business to try to document these rounding
behaviors, because as far as we're concerned they are platform-dependent.
Most modern machines follow IEEE 754,
https://en.wikipedia.org/wiki/IEEE_floating_point
but that's by no means universal and Postgres doesn't assume it.

If this is not something you're willing to deal with, use type numeric
rather than float4 or float8 --- numeric stores values in decimal notation
internally, so you don't get these odd roundoff effects from change of
base.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thanks for taking the time to show me how to print those extra digits of
precision, it really helped!

As a follow-up question, I am a bit confused as to the following behavior:

elsendb=> set extra_float_digits = 3;
SET
elsendb=> select 11143.15 :: float4;
float4
------------
11143.1504
(1 row)

elsendb=> select 11143.15 :: float4 :: text :: float4;
float4
------------
11143.1504
(1 row)

elsendb=> select 11143.15 :: float4 :: numeric :: float4;
float4
------------
11143.2002
(1 row)

How come 11143.1504 is not printed in all of these cases?

Best,
Charles

On Sun, May 15, 2016 at 1:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Charles..

On Sun, May 22, 2016 at 9:18 PM, Charles <cooper.charles.m@gmail.com> wrote:

As a follow-up question, I am a bit confused as to the following behavior:

elsendb=> set extra_float_digits = 3;

...

elsendb=> select 11143.15 :: float4 :: text :: float4;

...

11143.1504
elsendb=> select 11143.15 :: float4 :: numeric :: float4;

...

11143.2002

How come 11143.1504 is not printed in all of these cases?

Probably because extra_f_d only plays when converting to/from text,
and not to numeric. It really is a mechanism to insure that text dumps
+ restores do not modify data. The other way is to use hex or binary
floating ( %a in printf ) in dumps, which, IIRC, postgres did
understand on input but had no way to generate on output.

If you insist on mixing finite precisison binary fractions ( float )
with decimal fractions ( numeric and/or text ) you should be prepared
for this. Do not do it or use a text intermediate step always so you
can control it.

Francico Olarte.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Charles <cooper.charles.m@gmail.com> writes:

As a follow-up question, I am a bit confused as to the following behavior:
...
How come 11143.1504 is not printed in all of these cases?

float4 to numeric casting doesn't honor extra_float_digits, it just
converts as many digits as will be reliable.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I see, so the overriding, default rule in all these cases is, "convert as
many digits as will be reliable" -- as opposed to, "convert as many digits
as are needed to do a full round-trip"?

Best,
Charles

On Mon, May 23, 2016 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: