pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Gilleain Torrance <Gilleain.Torrance@alfasystems.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs\@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Incorrect rounding of double values at max precision
Date: Tue, 22 Oct 2019 19:54:41 +0100
Message-ID: <87wocwfxip.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <DB3D1D143DDA534BBB39DE713E92343701CBA89AB6@GBEDBP01.chp.co.uk>
References: <DB3D1D143DDA534BBB39DE713E92343701CBA89718@GBEDBP01.chp.co.uk>
	<5045.1571686957@sss.pgh.pa.us>
	<DB3D1D143DDA534BBB39DE713E92343701CBA89AB6@GBEDBP01.chp.co.uk>

>>>>> "Gilleain" == Gilleain Torrance <Gilleain.Torrance@alfasystems.com> 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)





view thread (11+ messages)  latest in thread

Message-ID: <87wocwfxip.fsf@news-spur.riddles.org.uk>
Permalink:  ../87wocwfxip.fsf@news-spur.riddles.org.uk/
Also on:    postgresql.org/message-id/87wocwfxip.fsf@news-spur.riddles.org.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: andrew@tao11.riddles.org.uk, Gilleain.Torrance@alfasystems.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
  Subject: Re: Incorrect rounding of double values at max precision
  In-Reply-To: <87wocwfxip.fsf@news-spur.riddles.org.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox