Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem in

Problem in

From
Max Lipsky
Date:
Hi everyone!

I found an incomprehensible behavior of some functions in PostgreSQL 9.5.12 (for 10.6 working good). 

For example:

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

But this one working good:

SELECT acos(
           cos(radians(48.9192))
           * cos(radians(48.9192))
           * cos(radians(2.5431) - radians(2.5431))
           + sin(radians(48.9192))
             * sin(radians(48.9192))
       ) as result;

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
SELECT sin(0.8538028527708621) * sin(0.8538028527708621) as s1; -- 0.5681915027618305
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

Re: Problem in

From
Tom Lane
Date:
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

Re: Problem in

From
Max Lipsky
Date:
Hello Tom!

I think is too much for roundoff error

Found funny post about it:



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

Re: Problem in

From
Tom Lane
Date:
Max Lipsky <maxlipsky@gmail.com> writes:
> I think is too much for roundoff error

The error in this example is one unit-in-the-last-place of a standard IEEE
double, if I did the math correctly, so it couldn't be any smaller without
being exact.

            regards, tom lane

Re: Problem in

From
Andrej
Date:
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

Re: Problem in

From
Max Lipsky
Date:
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

Re: Problem in

From
Jean-David Beyer
Date:
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 32-bit word size was even worse than the3
36-bit 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 (round-off). 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 60-bit word size.

-- 
  .~.  Jean-David Beyer
  /V\  PGP-Key:166D840A 0C610C8B
 /( )\ Shrewsbury, New Jersey
 ^^-^^ 11:55:01 up 1 day, 14:06, 2 users, load average: 4.92, 4.66, 4.69

Re: Problem in

From
Javin Paul
Date:
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 <maxlipsky@gmail.com> wrote:
Hello Tom!

I think is too much for roundoff error

Found funny post about it:



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



--

Re: Problem in

From
Charles Sheridan
Date:
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. 

Regards
Javin

On Fri, Apr 26, 2019 at 3:24 PM Max Lipsky <maxlipsky@gmail.com> wrote:
Hello Tom!

I think is too much for roundoff error

Found funny post about it:



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



--


Unsubscribing from list (was: Problem in

From
David W Noon
Date:
On Fri, 17 May 2019 22:31:33 -0500, Charles Sheridan
<cesheri@swbell.net> 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:

List-Unsubscribe:
<https://lists.postgresql.org/unsub/54/4e0987be49724bca1cd98366c565fdf40f01f76a46b3c7b2a0d4897d7c0d4bda/>

Follow the hypertext link and you can detach from the list.
-- 
Regards,

Dave  [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david.w.noon@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Re: Unsubscribing from list (was: Problem in

From
David W Noon
Date:
On Sat, 18 May 2019 16:33:13 +0100, David W Noon
<david.w.noon@googlemail.com> 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:
> 
> List-Unsubscribe:
> <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]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david.w.noon@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*