Hi everyone!
I found an incomprehensible behavior of some functions in PostgreSQL 9.5.12 (for 10.6 working good). For example: SELECT acos( This returned [22003] ERROR: input is out of range But this one working good: SELECT acos( The difference is 48.9193 => 48.9192 I did research a bit and found what this query SELECT cos(0.8538028527708621) * cos(0.8538028527708621) + sin(0.8538028527708621) * sin(0.8538028527708621) as s1; Returned 1.0000000000000002 SELECT cos(0.8538028527708621) * cos(0.8538028527708621) as s1;  0.43180849723816966 Looks like cos() have scale = 17 (sometimes), but sin() have scale 16 P.S. I saw it only in the IntelliJ IDEA console. In the native console these values will be rounded: SELECT cos(0.8538028527708621) * cos(0.8538028527708621) as s1; s1  0.43180849723817 * * * Best Regards, Max Lipsky 
Max Lipsky <[hidden email]> 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 finiteprecision math. https://en.wikipedia.org/wiki/Roundoff_error regards, tom lane 
Hello Tom!
I think is too much for roundoff error Found funny post about it:

Max Lipsky <[hidden email]> writes:
> I think is too much for roundoff error The error in this example is one unitinthelastplace of a standard IEEE double, if I did the math correctly, so it couldn't be any smaller without being exact. regards, tom lane 
In reply to this post by Max Lipsky
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 setup. 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 <[hidden email]> wrote: > > Hello Tom! > > I think is too much for roundoff error > > Found funny post about it: > https://randomascii.wordpress.com/2014/10/09/intelunderestimateserrorboundsby13quintillion/ > http://notabs.org/fpuaccuracy/index.htm > > > > On 25 Apr 2019, at 19:56, Tom Lane <[hidden email]> wrote: > > Max Lipsky <[hidden email]> 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 finiteprecision > math. > > https://en.wikipedia.org/wiki/Roundoff_error > > regards, tom lane > >  Please don't top post, and don't use HTML eMail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml http://www.catb.org/jargon/html/emailstyle.html 
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 <[hidden email]> 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 setup. > 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 <[hidden email]> wrote: >> >> Hello Tom! >> >> I think is too much for roundoff error >> >> Found funny post about it: >> https://randomascii.wordpress.com/2014/10/09/intelunderestimateserrorboundsby13quintillion/ >> http://notabs.org/fpuaccuracy/index.htm >> >> >> >> On 25 Apr 2019, at 19:56, Tom Lane <[hidden email]> wrote: >> >> Max Lipsky <[hidden email]> 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 finiteprecision >> math. >> >> https://en.wikipedia.org/wiki/Roundoff_error >> >> regards, tom lane >> >> > > >  > Please don't top post, and don't use HTML eMail :} Make your quotes concise. > > http://www.georgedillon.com/web/html_email_is_evil.shtml > http://www.catb.org/jargon/html/emailstyle.html 
On 4/27/19 3:32 AM, Max Lipsky wrote:
> Error comes from FSIN/FCOS instruction, but as Tom said — it’s OK (within acceptable limits). > It was just a little unexpected for me :) Back in my programming days, a friend was having trouble with a large (in memory) table of floating point numbers on an IBM/360. She was trying to do a statistical analysis of those numbers and getting strange results. I had her add up all the numbers and print the sum. I then had her sort the same numbers, add them up, and print the sum. The numbers were originally in "random" order, but they were summed from large to small after the sort. The sorted numbers summed to less than the random ones. She finally decided that the computer hardware was bad. In a sense, it was, though it worked within specifications. The system/360 was a hexadecimal machine; i.e., the floating point fraction could have up to 3 leading zeros in the fraction part of a floating point number. And the 32bit word size was even worse than the3 36bit word size of the IBM/7094 binary machines she was used to. So if you add up a bunch of large floating point numbers with small ones following, the small ones pretty much do not count (roundoff). Had she shorted them small to large, she would have gotten the biggest answer. The same problems were affecting her statistical analysis. Basically, the System/360 machines were disasters as far as mathematical calculations were concerned. Shorter word size, and inferior floating point representation. This at a time when Burroughs machines were 48 bits and Control Data machines had 60bit word size.  .~. JeanDavid Beyer /V\ PGPKey:166D840A 0C610C8B /( )\ Shrewsbury, New Jersey ^^^^ 11:55:01 up 1 day, 14:06, 2 users, load average: 4.92, 4.66, 4.69 
In reply to this post by Max Lipsky
Hello All, I want to unsubscribe from this group, can anyone please guide? Sorry for the trouble. Regards Javin On Fri, Apr 26, 2019 at 3:24 PM Max Lipsky <[hidden email]> wrote:
blog : http://java67.blogspot.com 
Hi, me too  have tried proscribed
steps before, to no avail.
Regards, Charles
On 5/17/19 22:13, Javin Paul wrote:

On Fri, 17 May 2019 22:31:33 0500, Charles Sheridan
<[hidden email]> wrote abour Re: Problem in: > Hi, me too  have tried proscribed steps before, to no avail. > Regards, Charles > > On 5/17/19 22:13, Javin Paul wrote: >> Hello All, >> >> I want to unsubscribe from this group, can anyone please guide? Sorry >> for the trouble. In your mail headers you will see a line that looks like this: ListUnsubscribe: <https://lists.postgresql.org/unsub/54/4e0987be49724bca1cd98366c565fdf40f01f76a46b3c7b2a0d4897d7c0d4bda/> Follow the hypertext link and you can detach from the list.  Regards, Dave [RLU #314465] ************************************ [hidden email] (David W Noon) ************************************ 
On Sat, 18 May 2019 16:33:13 +0100, David W Noon
<[hidden email]> wrote abour Unsubscribing from list (was: Problem in: [snip] >>> I want to unsubscribe from this group, can anyone please guide? Sorry >>> for the trouble. > > In your mail headers you will see a line that looks like this: > > ListUnsubscribe: > <https://lists.postgresql.org/unsub/54/4e09> > > Follow the hypertext link and you can detach from the list. Note that you are required to use the header line in *your* messages, not the ones in my messages. Somebody has tried to unsubscribe me from the list.  Regards, Dave [RLU #314465] ************************************ [hidden email] (David W Noon) ************************************ 
Free forum by Nabble  Edit this page 