On Fri, Oct 16, 2020 at 6:32 PM David G. Johnston <[hidden email]> wrote:
On Friday, October 16, 2020, Thangavel, Parameswaran <[hidden email]> wrote:
Any thoughts on why this behavior, Is this expected?
Not immediately, can you demonstrate the problem without using JDBC? The JDBC project has its own issues setup on GitHub.
So, confirming the following query result on head.
select '1234567'::float4::numeric; -- 1234570
Not tested on 9.4.1 (or the last current 9.4.26 release.
The open question is whether the change in result is a bug. I'll agree it is not expected; but then again floating point and numeric compatibility is not something that I am expecting to make sense at a high-level; the devil is in the details.
On 2020-10-20 09:50:46 -0700, David G. Johnston wrote:
> So, confirming the following query result on head.
> select '1234567'::float4::numeric; -- 1234570
The reason this happens is that the float->numeric casts are
implemented by converting the float value to string using
FLT_DIG/DBL_DIG, and then reading that back as a string. Unfortunately
that's pretty bogus - FLT_DIG / DBL_DIG are extremely pessimistic; as it
turns out rounding a few digits beyond the decimal point isn't the same
as before the decimal point.
Unfortunately this can't easily be changed, as any such change would
e.g. break indexes that include float->numeric casts. Including across
major versions, due to pg_upgrade.
For text this is not a problem anymore since v12 (with default settings,
i.e. extra_float_digits > 0), because we now use ryu for those
conversions. But we can't depend extra_float_digits in the case of
float->numeric casts, because it's an immutable function :/