Re: Incorrect rounding of double values at max precision

Hi,

Re-found this thread due to
https://postgr.es/m/CH2PR19MB3798B24BCC34D3F9949F629C83000%40CH2PR19MB3798.namprd19.prod.outlook.com

On 2019-10-21 22:41:11 -0400, Tom Lane wrote:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> > The Ryu output values will still throw out edge cases similar to the
> > above; for example, 502.15::float8 / 10 = 50.214999999999996 whereas
> > 502.15::numeric / 10 = 50.215, so rounding the result of that to 2
> > digits will give a different result.
>
> Yeah. Worse, casting that to numeric currently gives the "correct"
> result:
>
> regression=# select (502.15::float8 / 10)::numeric;
> numeric
> ---------
> 50.215
> (1 row)
>
> while if we changed float8_numeric to apply Ryu, the result would be
> 50.214999999999996. So that's not great. But there are other cases
> where the result would be better than before, such as the OP's example
> of 42258656681.38498::float8. I'd like to get my hands around how
> many "better" and "worse" cases there would be, but I'm not sure how
> to attack that question.

The above bug report has a, in my opinion, pretty egregious cases of
wrongness. From the bug:

postgres[1296822][1]=# select '1234567'::float4::numeric;
┌─────────┐
│ numeric │
├─────────┤
│ 1234570 │
└─────────┘
(1 row)

It seems crazy that we throw away integer precision in the range it's
guaranteed to be accurate (that's what, −16777216 to 16777216 for
float4, more than a magnitude larger than this value). It does feel
different to me that we're throwing away precision that we could know is
not just the result of floating point imprecision.

I can't really see outputting a bunch of "fake precision" post decimal
points digits being comparably bad?

I didn't find much discussion about how we could deal with pg_upgrade
issues if we were to change the behaviour. Detect indexes involving such
casts, and mark them as invalid?

Greetings,

Andres Freund