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)
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