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 1rmJiq-007CW7-48 for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Mar 2024 20:36:40 +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 1rmJin-005hmW-KB for pgsql-hackers@arkaria.postgresql.org; Mon, 18 Mar 2024 20:36:38 +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 1rmJ1u-005S8O-N8 for pgsql-hackers@lists.postgresql.org; Mon, 18 Mar 2024 19:52:19 +0000 Received: from mail-pl1-x62a.google.com ([2607:f8b0:4864:20::62a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rmJ1q-005CFq-4e for pgsql-hackers@lists.postgresql.org; Mon, 18 Mar 2024 19:52:17 +0000 Received: by mail-pl1-x62a.google.com with SMTP id d9443c01a7336-1e0000cdf99so16734575ad.0 for ; Mon, 18 Mar 2024 12:52:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710791534; x=1711396334; darn=lists.postgresql.org; h=to:cc:message-id:subject:date:mime-version:from :content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=elqoh/asvzluQraM4e5LcRv71lz0kxfaXC6ngOQ9KIk=; b=eqtaPv5VjjBXnLyNi+o1hEJU1TJTxRuVuQjXIlHespPSamg9k82ZpHh3JfqhHwVqn8 /Q04S8GQo3ZDF2XAQvUkHrDl1J+mpjv70YC+Hb2UDG1kqvXfSyawdShG3+3Y8Otb4Mfu QV33aniCS6FgObl2w93v/1iUq5N0pQV3tN74pw5tTaqfHb1sYCpZeCCPHHUlIhkNkMvK PlTvLkHM/6YkIiLEDIWYahUWpHWUpU1ZOnobnozJxkqQN/dqRkt2QPgAeWqBz60ojQfV xMu8WrCRq2ObZGQOARzPi7rRsoGu9d7HI3J4zqiDLS8FyQxAmJ3iw6g0AZ0k6kvtZoWS f4QA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710791534; x=1711396334; h=to:cc:message-id:subject:date:mime-version:from :content-transfer-encoding:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=elqoh/asvzluQraM4e5LcRv71lz0kxfaXC6ngOQ9KIk=; b=LgF6Xu6hZlW8uqv31csqzts89RpNnyjYh50rq61g5nyrl0SVodhLB43iRGkpu1YI2P YAHCuy37Qs0Q8LLwft0fhM25icu75ApfowYCYZQ65EYh78eCECMalF3P7wM7AhJxWvwx BpJcBYsuhTKVDJkCa3IYo0up/XB3galyfX1D5UUuVEsk3sAo4GvaeSpd+3SzaypzObPK +h1BjVvPWGilBpdtLoDVu2dnNCSY8lcbDh+zXd5TV8blBHouJDeffvGOfXuNm2uRXNgX biQdJCqDHNTFcErWlLer8HdncW3GvI9OkcW7EytGPuixfeOX0sL9esjGZMrjguZgHwYB XQmg== X-Gm-Message-State: AOJu0YwqaFATg6vgEMFWN44UArHEzdvv2/1I7nq3eAYEiAAxdO7WzJa7 WhbeoYJ+nbA4z+LyvKffm99BnPbDfUJyUKG23uIC0oMckhijf4Qy8dP28EnceRs= X-Google-Smtp-Source: AGHT+IF15/7GTNrX3MwG+k24d2raGfWLkCCm39YNApxatDIc9CA0MOvqKZukU8d+WhYPrz8dkIvirw== X-Received: by 2002:a17:902:d4d0:b0:1dd:e115:3368 with SMTP id o16-20020a170902d4d000b001dde1153368mr18043395plg.68.1710791533540; Mon, 18 Mar 2024 12:52:13 -0700 (PDT) Received: from smtpclient.apple ([49.43.162.187]) by smtp.gmail.com with ESMTPSA id p18-20020a170902e75200b001e01bf88881sm3110802plf.253.2024.03.18.12.52.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 18 Mar 2024 12:52:13 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-0268D5BA-19CB-4B25-BB80-D5FCA56464A3 Content-Transfer-Encoding: 7bit From: Rahul Uniyal Mime-Version: 1.0 (1.0) Date: Tue, 19 Mar 2024 01:22:00 +0530 Subject: Re: Java : Postgres double precession issue with different data format text and binary Message-Id: <7628237F-DEB2-414C-84D9-2E0C29FD9173@gmail.com> Cc: pgsql-hackers@lists.postgresql.org To: Chapman Flack X-Mailer: iPhone Mail (21D61) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-0268D5BA-19CB-4B25-BB80-D5FCA56464A3 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello Chapman, Thanks for the reply and suggestion. 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 create= 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 > On 19-Mar-2024, at 1:02=E2=80=AFAM, Rahul Uniyal wrote: >=20 --Apple-Mail-0268D5BA-19CB-4B25-BB80-D5FCA56464A3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Hello=  Chapman,

Thanks for the reply and suggestion.

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 - re= ceiveFields() , we create Fields metadata 

     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.receiveInteg= er2();
      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, tableO= id, 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,FLOAT= 8,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(payload_type,VARCHAR,65535,T), Field(re= pository,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T)]

 <= /p>

  &= nbsp;      
     B- Then in the class PgResultSet , it calls the method&n= bsp; 
              public java.math.@Nullable B= igDecimal getBigDecimal(@Positive int columnIndex) throws SQLException {
=                    return g= etBigDecimal(columnIndex, -1);
                }
      and then it calls the method 
       @Pure
  private @Nullable Number getNumeric(
      int columnIndex, int scale, boolean allowNaN) throws SQ= LException {
    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 T= ypes.DECIMAL) {
        Object obj =3D internalGetObject(columnIndex, fi= elds[columnIndex - 1]);
        if (obj =3D=3D null) {
          return null;
        }
        if (obj instanceof Long || obj instanceof Intege= r || obj instanceof Byte) {
          BigDecimal res =3D BigDecimal.valueOf(((N= umber) 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.doubleV= alue())) {
          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 -

  &= nbsp;[Field(id,FLOAT8,8,B), Field(client_id,FLOAT8,8,B), Field(ocode,VARCHAR= ,65535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VARCHAR,655= 35,T), Field(sub_repository,VARCHAR,65535,T), Field(force_generation_flag,VA= RCHAR,65535,T), Field(is_jmx_call,VARCHAR,65535,T), Field(instance_id,FLOAT8= ,8,B), Field(create_ts,TIMESTAMP,8,B)] 

Now since the format is Binary Type hence in  PgResultSet  class a= nd in Numeric 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 

On 19-Mar-2024, at 1:02=E2=80=AFAM, Rahul Uniyal <rahul.uniyal00@g= mail.com> wrote:

= --Apple-Mail-0268D5BA-19CB-4B25-BB80-D5FCA56464A3--