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
