Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmbNk-008r9v-6P for pgsql-jdbc@arkaria.postgresql.org; Tue, 19 Mar 2024 15:28:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rmbNi-00GDMQ-AA for pgsql-jdbc@arkaria.postgresql.org; Tue, 19 Mar 2024 15:28:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmQ3A-0090Fl-No for pgsql-jdbc@lists.postgresql.org; Tue, 19 Mar 2024 03:22:05 +0000 Received: from mail-pl1-x634.google.com ([2607:f8b0:4864:20::634]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rmQ33-005Fd2-0M for pgsql-jdbc@lists.postgresql.org; Tue, 19 Mar 2024 03:22:03 +0000 Received: by mail-pl1-x634.google.com with SMTP id d9443c01a7336-1deffa23bb9so22536605ad.2 for ; Mon, 18 Mar 2024 20:21:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710818517; x=1711423317; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from :content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=IamD1l3Ka+K01R7rXbhoR3kXL4PjoNNwA8RvUShsaT4=; b=azOqEOToAzTP82Mgw6Q8QMjWf0DM/VtuMvilHeiM3eGVLV0D8NsuqqtD8puWFFicI2 AqHim+sJBHyizXwgIxVVlfovmskpvhcBUFcqK4WeB3DbdF5G1/TnfghJdW7Y5/9cPukg PRGORLH1YWHv5cMIwIvQjzL2dydZydTw86bBei/jgMmJEKveZ2Av1X44sTGDC+FGUgem 5HZL88DeCUW47lZANW16/wLxtYp+rGmTUCdPaZ1e3K8Blj5QUgm8DlwkcL8xns4VN+Fn pV6L1we2Fh2dLFmsljf2ED1QU4/kWEV+jalLRsOlwZQdpa9byYb04maKbnSddG3ZAmO9 8dRg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710818517; x=1711423317; h=to:date:message-id:subject:mime-version:from :content-transfer-encoding:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=IamD1l3Ka+K01R7rXbhoR3kXL4PjoNNwA8RvUShsaT4=; b=tBOTQHFtmOrztEXbIZBazaLBi3OdwHblM5cqtYHnqXEHSMxB/gcjc/ckPK5P5eYDv4 5Svw5ua2uVsxSlTUPsbxO45JysUi3uh6wAXrj8GP7w4cvyuVMM0kBNjmna3CZySHp7kl ZqUKsbwLk21lpLv9kTCLruhtiwoxKRi5MUNeCu0m3crC1yNxGMFQndr8MG1ILsZJWV6c XJVTDXfShtVJ5l70Cz1GTpqvUod8rIndwEG4csGcZctKxpRLcD7j0d3JckpX68j+Hj0e 6EKhG69Kp+IX9H4cyKoyEtzXbH4r0t+HzRPk27wnDj3Bd+WwBAOaiHnYjWbhcjaWBKs2 1Qfw== X-Gm-Message-State: AOJu0YwvGDkWPvzFiVSPl0BDTUrEsyriWxtFpEq9JMepqli9Zq2er/zt aSoEau8vXyKkBrkRj8TnsU48QFvPdTc2V8RsREdU6WefXEjiXQvBhaWeSoRTiNU= X-Google-Smtp-Source: AGHT+IE8p2xsDbiW15bZMXwwUPbXcSBVsWsO+fxpSo2ckwYCac9t5Miy5JUVE5gX0i2RiLTVKFeVXw== X-Received: by 2002:a17:902:db0d:b0:1e0:188a:5ae7 with SMTP id m13-20020a170902db0d00b001e0188a5ae7mr7581410plx.49.1710818516560; Mon, 18 Mar 2024 20:21:56 -0700 (PDT) Received: from smtpclient.apple ([2405:201:6823:687b:7919:de:c96b:e4aa]) by smtp.gmail.com with ESMTPSA id k7-20020a170902c40700b001dedfba4c69sm9428008plk.134.2024.03.18.20.21.56 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 18 Mar 2024 20:21:56 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-4CDD2AD6-1D28-4C7D-ACDC-D9BB315F3E05 Content-Transfer-Encoding: 7bit From: Rahul Uniyal Mime-Version: 1.0 (1.0) Subject: Java - Inconsistent behaviour with double precession data type Message-Id: Date: Tue, 19 Mar 2024 08:51:44 +0530 To: pgsql-jdbc@lists.postgresql.org X-Mailer: iPhone Mail (21D61) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-4CDD2AD6-1D28-4C7D-ACDC-D9BB315F3E05 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hello Team, Hope everyone is doing well here. I am writing this email to understand an issue I'm facing when fetching data= in our Java application. We are using PostgreSQL JDBC Driver version 42.6.0= . Issue: We are encountering an issue where the double precision data type in Postgre= SQL is giving some intermittent results when fetching data. For example, in t= he database the value is 40, but sometimes this value is fetched as 40.0. Si= milarly, for a value of 0.0005, it is being fetched as 0.00050, resulting in= extra trailing zeros. Observations :-=20 Below are my observations when i was debugging the code of postgres-jdbc dri= ver for double precision data type. 1- When the value in DB is 40 and fetched value is also 40 A - In the QueryExecuterImpl class method - receiveFields() , we creat= e Fields metadata=20 private Field[] receiveFields() throws IOException { pgStream.receiveInteger4(); // MESSAGE SIZE int size =3D pgStream.receiveInteger2(); Field[] fields =3D new Field[size]; if (LOGGER.isLoggable(Level.FINEST)) { LOGGER.log(Level.FINEST, " <=3DBE RowDescription({0})", size); } for (int i =3D 0; i < fields.length; i++) { String columnLabel =3D pgStream.receiveCanonicalString(); int tableOid =3D pgStream.receiveInteger4(); short positionInTable =3D (short) pgStream.receiveInteger2(); int typeOid =3D pgStream.receiveInteger4(); int typeLength =3D pgStream.receiveInteger2(); int typeModifier =3D pgStream.receiveInteger4(); int formatType =3D pgStream.receiveInteger2(); fields[i] =3D new Field(columnLabel, typeOid, typeLength, typeModifier, tableOid, positionInTable); fields[i].setFormat(formatType); LOGGER.log(Level.FINEST, " {0}", fields[i]); } return fields; } Output of this method is - [Field(id,FLOAT8,8,T), Field(client_id,FLOAT8,8,T= ), Field(create_ts,TIMESTAMP,8,T), Field(force_generation_flag,VARCHAR,65535= ,T), Field(instance_id,FLOAT8,8,T), Field(is_jmx_call,VARCHAR,65535,T), Fiel= d(ocode,VARCHAR,65535,T), Field(payload_type,VARCHAR,65535,T), Field(reposit= ory,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T)] =20 =20 B- Then in the class PgResultSet , it calls the method =20 public java.math.@Nullable BigDecimal getBigDecimal(@Positive i= nt columnIndex) throws SQLException { return getBigDecimal(columnIndex, -1); } and then it calls the method=20 @Pure private @Nullable Number getNumeric( int columnIndex, int scale, boolean allowNaN) throws SQLException { byte[] value =3D getRawValue(columnIndex); if (value =3D=3D null) { return null; } if (isBinary(columnIndex)) { int sqlType =3D getSQLType(columnIndex); if (sqlType !=3D Types.NUMERIC && sqlType !=3D Types.DECIMAL) { Object obj =3D internalGetObject(columnIndex, fields[columnIndex - 1= ]); if (obj =3D=3D null) { return null; } if (obj instanceof Long || obj instanceof Integer || obj instanceof B= yte) { BigDecimal res =3D BigDecimal.valueOf(((Number) obj).longValue());= res =3D scaleBigDecimal(res, scale); return res; } return toBigDecimal(trimMoney(String.valueOf(obj)), scale); } else { Number num =3D ByteConverter.numeric(value); if (allowNaN && Double.isNaN(num.doubleValue())) { return Double.NaN; } return num; } } Since the column format is text and not binary it converts the value to BigD= ecimal and give back the value as 40 . 2- When the value in DB is 40 and fetched value is 40.0 (trailing zero) In this case the field metadata is - [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), Field(ocode,VARCHAR,6= 5535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VARCHAR,65535= ,T), Field(sub_repository,VARCHAR,65535,T), Field(force_generation_flag,VARC= HAR,65535,T), Field(is_jmx_call,VARCHAR,65535,T), Field(instance_id,FLOAT8,8= ,B), Field(create_ts,TIMESTAMP,8,B)]=20 Now since the format is Binary Type hence in PgResultSet class and in Nume= ric method condition isBinary(columnIndex) is true. and it returns DOUBLE from there result in 40.0 Now i am not sure for the same table and same column why we have two differe= nt format and this issue is intermittent. Thanks , Rahul=20 --Apple-Mail-4CDD2AD6-1D28-4C7D-ACDC-D9BB315F3E05 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Hello Team,

Hope everyone is doing well here.

I am writing t= his email to understand an issue I'm facing when fetching data in our Java a= pplication. We are using PostgreSQL JDBC Driver version 42.6.0.

Issue:

We are encountering an issue whe= re the double precision data type in PostgreSQL is giving some intermittent r= esults when fetching data. For example, in the database the value is 40, but= sometimes this value is fetched as 40.0. Similarly, for a value of 0.0005, i= t is being fetched as 0.00050, resulting in extra trailing zeros.

Observations :- 

Below ar= e my observations when i was debugging the code of postgres-jdbc driver for d= ouble precision data type.

1- When the value in DB is 40 and fetched v= alue is also 40
     A - In the QueryExecuterImpl cla= ss method  - receiveFields() , we create Fields metadata 
=
     private Field[] receiveFields() throws IOException {=
    pgStream.receiveInteger4(); // MESSAGE SIZE
  &nbs= p; int size =3D pgStream.receiveInteger2();
    Field[] fields =3D= new Field[size];

    if (LOGGER.isLoggable(Level.FINEST)) {=
      LOGGER.log(Level.FINEST, " <=3DBE RowDescription= ({0})", size);
    }

    for (int i =3D 0; i &= lt; fields.length; i++) {
      String columnLabel =3D pgS= tream.receiveCanonicalString();
      int tableOid =3D pgS= tream.receiveInteger4();
      short positionInTable =3D (= short) pgStream.receiveInteger2();
      int typeOid =3D p= gStream.receiveInteger4();
      int typeLength =3D pgStre= am.receiveInteger2();
      int typeModifier =3D pgStream.= receiveInteger4();
      int formatType =3D pgStream.= receiveInteger2();
      fields[i] =3D new Field(columnLab= el,
          typeOid, typeLength, typeModifier,= tableOid, positionInTable);
      fields[i].setForma= t(formatType);

      LOGGER.log(Level.FINEST, "  =      {0}", fields[i]);
    }

  &nbs= p; return fields;
  }

Output of this method is - [Field(= id,FLOAT8,8,T), Field(client_id,FLOAT8,8,T), Field(create_ts,TIMESTAMP,8,T),= Field(force_generation_flag,VARCHAR,65535,T), Field(instance_id,FLOAT8,8,T)= , Field(is_jmx_call,VARCHAR,65535,T), Field(ocode,VARCHAR,65535,T), Field(pa= yload_type,VARCHAR,65535,T), Field(repository,VARCHAR,65535,T), Field(sub_re= pository,VARCHAR,65535,T)]

 

         
     B-= Then in the class PgResultSet , it calls the method  
  &= nbsp;           public java.math.@Nullable BigDecim= al getBigDecimal(@Positive int columnIndex) throws SQLException {
  &= nbsp;                return getBigDe= cimal(columnIndex, -1);
              &= nbsp; }
      and then it calls the method 
 =      @Pure
  private @Nullable Number getNumeric(      int columnIndex, int scale, boolean allowNaN) throws S= QLException {
    byte[] value =3D getRawValue(columnIndex);    if (value =3D=3D null) {
      return null;=
    }

    if (isBinary(columnIndex)) {
&nb= sp;     int sqlType =3D getSQLType(columnIndex);
    &= nbsp; if (sqlType !=3D Types.NUMERIC && sqlType !=3D Types.DECIMAL) {=
        Object obj =3D internalGetObject(columnIndex= , fields[columnIndex - 1]);
        if (obj =3D=3D nu= ll) {
          return null;
    &n= bsp;   }
        if (obj instanceof Long || obj i= nstanceof Integer || obj instanceof Byte) {
        &= nbsp; BigDecimal res =3D BigDecimal.valueOf(((Number) obj).longValue());
=           res =3D scaleBigDecimal(res, scale);
&= nbsp;         return res;
       = }
        return toBigDecimal(trimMoney(String.value= Of(obj)), scale);
      } else {
      &= nbsp; Number num =3D ByteConverter.numeric(value);
      &= nbsp; if (allowNaN && Double.isNaN(num.doubleValue())) {
  &= nbsp;       return Double.NaN;
       = }

        return num;
      }<= br>    }
Since the column format is text and not binary it conv= erts the value to BigDecimal and give back the value as 40 .

2- When t= he value in DB is 40 and fetched value is 40.0 (trailing zero)
  &nb= sp;In this case the field metadata is -

 =  [Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), Field(ocode,VARCH= AR,65535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VARCHAR,6= 5535,T), Field(sub_repository,VARCHAR,65535,T), Field(force_generation_flag,= VARCHAR,65535,T), Field(is_jmx_call,VARCHAR,65535,T), Field(instance_id,FLOA= T8,8,B), Field(create_ts,TIMESTAMP,8,B)] 

Now since the format i= s Binary Type hence in  PgResultSet  class and in Numeric method c= ondition  isBinary(columnIndex) is true.
and it returns  DOUBLE= from there result in 40.0

Now i am not sure for the same table and s= ame column why we have two different format and this issue is intermittent.<= /p>


Thanks ,

=

Rahul 


= --Apple-Mail-4CDD2AD6-1D28-4C7D-ACDC-D9BB315F3E05--