Re: Problem in
Max Lipsky <[email protected]> Sat, 27 Apr 2019 10:32:37 +0300
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
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 <[email protected]> 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 <[email protected]> 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 <[email protected]> wrote: >> >> Max Lipsky <[email protected]> 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