From: | Rahul Uniyal <rahul(dot)uniyal00(at)gmail(dot)com> |
---|---|
To: | Chapman Flack <jcflack(at)acm(dot)org> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Java : Postgres double precession issue with different data format text and binary |
Date: | 2024-03-18 19:52:00 |
Message-ID: | 7628237F-DEB2-414C-84D9-2E0C29FD9173@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-jdbc |
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:
>
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2024-03-18 20:07:35 | Re: Possibility to disable `ALTER SYSTEM` |
Previous Message | Jacob Champion | 2024-03-18 19:34:51 | Re: WIP Incremental JSON Parser |
From | Date | Subject | |
---|---|---|---|
Next Message | Chapman Flack | 2024-03-19 00:26:33 | Re: Java : Postgres double precession issue with different data format text and binary |
Previous Message | Dave Cramer | 2023-11-29 16:12:21 | Re: Driver hangs in connect->MakeSSL.convert->SSLSocketImpl.startHandshake |