PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Thangavel, Parameswaran

Hi Team,

 

I am using postgresql version 9.4.1. We are in the process of upgrading to PostgreSQL 10.

 

While doing so, we have upgraded the JDBC postgres driver from 9.1-901.jdbc4 to 42.2.5 Post the upgrade we are facing the issue with data integrity where the numeric value is getting rounded off.

 

Scenario:

I am trying to insert a value (float datatype in Java) and in DB it is represented as Numeric.

 

When I try to store, "1234567" the value is stored as "1234570".

When I try to store, "123456" then the value is stored as "123456"

 

I am not able to understand the behavior of #1, where the digit 7 is dropped and 6 is getting rounded off to 7. When I try #1 using older driver (9.1-901.jdbc4) then all is good.

 

Any thoughts on why this behavior, Is this expected?

 

Java code snippet:

==============

String inserSqlv2 = "INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE, LASTMODIFIED) VALUES (?,?,?,?,?);";

 

Float val = Float.valueOf(1234567f);

preparedStatement = conn.prepareStatement(inserSqlv2);

preparedStatement.setString(1, "d77e7ed20c2b650a148df390a8b3bce9");

preparedStatement.setString(2, "f199af000c2b650a397a8c537ba69d71");

preparedStatement.setString(3, "FLOT_CHECK");

preparedStatement.setFloat(4, val);

preparedStatement.setTimestamp(5, now);

preparedStatement.execute();

 

Table Definition:

=============

CREATE TABLE rsa_rep.abc

(

    id character varying(32) COLLATE pg_catalog."default" NOT NULL,

    idname character varying(32) COLLATE pg_catalog."default" NOT NULL,

    name character varying(1020) COLLATE pg_catalog."default" NOT NULL,

    float_value numeric

)

 

Logs on postgresql:

================

=== when PostgreSQL driver 9.1-901.jdbc4 is used. ===========

2020-06-27 08:07:58.463 GMT [unknown] dba_user 5ef6fe5d.15c8 10/45 0 LOG:  execute <unnamed>: INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE) VALUES ($1,$2,$3,$4)

2020-06-27 08:07:58.463 GMT [unknown] dba_user 5ef6fe5d.15c8 10/45 0 DETAIL:  parameters: $1 = 'd77e7ed20c2b650a148df390a8b3bce9', $2 = 'f199af000c2b650a397a8c537ba69d71', $3 = 'FLOT_CHECK', $4 = '1234567'

 

=== when PostgreSQL driver 42.2.5 is used ===========

2020-06-27 08:08:47.065 GMT PostgreSQL JDBC Driver dba_user 5ef6fe8d.eac 10/63 0 LOG:  execute <unnamed>: INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE) VALUES ($1,$2,$3,$4)

2020-06-27 08:08:47.065 GMT PostgreSQL JDBC Driver dba_user 5ef6fe8d.eac 10/63 0 DETAIL:  parameters: $1 = 'd77e7ed20c2b650a148df390a8b3bce9', $2 = 'f199af000c2b650a397a8c537ba69d71', $3 = 'FLOT_CHECK', $4 = '1.234567e+06'

 

Maven dependency (for reference):

=============================

<dependency>

        <groupId>org.postgresql</groupId>

        <artifactId>postgresql</artifactId>

        <version>42.2.5</version>

        </dependency>

       

        <dependency>

            <groupId>postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <version>9.1-901.jdbc4</version>

        </dependency>

 

 

I have raised the same issue in below site as well….

https://stackoverflow.com/questions/62600304/postgressql-driver-version-12-rounding-java-float-value-to-six-digits

 

Thanks

Param

Reply | Threaded
Open this post in threaded view
|

Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

David G Johnston
On Friday, October 16, 2020, Thangavel, Parameswaran <[hidden email]> wrote:

Any thoughts on why this behavior, Is this expected?

Not immediately, can you demonstrate the problem without using JDBC?  The JDBC project has its own issues setup on GitHub.

preparedStatement.setFloat(4, val);

    float_value numeric

Frankly, I would consider this to be an application bug, incorrectly using the API. BigDecimal maps onto numeric.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

David G Johnston
On Fri, Oct 16, 2020 at 6:32 PM David G. Johnston <[hidden email]> wrote:
On Friday, October 16, 2020, Thangavel, Parameswaran <[hidden email]> wrote:

Any thoughts on why this behavior, Is this expected?

 
Not immediately, can you demonstrate the problem without using JDBC?  The JDBC project has its own issues setup on GitHub.

So, confirming the following query result on head.

select '1234567'::float4::numeric; -- 1234570

Not tested on 9.4.1 (or the last current 9.4.26 release.

The open question is whether the change in result is a bug.  I'll agree it is not expected; but then again floating point and numeric compatibility is not something that I am expecting to make sense at a high-level; the devil is in the details.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Andres Freund
Hi,

On 2020-10-20 09:50:46 -0700, David G. Johnston wrote:
> So, confirming the following query result on head.
>
> select '1234567'::float4::numeric; -- 1234570

The reason this happens is that the float[48]->numeric casts are
implemented by converting the float value to string using
FLT_DIG/DBL_DIG, and then reading that back as a string. Unfortunately
that's pretty bogus - FLT_DIG / DBL_DIG are extremely pessimistic; as it
turns out rounding a few digits beyond the decimal point isn't the same
as before the decimal point.

Unfortunately this can't easily be changed, as any such change would
e.g. break indexes that include float->numeric casts. Including across
major versions, due to pg_upgrade.

For text this is not a problem anymore since v12 (with default settings,
i.e. extra_float_digits > 0), because we now use ryu for those
conversions. But we can't depend extra_float_digits in the case of
float->numeric casts, because it's an immutable function :/

Thanks to Andrew Gierth for some pointers.

Some discussion links:
https://www.postgresql.org/message-id/874lagotif.fsf%40news-spur.riddles.org.uk
https://postgr.es/m/26443.1571712071%40sss.pgh.pa.us
https://www.postgresql.org/message-id/87y37qmwee.fsf%40news-spur.riddles.org.uk

Greetings,

Andres Freund