Re: Problem in - Mailing list pgsql-sql

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