agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedProblem in
9+ messages / 6 participants
[nested] [flat]
* Problem in
@ 2019-04-25 13:45 Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Max Lipsky @ 2019-04-25 13:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
@ 2019-04-25 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2019-04-25 16:56 UTC (permalink / raw)
To: Max Lipsky <maxlipsky@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-04-25 21:08 ` Max Lipsky <maxlipsky@gmail.com>
2019-04-25 21:41 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-27 00:34 ` Re: Problem in Andrej <andrej.groups@gmail.com>
2019-05-18 03:13 ` Re: Problem in Javin Paul <savingfunda@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Max Lipsky @ 2019-04-25 21:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
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/ <https://randomascii.wordpress.com/2014/10/09/intel-underestimates-error-bounds-by-1-3-quintillion/;
http://notabs.org/fpuaccuracy/index.htm <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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
@ 2019-04-25 21:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2019-04-25 21:41 UTC (permalink / raw)
To: Max Lipsky <maxlipsky@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
@ 2019-04-27 00:34 ` Andrej <andrej.groups@gmail.com>
2019-04-27 07:32 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Andrej @ 2019-04-27 00:34 UTC (permalink / raw)
To: Max Lipsky <maxlipsky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-27 00:34 ` Re: Problem in Andrej <andrej.groups@gmail.com>
@ 2019-04-27 07:32 ` Max Lipsky <maxlipsky@gmail.com>
2019-05-17 16:09 ` Re: Problem in Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 1 reply; 9+ messages in thread
From: Max Lipsky @ 2019-04-27 07:32 UTC (permalink / raw)
To: Andrej <andrej.groups@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-27 00:34 ` Re: Problem in Andrej <andrej.groups@gmail.com>
2019-04-27 07:32 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
@ 2019-05-17 16:09 ` Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 0 replies; 9+ messages in thread
From: Jean-David Beyer @ 2019-05-17 16:09 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
@ 2019-05-18 03:13 ` Javin Paul <savingfunda@gmail.com>
2019-05-18 03:31 ` Re: Problem in Charles Sheridan <cesheri@swbell.net>
2 siblings, 1 reply; 9+ messages in thread
From: Javin Paul @ 2019-05-18 03:13 UTC (permalink / raw)
To: Max Lipsky <maxlipsky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@lists.postgresql.org
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:
>
> 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
>
>
>
--
Thanks
Javin
http://javarevisited.blogspot.com/
Twitter : https://twitter.com/javinpaul
blog : http://java67.blogspot.com
blog : http://savingsfunda.blogspot.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem in
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Re: Problem in Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Re: Problem in Max Lipsky <maxlipsky@gmail.com>
2019-05-18 03:13 ` Re: Problem in Javin Paul <savingfunda@gmail.com>
@ 2019-05-18 03:31 ` Charles Sheridan <cesheri@swbell.net>
0 siblings, 0 replies; 9+ messages in thread
From: Charles Sheridan @ 2019-05-18 03:31 UTC (permalink / raw)
To: Javin Paul <savingfunda@gmail.com>; Max Lipsky <maxlipsky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@lists.postgresql.org
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
> <mailto: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
>> <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>> Max Lipsky <maxlipsky@gmail.com <mailto: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
>
>
>
> --
> Thanks
> Javin
> http://javarevisited.blogspot.com/
> Twitter : https://twitter.com/javinpaul
> blog : http://java67.blogspot.com
> blog : http://savingsfunda.blogspot.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-05-18 03:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-25 13:45 Problem in Max Lipsky <maxlipsky@gmail.com>
2019-04-25 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-25 21:08 ` Max Lipsky <maxlipsky@gmail.com>
2019-04-25 21:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-04-27 00:34 ` Andrej <andrej.groups@gmail.com>
2019-04-27 07:32 ` Max Lipsky <maxlipsky@gmail.com>
2019-05-17 16:09 ` Jean-David Beyer <jeandavid8@verizon.net>
2019-05-18 03:13 ` Javin Paul <savingfunda@gmail.com>
2019-05-18 03:31 ` Charles Sheridan <cesheri@swbell.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox