Subject: Re: Java : Postgres double precession issue with different data
 format text and binary
To: Rahul Uniyal <rahul.uniyal00@gmail.com>, pgsql-jdbc@lists.postgresql.org
References: <7628237F-DEB2-414C-84D9-2E0C29FD9173@gmail.com>
 <510AA213-BEC4-458D-A3AB-38E43B67E482@gmail.com>
From: Chapman Flack <jcflack@acm.org>
Message-ID: <65F9A90E.5030508@acm.org>
Date: Tue, 19 Mar 2024 11:02:38 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: <510AA213-BEC4-458D-A3AB-38E43B67E482@gmail.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/65F9A90E.5030508%40acm.org>
Precedence: bulk

On 03/18/24 23:05, Rahul Uniyal wrote:
> This is the public method of PgResultSet class . 
> Apart from which format type it is ,
> getBigDecimal public method will get call

That is interesting. That's why it's important to include the basics
when making a report, including which public API method your code
is calling.

As you call getBigDecimal, you always will get a BigDecimal (or an
exception). But, as you have shown, the implementation detail of
the on-the-wire format influences how the BigDecimal is constructed.

When the wire format is text, you get a BigDecimal constructed
directly from the on-the-wire string value.

When the wire format is binary (and the type is not NUMERIC or
DECIMAL), there first is an object constructed by internalGetObject,
and (if that is not an instance of Long, Integer, or Byte), it gets
converted to a BigDecimal this way:

  return toBigDecimal(trimMoney(String.valueOf(obj)), scale);

Assuming the object returned by internalGetObject is a Double, the
reason for the divergent results is the String.valueOf in that
conversion. BigDecimal and Double have different conventions for
how they are rendered as a String; the representation of a Double
will always have a decimal place:

jshell> new BigDecimal("40")
$1 ==> 40
jshell> Double.valueOf("40")
$2 ==> 40.0

If you construct a BigDecimal directly from the Double, you get
the one you expect:

jshell> new BigDecimal($2)
$3 ==> 40

But if you take the String value of the Double first, the extra
decimal place shown in the Double's String value results in a
BigDecimal with scale of 1 rather than 0:

jshell> new BigDecimal(String.valueOf($2))
$4 ==> 40.0

jshell> $3.scale(); $4.scale()
$5 ==> 0
$6 ==> 1

This is very probably worth reporting on pgsql-jdbc.

I also wonder why the trimMoney() is there. The method
seems to exist to deal with a string that could have $
in it, or parentheses instead of a negative sign, but
I am unsure what kind of object could be obtained from
internalGetObject that would require such treatment.

Unrelated to your case, I wonder also about the handling
of NUMERIC or DECIMAL when the value is NaN. The
ByteConverter.numeric method is declared to return Number,
and documented to return either a BigDecimal or Double.NaN.
Likewise, PgResultSet.getNumeric can return Double.NaN
in that case. But getBigDecimal contains an unconditional
cast to BigDecimal, which seems like a ClassCastException
waiting to happen for the NaN case. (getBigDecimal clearly
can't return Double.NaN, but maybe a more-specific exception
would be more helpful than "Double cannot be cast to BigInteger"?)

I've added pgsql-jdbc to the To: for this message, but it will
probably be delayed somewhat in moderation, as I am not
subscribed to that list.

Regards,
-Chap