Обсуждение: real -> numeric -> real result different through jdbc

Hi there,

the following function:

CREATE OR REPLACE FUNCTION test_reals(OUT r1 REAL, OUT r2 NUMERIC, OUT r3 NUMERIC, OUT r4 REAL, OUT r5 NUMERIC)
    RETURNS SETOF RECORD AS $$
    DECLARE
        temp REAL[5];
    BEGIN
        SELECT ARRAY[ '0.6'::real,
                      ('0.6'::real)::numeric(5,3),
                      round(('0.6'::real)::numeric(5,3), 3),
                      round(('0.6'::real)::numeric(5,3), 3)::real,
                      round(('0.6'::real)::numeric(5,3), 3)::numeric ]::real[]
        INTO temp;
        RAISE NOTICE 'temp: %', temp;
        r1 := temp[1]; r2 := temp[2]; r3 := temp[3]; r4 := temp[4]; r5 := temp[5];
        RETURN NEXT;
    END;
$$ LANGUAGE 'plpgsql';

called this way:

SELECT * FROM test_reals();

emits:

NOTICE:  temp: {0.6,0.6,0.6,0.6,0.6}  if called from psql/pgadmin

and

NOTICE:  temp: {0.60000002,0.60000002,0.60000002,0.60000002,0.60000002}  if called through jdbc

what's the story here? how can the jdbc driver affect the precision of real to numeric / numeric to real casts within PL/pgSQL functions?

we are using the 8.4-701-jdbc4 driver, connecting to a UTF8 database on a 8.4.1 PostgreSQL server

TIA,

Michael

Michael Nacos <m.nacos@gmail.com> writes:
> what's the story here? how can the jdbc driver affect the precision of real
> to numeric / numeric to real casts within PL/pgSQL functions?

I seem to recall hearing that JDBC fools with the extra_float_digits
setting.  The "0.60000002" looks like it probably is the actual result
of casting 0.6 to float4 --- remember float4 is only good to about six
decimal digits.  The default display of float4 rounds off at six digits,
but in some contexts you don't want that rounding to happen because it
might result in losing even more precision.  JDBC is being conservative
and taking the latter approach.

            regards, tom lane

thanks tom,

we have just discovered this setting hidden away in ConnectionFactoryImpl.java of the JDBC sources.
surely, it's not just a case of float4 display, type casting seems to be affected, too.
does casting use the default display or does the internal representation change?
It is causing our regression tests, which test for equality at the SQL level, to fail.

cheers, Michael

> surely, it's not just a case of float4 display, type casting seems to be affected, too.

I have run some more tests and I'd like to take this back. The reason our equality tests fail when
extra_float_digits = 2 is this: we are constructing dynamic SQL statements inside a PL/pgSQL
function. The SQL produced is, of course, affected by the textual representation of REALs in effect,
hence the problems we have encountered. Thanks, this is making much more sense now.

Michael