RE: Incorrect rounding of double values at max precision
От | Gilleain Torrance |
---|---|
Тема | RE: Incorrect rounding of double values at max precision |
Дата | |
Msg-id | DB3D1D143DDA534BBB39DE713E92343701CBA89AB6@GBEDBP01.chp.co.uk обсуждение исходный текст |
Ответ на | Re: Incorrect rounding of double values at max precision (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Incorrect rounding of double values at max precision
(Andrew Gierth <andrew@tao11.riddles.org.uk>)
|
Список | pgsql-bugs |
> I think this is behaving as expected. float8-to-numeric conversion rounds the float8 to 15 (DBL_DIG) decimal places, since that's as much precision as you're guaranteed to have. Perhaps, but the original path we took to find this was through JDBC, specifically the one from https://jdbc.postgresql.org/.Tracking the double (42258656681.38498) through the driver shows that it is converted from javadouble to float8 bytes, and is then stored as 42258656681.39. Which is definitely not expected, even if it can be explainedby double rounding. The half-up/half-down of the rounding may be a side issue, although also important. A simple test like this will show the behaviour we see: Connection conn = getConnection(); // some db connection PreparedStatement pstmt = conn.prepareStatement("INSERT into mytable VALUES (?, ?)"); double x = 4.225865668138498E10; int id = 123; pstmt.setObject(1, id); pstmt.setDouble(2, x); pstmt.execute(); where mytable just has a decimal(13, 2) column and an integer id. When selected afterwards, we get the 42258656681.39 valueinstead of an expected value of 42258656681.38. thanks gilleain -----Original Message----- From: Tom Lane <tgl@sss.pgh.pa.us> Sent: 21 October 2019 19:43 To: Gilleain Torrance <Gilleain.Torrance@alfasystems.com> Cc: pgsql-bugs@lists.postgresql.org; Andrew Gierth <andrew@tao11.riddles.org.uk> Subject: Re: Incorrect rounding of double values at max precision Gilleain Torrance <Gilleain.Torrance@alfasystems.com> writes: > When storing a double in Postgres, it looks like under specific circumstances it can get rounded incorrectly: > select round(cast(float8 '42258656681.38498' as numeric), 2), round(numeric '42258656681.38498', 2); > which returns either 42258656681.38 or 42258656681.39 depending on whether it is float8 or not. I think this is behaving as expected. float8-to-numeric conversion rounds the float8 to 15 (DBL_DIG) decimal places, since that's as much precision as you're guaranteed to have. So what comes out of the cast is regression=# select cast(float8 '42258656681.38498' as numeric); numeric ----------------- 42258656681.385 (1 row) and then that rounds up to 42258656681.39. In the other case you have an exact numeric value of 42258656681.38498, so it's unsurprisingly rounded to 42258656681.38. You could quibble about whether numeric round() ought to apply round-up or round-to-nearest-even when dealing with exact halfway cases. If it did the latter, this particular case would match up, but other cases would not, so I don't think it's a helpful proposal for this issue. The other thing we could conceivably do is ask sprintf for more digits. But since those extra digit(s) aren't fully precise, I'm afraid that would likewise introduce as many oddities as it fixes. Still, it's somewhat interesting to wonder whether applying the Ryu algorithm would produce better or worse results on average. regards, tom lane
В списке pgsql-bugs по дате отправления:
Следующее
От: Bruno DUPUISДата:
Сообщение: RE: PGDG strech : postgresql-11-postgis-2.5 won't install beacause ofmissing dependency