agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Max Lipsky <maxlipsky@gmail.com>
To: Andrej <andrej.groups@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Problem in
Date: Sat, 27 Apr 2019 10:32:37 +0300
Message-ID: <A811154F-C44C-4066-B952-6A98F2A7C99C@gmail.com> (raw)
In-Reply-To: <CACMx3pMZos=m9rPhwKMANcTqRY5LfBWrpx85G0MG4-595v8O5A@mail.gmail.com>
References: <2164C531-EAA3-4CAF-996F-FEB0E5F73B32@gmail.com>
<26108.1556211406@sss.pgh.pa.us>
<32A34C31-946E-4946-8CD1-DB36F70160ED@gmail.com>
<CACMx3pMZos=m9rPhwKMANcTqRY5LfBWrpx85G0MG4-595v8O5A@mail.gmail.com>
Hello Andrej
That’s true, it's depends of system (CPU and x87 instructions)
But for some reason I thought that the calculations should be symmetrical, but this is not :)
I made small program on c, which calculating sin, cos and (sin^2 + cos^2)
1. sin & cos (0.8538028527708625) > 0.753784785440665960898343200824 0.657121371770975959414329281572
2. sin & cos (0.8538028527708626) > 0.753784785440666071920645663340 0.657121371770975848392026819056
3. sin & cos (0.8538028527708627) > 0.753784785440666071920645663340 0.657121371770975848392026819056
4. sin & cos (0.8538028527708628) > 0.753784785440666071920645663340 0.657121371770975737369724356540
5. sin & cos (0.8538028527708629) > 0.753784785440666182942948125856 0.657121371770975737369724356540
6. sin & cos (0.8538028527708630) > 0.753784785440666293965250588371 0.657121371770975626347421894025
2, 3, 4 — same result for SIN
2, 3 and 4,5 — same result for COS
1. sin^2 + cos^2 (0.8538028527708625) > 1.000000000000000000000000000000
2. sin^2 + cos^2 (0.8538028527708626) > 1.000000000000000000000000000000
3. sin^2 + cos^2 (0.8538028527708627) > 1.000000000000000000000000000000
4. sin^2 + cos^2 (0.8538028527708628) > 0.999999999999999888977697537484
5. sin^2 + cos^2 (0.8538028527708629) > 1.000000000000000000000000000000
6. sin^2 + cos^2 (0.8538028527708630) > 1.000000000000000000000000000000
Also, if you change sin to (1 - cos^2) in sql query — it will be works good.
Error comes from FSIN/FCOS instruction, but as Tom said — it’s OK (within acceptable limits).
It was just a little unexpected for me :)
P.S. OK for HTML
> On 27 Apr 2019, at 03:34, Andrej <andrej.groups@gmail.com> wrote:
>
> Max, just try
>
> SELECT 1 - ( cos(radians(48.9193)) * cos(radians(48.9193)) *
> cos(radians(2.5431) - radians(2.5431)) + sin(radians(48.9193)) *
> sin(radians(48.9193)) ) as result;
>
> You may find that enlightening. I did play with this for a while
> yesterday (and asked people on IRC), and it seems to heavily depend
> the on local systems set-up.
> Seems that e.g. MacOS mojave & postgres9.6 from brew work as you
> expect; someone confirmed postgres on Suse to do the right thing.
> 9.6.15 on Ubuntu 16.04
> gives a remainder that would then make the acos fail ...
>
> On Fri, 26 Apr 2019 at 19:24, Max Lipsky <maxlipsky@gmail.com> wrote:
>>
>> Hello Tom!
>>
>> I think is too much for roundoff error
>>
>> Found funny post about it:
>> https://randomascii.wordpress.com/2014/10/09/intel-underestimates-error-bounds-by-1-3-quintillion/
>> http://notabs.org/fpuaccuracy/index.htm
>>
>>
>>
>> On 25 Apr 2019, at 19:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Max Lipsky <maxlipsky@gmail.com> writes:
>>
>> SELECT acos(
>> cos(radians(48.9193))
>> * cos(radians(48.9193))
>> * cos(radians(2.5431) - radians(2.5431))
>> + sin(radians(48.9193))
>> * sin(radians(48.9193))
>> ) as result;
>>
>>
>> This returned [22003] ERROR: input is out of range
>>
>>
>> Not too surprising, because (at least on my machine)
>>
>> regression=# select cos(radians(48.9193))
>> regression-# * cos(radians(48.9193)) + sin(radians(48.9193))
>> regression-# * sin(radians(48.9193));
>> ?column?
>> --------------------
>> 1.0000000000000002
>> (1 row)
>>
>> Problems of this sort are inevitable when working with finite-precision
>> math.
>>
>> https://en.wikipedia.org/wiki/Round-off_error
>>
>> regards, tom lane
>>
>>
>
>
> --
> Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
>
> http://www.georgedillon.com/web/html_email_is_evil.shtml
> http://www.catb.org/jargon/html/email-style.html
view thread (9+ messages) latest in thread
Message-ID: <A811154F-C44C-4066-B952-6A98F2A7C99C@gmail.com>
Permalink: ../A811154F-C44C-4066-B952-6A98F2A7C99C@gmail.com/
Also on: postgresql.org/message-id/A811154F-C44C-4066-B952-6A98F2A7C99C@gmail.com
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-sql@postgresql.org
Cc: maxlipsky@gmail.com, andrej.groups@gmail.com, tgl@sss.pgh.pa.us, pgsql-sql@lists.postgresql.org
Subject: Re: Problem in
In-Reply-To: <A811154F-C44C-4066-B952-6A98F2A7C99C@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox