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(dot)math(dot)(at)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(dot)uniyal00(at)gmail(dot)com> wrote:
>