Problem in

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem in

Max Lipsky
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

Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Tom Lane-2
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 finite-precision
math.

https://en.wikipedia.org/wiki/Round-off_error

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Max Lipsky
Hello Tom!

I think is too much for roundoff error

Found funny post about it:



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 finite-precision
math.

https://en.wikipedia.org/wiki/Round-off_error

regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Tom Lane-2
Max Lipsky <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Andrej Ricnik-Bay
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 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 <[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/intel-underestimates-error-bounds-by-1-3-quintillion/
> 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 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


Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Max Lipsky
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 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 <[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/intel-underestimates-error-bounds-by-1-3-quintillion/
>> 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 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



Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Jean-David Beyer-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Javin Paul
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:
Hello Tom!

I think is too much for roundoff error

Found funny post about it:



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 finite-precision
math.

https://en.wikipedia.org/wiki/Round-off_error

regards, tom lane



--
Reply | Threaded
Open this post in threaded view
|

Re: Problem in

Charles Sheridan-2
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 <[hidden email]> 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 <[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 finite-precision
math.

https://en.wikipedia.org/wiki/Round-off_error

regards, tom lane



--


Reply | Threaded
Open this post in threaded view
|

Unsubscribing from list (was: Problem in

David W Noon-3
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:

List-Unsubscribe:
<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)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*


Reply | Threaded
Open this post in threaded view
|

Re: Unsubscribing from list (was: Problem in

David W Noon-3
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:
>
> 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]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
[hidden email] (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*