Re: Java : Postgres double precession issue with different data format text and binary

Hello Chapman,

Thanks for the reply and suggestion.

Below are my observations when i was debugging the code of postgres-jdbc driver 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 

     private Field[] receiveFields() throws IOException {
    pgStream.receiveInteger4(); // MESSAGE SIZE
    int size = pgStream.receiveInteger2();
    Field[] fields = new Field[size];

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

    for (int i = 0; i < fields.length; i++) {
      String columnLabel = pgStream.receiveCanonicalString();
      int tableOid = pgStream.receiveInteger4();
      short positionInTable = (short) pgStream.receiveInteger2();
      int typeOid = pgStream.receiveInteger4();
      int typeLength = pgStream.receiveInteger2();
      int typeModifier = pgStream.receiveInteger4();
      int formatType = pgStream.receiveInteger2();
      fields[i] = 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), Field(ocode,VARCHAR,65535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T)]

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

    if (isBinary(columnIndex)) {
      int sqlType = getSQLType(columnIndex);
      if (sqlType != Types.NUMERIC && sqlType != Types.DECIMAL) {
        Object obj = internalGetObject(columnIndex, fields[columnIndex - 1]);
        if (obj == null) {
          return null;
        }
        if (obj instanceof Long || obj instanceof Integer || obj instanceof Byte) {
          BigDecimal res = BigDecimal.valueOf(((Number) obj).longValue());
          res = scaleBigDecimal(res, scale);
          return res;
        }
        return toBigDecimal(trimMoney(String.valueOf(obj)), scale);
      } else {
        Number num = 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 BigDecimal 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,65535,T), Field(payload_type,VARCHAR,65535,T), Field(repository,VARCHAR,65535,T), Field(sub_repository,VARCHAR,65535,T), Field(force_generation_flag,VARCHAR,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 and 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 different format and this issue is intermittent.

Thanks,

Rahul 

On 19-Mar-2024, at 1:02 AM, Rahul Uniyal <rahul.uniyal00@gmail.com> wrote: