BUG #13885: float->string conversion loses precision server-side on JDBC connection - Mailing list pgsql-bugs

From xtracoder@gmail.com
Subject BUG #13885: float->string conversion loses precision server-side on JDBC connection
Date
Msg-id 20160124202951.2537.67173@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #13885: float->string conversion loses precision server-side on JDBC connection
List pgsql-bugs
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).

pgsql-bugs by date:

Previous
From: djm7@nym.hush.com
Date:
Subject: BUG #13883: Very Important Facility
Next
From: xtracoder@gmail.com
Date:
Subject: BUG #13884: array_to_json() works incorrectly for non-0-based arrays