Thread: BUG #13885: float->string conversion loses precision server-side on JDBC connection

The following bug has been logged on the website:

Bug reference:      13885
Logged by:          XtraCoder
Email address:      xtracoder@gmail.com
PostgreSQL version: 9.5.0
Operating system:   Window 7
Description:

I have a PostgreSQL function, which accepts JSON, performs some processing,
and returns JSON. Something strange is happening when calling stored
procedure from Java application - float->string conversion is incorrect.

Processing is mostly filtering of data and returning restructured and
reorganized result. For simplicity let's assume input is map `[name->float]`
and output is `[float]`.

The problem is that during intermediate data storage of values into native
PostgreSQL data type float values loose precision/accuracy/formatting.

Here is the code to reproduce

    CREATE OR REPLACE FUNCTION do_dummy()
        RETURNS void LANGUAGE plpgsql AS $$
    DECLARE
        v_float float[];
        v_json jsonb;
        v_str varchar;
    BEGIN
        v_float[0] = 4.1;
        raise notice 'v_float = %', v_float[0];
        raise notice 'jsonb float -> %', ('{"v": 4.1}'::jsonb)->'v';
        raise notice 'jsonb float ->> %', ('{"v": 4.1}'::jsonb)->>'v';

        v_float[0] = ('{"v": 4.1}'::jsonb)->>'v';
        raise notice 'jsonb float ->>::float %', v_float[0];

        v_json = array_to_json(v_float);
        raise notice 'jsonb: %', v_json;

        v_str = concat('jsonb as string: ', v_json::varchar);
        raise notice '%', v_str;
    END $$

When executing

    select do_dummy();

... via pgAdmin, output is following and is as expected:

    NOTICE:  v_float = 4.1
    NOTICE:  jsonb float -> 4.1
    NOTICE:  jsonb float ->> 4.1
    NOTICE:  jsonb float ->>::float 4.1
    NOTICE:  jsonb: [4.1]
    NOTICE:  jsonb as string: [4.1]

When executing same from Java application, result is following

    NOTICE: v_float = 4.0999999999999996
    NOTICE: jsonb float -> 4.1
    NOTICE: jsonb float ->> 4.1
    NOTICE: jsonb float ->>::float 4.0999999999999996
    NOTICE: jsonb: [4.0999999999999996]
    NOTICE: jsonb as string: [4.0999999999999996]

Since no data is transferred to/from server in function call (except
'notice' messages generated server-side) - problem occurs completely
server-side, but something in JDBC driver is triggering the problem.

-------------------------------------------------------
What can be wrong with JDBC connection in this regard?
-------------------------------------------------------

I'm using jdbc driver v9.4.1207 (the latest one at the moment).
Tested from my own app, http://www.squirrelsql.org/ and
http://bits.netbeans.org/download/trunk/nightly/latest (latest dev
version).

xtracoder@gmail.com writes:
> I have a PostgreSQL function, which accepts JSON, performs some processing,
> and returns JSON. Something strange is happening when calling stored
> procedure from Java application - float->string conversion is incorrect.

I don't think it's "incorrect".  I believe the displayed difference here
is because the JDBC driver sets extra_float_digits to 3 or so.
Compare this in psql:

regression=# do $$ declare x float := 4.1; begin raise notice 'x = %', x; end$$;
NOTICE:  x = 4.1
DO
regression=# set extra_float_digits = 3;
SET
regression=# do $$ declare x float := 4.1; begin raise notice 'x = %', x; end$$;
NOTICE:  x = 4.09999999999999964
DO

The uglier-looking number is a more precise representation of the actual
float4 value.

            regards, tom lane

Yes, this is not bug in terms of 'float->string' convertion - people in
other forum also pointed out to JDBC, "extra_float_digits" and consequences.


On Mon, Jan 25, 2016 at 7:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> xtracoder@gmail.com writes:
> > I have a PostgreSQL function, which accepts JSON, performs some
> processing,
> > and returns JSON. Something strange is happening when calling stored
> > procedure from Java application - float->string conversion is incorrect.
>
> I don't think it's "incorrect".  I believe the displayed difference here
> is because the JDBC driver sets extra_float_digits to 3 or so.
> Compare this in psql:
>
> regression=# do $$ declare x float := 4.1; begin raise notice 'x = %', x;
> end$$;
> NOTICE:  x = 4.1
> DO
> regression=# set extra_float_digits = 3;
> SET
> regression=# do $$ declare x float := 4.1; begin raise notice 'x = %', x;
> end$$;
> NOTICE:  x = 4.09999999999999964
> DO
>
> The uglier-looking number is a more precise representation of the actual
> float4 value.
>
>                         regards, tom lane
>