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@tao11.riddles.org.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