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).
