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 1iMk6w-0008R4-Iy for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Oct 2019 02:41:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMk6s-000866-Qr for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Oct 2019 02:41:22 +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 1iMk6s-00085y-JJ for pgsql-bugs@lists.postgresql.org; Tue, 22 Oct 2019 02:41:22 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMk6n-0004Xe-KF for pgsql-bugs@lists.postgresql.org; Tue, 22 Oct 2019 02:41:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x9M2fB9h026444; Mon, 21 Oct 2019 22:41:12 -0400 From: Tom Lane To: Andrew Gierth cc: Gilleain Torrance , "pgsql-bugs\@lists.postgresql.org" Subject: Re: Incorrect rounding of double values at max precision In-reply-to: <875zkhhjs0.fsf@news-spur.riddles.org.uk> References: <5045.1571686957@sss.pgh.pa.us> <875zkhhjs0.fsf@news-spur.riddles.org.uk> Comments: In-reply-to Andrew Gierth message dated "Mon, 21 Oct 2019 23:26:53 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <26442.1571712071.1@sss.pgh.pa.us> Date: Mon, 21 Oct 2019 22:41:11 -0400 Message-ID: <26443.1571712071@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Andrew Gierth writes: > "Tom" == Tom Lane writes: > 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. 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. > Perhaps it would make more sense for the float8 to numeric cast to look > at the requested typmod and use that for the conversion? As things stand right now, float8_numeric has no idea what the target typmod is; any typmod-driven rounding happens in a separate function call afterwards. I don't recall whether the parser's casting infrastructure could support merging those steps, and I'm not sure it matters in most cases. Commonly, we don't have a target typmod. (Still, if we do, having two separate rounding steps isn't nice.) regards, tom lane