Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMg8j-0000Ev-Hr for pgsql-bugs@arkaria.postgresql.org; Mon, 21 Oct 2019 22:27:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMg8i-0003HG-Ah for pgsql-bugs@arkaria.postgresql.org; Mon, 21 Oct 2019 22:27:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMg8i-0003H7-3Q for pgsql-bugs@lists.postgresql.org; Mon, 21 Oct 2019 22:27:00 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMg8f-0005fI-NN for pgsql-bugs@lists.postgresql.org; Mon, 21 Oct 2019 22:26:59 +0000 Received: from [192.168.127.1] (port=52859 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1iMg8c-000FM2-Ny; Mon, 21 Oct 2019 22:26:54 +0000 Received: from localhost ([127.0.0.1]:24167 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1iMg8b-0004JU-RI; Mon, 21 Oct 2019 22:26:53 +0000 From: Andrew Gierth To: Tom Lane Cc: Gilleain Torrance , "pgsql-bugs\@lists.postgresql.org" Subject: Re: Incorrect rounding of double values at max precision In-Reply-To: <5045.1571686957@sss.pgh.pa.us> (Tom Lane's message of "Mon, 21 Oct 2019 15:42:37 -0400") Message-ID: <875zkhhjs0.fsf@news-spur.riddles.org.uk> References: <5045.1571686957@sss.pgh.pa.us> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.3 (berkeley-unix) Date: Mon, 21 Oct 2019 23:26:53 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Tom" == Tom Lane writes: > Gilleain Torrance 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. Tom> I think this is behaving as expected. float8-to-numeric conversion Tom> rounds the float8 to 15 (DBL_DIG) decimal places, since that's as Tom> much precision as you're guaranteed to have. Yes. This came up for discussion in the Ryu patch, but did not get much input; I think some sort of case could be made for making the casts exact, but the cast can't look at a config GUC without losing its immutability, and changing the value could have an effect on functional indexes. So I ended up not touching that at all. Tom> The other thing we could conceivably do is ask sprintf for more Tom> digits. But since those extra digit(s) aren't fully precise, I'm Tom> afraid that would likewise introduce as many oddities as it fixes. Tom> Still, it's somewhat interesting to wonder whether applying the Tom> Ryu algorithm would produce better or worse results on average. Hmm. 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. Perhaps it would make more sense for the float8 to numeric cast to look at the requested typmod and use that for the conversion? That way we could make casts like fltval::numeric(20,2) or whatever produce the correct result without any double-rounding issues. But the nature of floating point means that this would still throw out occasionally unexpected values (e.g. the 502.15::float8/10 example would still give 50.21 for a 2-digit result rather than 50.22). (502.15::float8 is exactly 502.14999999999997726263245567679405212402343750) I also did consider adding functions to convert a float8 value to the _exact_ numeric that it represents. This is easy enough to write using numeric arithmetic (I have SQL versions that I used extensively when testing the Ryu code) but the performance isn't exceptionally good. Might be good enough for many applications, though. -- Andrew (irc:RhodiumToad)