Strange results when casting string to double

Hi there,

I'm using several (now unsupported) PostgreSQL 9.3.24 servers on 
different (ancient) Ubuntu 14.04 LTS machines. On only one of those 
servers, I get strange/wrong results when converting a string into a 
double value:

SELECT 1.56::double precision;

--> 1.55999999999999   (wrong!)

Although I do not find any differences in configuration, on all other 
servers the result looks like this (correct?):

SELECT 1.56::double precision;

--> 1.56               (correct!)

AFAIK, this conversion is done by internal function float8in, which, 
when called directly, yields the same results:

SELECT float8in('1.56');

--> 1.55999999999999   (wrong!)   on one server, and
--> 1.56               (correct!) on all other servers.

Option extra_float_digits is zero (0) while doing all these tests. Also, 
the problem seems to occur while converting text to double precision and 
not when displaying the obtained double precision value. Why? The binary 
representation of the double precision value is also different.

I've created a small to_bit function in Python to get the double 
precision value's binary representation:


CREATE OR REPLACE FUNCTION to_bit(value double precision)
   RETURNS bit AS
$BODY$
     if 'fn.to_bit_d64' in SD:
         return SD['fn.to_bit_d64'](value)

     import struct
     def to_bit_d64(value):
         return ''.join('{:0>8b}'.format(c) for c in struct.pack('!d', 
value))

     SD['fn.to_bit_d64'] = to_bit_d64
     return SD['fn.to_bit_d64'](value)
  $BODY$
   LANGUAGE plpython3u IMMUTABLE STRICT
   COST 100;


The fraction (mantissa) of both values is different by 1:

value            fraction
1.55999999999999 1000111101011100001010001111010111000010100011110101
1.56             1000111101011100001010001111010111000010100011110110

The fraction of the probably wrong value is one less than the fraction 
of the correct value.

Formatting both values with 20 digits right of the decimal separator 
(like printf("%.20f" ...) yields:

1.55999999999999983124 (wrong!)
1.56000000000000005329 (correct!)

Since even calling function float8in directly returns a probably wrong 
result on one of the servers makes me believe, that there's no custom 
cast in place being responsible for the wrong results.

Function float8in basically relies on C library function

double strtod(const char *str, char **endptr)

which I tested with a tiny C programm (calling strtod only and printing 
the result with printf("%.20f", val);). The result is

1.56000000000000005329 (correct!)

on every server. So, seems like the C library function works as expected 
on all servers.

Although I'm not a C expert, I don't find anything suspicious that 
function float8in does with the value returned from strtod.

In version 9.3.24, file /src/backend/utils/adt/float.c looks a bit 
different from the file in master branch. However, basically both 
versions do much the same things. The old 9.3.24 version does some more 
special error checks (#ifdef HAVE_BUGGY_IRIX_STRTOD, #ifdef 
HAVE_BUGGY_SOLARIS_STRTOD and CHECKFLOATVAL), but these either throw 
errors or set the converted value to return to a special value (if 
indicated).

Has anyone an idea of what's going on here? I know, this version is far 
from still being supported, however, there's not much real changes in 
file float.c between these versions (in other words, this may happen 
with recent versions as well?). The database instances on all servers 
are configured quite the same (more or less). All run with the same 
extensions installed; none is using any preloaded libraries (which may 
replace C library function strtod?).

-- 
Carsten Klein
c(dot)klein(@)datagis(dot)com