Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.8\))
Subject: Re: Problem in
From: Max Lipsky <maxlipsky@gmail.com>
In-Reply-To: 
 <CACMx3pMZos=m9rPhwKMANcTqRY5LfBWrpx85G0MG4-595v8O5A@mail.gmail.com>
Date: Sat, 27 Apr 2019 10:32:37 +0300
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-sql@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <A811154F-C44C-4066-B952-6A98F2A7C99C@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>
To: Andrej <andrej.groups@gmail.com>
Precedence: bulk

Hello Andrej

That=E2=80=99s 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 =E2=80=94 same result for SIN
2, 3 and 4,5 =E2=80=94 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 =E2=80=94 it will be =
works good.

Error comes from FSIN/FCOS instruction, but as Tom said =E2=80=94 it=E2=80=
=99s 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:
>=20
> Max, just try
>=20
> 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;
>=20
> 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 ...
>=20
> On Fri, 26 Apr 2019 at 19:24, Max Lipsky <maxlipsky@gmail.com> wrote:
>>=20
>> Hello Tom!
>>=20
>> I think is too much for roundoff error
>>=20
>> Found funny post about it:
>> =
https://randomascii.wordpress.com/2014/10/09/intel-underestimates-error-bo=
unds-by-1-3-quintillion/
>> http://notabs.org/fpuaccuracy/index.htm
>>=20
>>=20
>>=20
>> On 25 Apr 2019, at 19:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>=20
>> Max Lipsky <maxlipsky@gmail.com> writes:
>>=20
>> 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;
>>=20
>>=20
>> This returned [22003] ERROR: input is out of range
>>=20
>>=20
>> Not too surprising, because (at least on my machine)
>>=20
>> regression=3D# select cos(radians(48.9193))
>> regression-#            * cos(radians(48.9193)) +  =
sin(radians(48.9193))
>> regression-#              * sin(radians(48.9193));
>>     ?column?
>> --------------------
>> 1.0000000000000002
>> (1 row)
>>=20
>> Problems of this sort are inevitable when working with =
finite-precision
>> math.
>>=20
>> https://en.wikipedia.org/wiki/Round-off_error
>>=20
>> regards, tom lane
>>=20
>>=20
>=20
>=20
> --=20
> Please don't top post, and don't use HTML e-Mail :}  Make your quotes =
concise.
>=20
> http://www.georgedillon.com/web/html_email_is_evil.shtml
> http://www.catb.org/jargon/html/email-style.html