Re: Problem in

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(dot)groups(at)gmail(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Max Lipsky <maxlipsky(at)gmail(dot)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