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 1iMzIw-00075P-GX for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Oct 2019 18:54:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMzIv-0007Ts-1F for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Oct 2019 18:54:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMzIu-0007TB-Kx for pgsql-bugs@lists.postgresql.org; Tue, 22 Oct 2019 18:54:48 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iMzIr-00017M-Sa for pgsql-bugs@lists.postgresql.org; Tue, 22 Oct 2019 18:54:47 +0000 Received: from [192.168.127.1] (port=29966 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1iMzIn-000I1J-S5; Tue, 22 Oct 2019 18:54:41 +0000 Received: from localhost ([127.0.0.1]:58504 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1iMzIn-00050p-Nd; Tue, 22 Oct 2019 18:54:41 +0000 From: Andrew Gierth To: Gilleain Torrance Cc: Tom Lane , "pgsql-bugs\@lists.postgresql.org" Subject: Re: Incorrect rounding of double values at max precision In-Reply-To: (Gilleain Torrance's message of "Tue, 22 Oct 2019 09:25:37 +0000") Message-ID: <87wocwfxip.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: Tue, 22 Oct 2019 19:54:41 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Gilleain" == Gilleain Torrance writes: Gilleain> A simple test like this will show the behaviour we see: Gilleain> Connection conn = getConnection(); // some db connection Gilleain> PreparedStatement pstmt = conn.prepareStatement("INSERT into mytable VALUES (?, ?)"); Gilleain> double x = 4.225865668138498E10; Gilleain> int id = 123; Gilleain> pstmt.setObject(1, id); Gilleain> pstmt.setDouble(2, x); Gilleain> pstmt.execute(); Gilleain> where mytable just has a decimal(13, 2) column and an integer Gilleain> id. I do not see any way to make that code work as you apparently expect in all cases. Here is a simple counterexample: imagine setting x to 502.215 instead. Currently, that will insert a value of 502.22 into the table, as you would expect. If we fixed your example above by doing the conversion with maximum precision, then 502.215 would instead be inserted as 502.21, because the actual float value that represents 502.215 is equal to 502.21499999999997498889570124447345733642578125. Or for another example, 4.225865668139500E10 currently inserts as 42258656681.40 in your code, but using maximum precision would cause it to insert as 42258656681.39 instead (the true value of a float8 '4.225865668139500E10' is 42258656681.39499664306640625.) So while what we currently do is arguably wrong since it's doing two rounding steps, fixing it wouldn't actually help your problem but would just move the failure cases to different values. -- Andrew (irc:RhodiumToad)