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

From: xtracoder(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13885: float->string conversion loses precision server-side on JDBC connection
Date: 2016-01-24 20:29:51
Message-ID: 20160124202951.2537.67173@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13885
Logged by: XtraCoder
Email address: xtracoder(at)gmail(dot)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).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message rwestlun 2016-01-25 07:48:06 BUG #13886: When INSERT ON CONFLICT DO UPDATE updates, it returns INSERT rather than UPDATE
Previous Message xtracoder 2016-01-24 10:47:02 BUG #13884: array_to_json() works incorrectly for non-0-based arrays