Thread: Unexpected result using floor() function

Unexpected result using floor() function

From
"Frank Millman"
Date:
Hi all
 
I am running PostgreSQL 9.4.4 on Fedora 22.
 
SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
 
SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
 
Please can someone explain the anomaly.
 
Thanks
 
Frank Millman
 

Re: Unexpected result using floor() function

From
Pujol Mathieu
Date:
Hi
I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit" on Windows 8
I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as expected

Mathieu

Le 14/03/2016 15:11, Frank Millman a écrit :
Hi all
 
I am running PostgreSQL 9.4.4 on Fedora 22.
 
SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
 
SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
 
Please can someone explain the anomaly.
 
Thanks
 
Frank Millman
 

-- 
Mathieu PUJOL
Ingénieur Réalité Virtuelle
Tel : 05.81.33.13.36
REAL FUSIO - 3D Computer Graphics
9, rue Paul Mesple - 31100 TOULOUSE - FRANCE
mathieu.pujol@realfusio.com - http://www.realfusio.com

Re: Unexpected result using floor() function

From
Vick Khera
Date:
100 is an integer
 power(10,2) is a double precision.

Try this one:

SELECT floor(4.725 * 100::double precision + 0.5);




On Mon, Mar 14, 2016 at 10:11 AM, Frank Millman <frank@chagford.com> wrote:
Hi all
 
I am running PostgreSQL 9.4.4 on Fedora 22.
 
SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
 
SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
 
Please can someone explain the anomaly.
 
Thanks
 
Frank Millman
 

Re: Unexpected result using floor() function

From
Merlin Moncure
Date:
On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
<mathieu.pujol@realfusio.com> wrote:
> Hi
> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build
> 1600, 64-bit" on Windows 8
> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as
> expected

I don't think this is a bug -- just peculiarities of floating point math.

merlin


Re: Unexpected result using floor() function

From
Adrian Klaver
Date:
On 03/14/2016 07:21 AM, Pujol Mathieu wrote:
> Hi
> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++
> build 1600, 64-bit" on Windows 8
> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5)
> work as expected

Seems to be related to the two forms of power():

http://www.postgresql.org/docs/9.4/interactive/functions-math.html

Function     Return Type
power(a dp, b dp)     dp
power(a numeric, b numeric)     numeric

So just doing:

test=> select floor(4.725 * power(10, 2.0) + 0.5);
  floor
-------
    473
(1 row)


works.

>
> Mathieu
>
> Le 14/03/2016 15:11, Frank Millman a écrit :
>> Hi all
>> I am running PostgreSQL 9.4.4 on Fedora 22.
>> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find
>> surprising.
>> Please can someone explain the anomaly.
>> Thanks
>> Frank Millman
>
> --
> Mathieu PUJOL
> Ingénieur Réalité Virtuelle
> Tel : 05.81.33.13.36
> REAL FUSIO - 3D Computer Graphics
> 9, rue Paul Mesple - 31100 TOULOUSE - FRANCE
> mathieu.pujol@realfusio.com  -http://www.realfusio.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Unexpected result using floor() function

From
Merlin Moncure
Date:
On Mon, Mar 14, 2016 at 9:29 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
> <mathieu.pujol@realfusio.com> wrote:
>> Hi
>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build
>> 1600, 64-bit" on Windows 8
>> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as
>> expected
>
> I don't think this is a bug -- just peculiarities of floating point math.

To be more clear: the problem is neither with pow() or floor().  The issue is:

postgres=# SELECT (4.725 * 100.0 + 0.5 ) = 473;
 ?column?
──────────
 t
(1 row)

Time: 0.387 ms
postgres=# SELECT (4.725 * 100.0::FLOAT8 + 0.5 ) = 473;
 ?column?
──────────
 f

The workaround is to use fixed point or build in epsilon tolerances in
any case where you are using decimal numbers and expect precise
results.

merlin


Re: Unexpected result using floor() function

From
Pujol Mathieu
Date:

Le 14/03/2016 15:29, Merlin Moncure a écrit :
> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
> <mathieu.pujol@realfusio.com> wrote:
>> Hi
>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build
>> 1600, 64-bit" on Windows 8
>> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as
>> expected
> I don't think this is a bug -- just peculiarities of floating point math.
>
> merlin
>
>
I think also that it is a float precision issue but the weird thing is
that both calls without floor return 273. Maybe the display method make
a rounding ?

SELECT 4.725 * power(10, 2)::numeric + 0.5) => 273.000
SELECT (4.725 * power(10, 2) + 0.5) => 273





Re: Unexpected result using floor() function

From
Adrian Klaver
Date:
On 03/14/2016 09:54 AM, Pujol Mathieu wrote:
>
>
> Le 14/03/2016 15:29, Merlin Moncure a écrit :
>> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
>> <mathieu.pujol@realfusio.com> wrote:
>>> Hi
>>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++
>>> build
>>> 1600, 64-bit" on Windows 8
>>> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5)
>>> work as
>>> expected
>> I don't think this is a bug -- just peculiarities of floating point math.
>>
>> merlin
>>
>>
> I think also that it is a float precision issue but the weird thing is
> that both calls without floor return 273. Maybe the display method make
> a rounding ?
>
> SELECT 4.725 * power(10, 2)::numeric + 0.5) => 273.000
> SELECT (4.725 * power(10, 2) + 0.5) => 273

First I would say the results you are seeing are 473.000 and 473.

There are two version of power():

http://www.postgresql.org/docs/9.4/interactive/functions-math.html

The version you are using returns a float. In your first example you
turn that into a numeric and the overall output becomes numeric, hence
the trailing 0's. In your second example you leave it as float and the
output is rounded to 473. The 473.000 is not equal to the 473. To borrow
from Merlins example:

test=> SELECT 4.725 * power(10, 2)::numeric + 0.5 =473;
  ?column?
----------
  t
(1 row)

test=> SELECT (4.725 * power(10, 2) + 0.5) = 473;
  ?column?
----------
  f
(1 row)

>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Unexpected result using floor() function

From
"Frank Millman"
Date:
> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
>
> Please can someone explain the anomaly.
 
Thanks for all the responses.
 
Plenty of workarounds. I can carry on now :-)
 
Frank
 

Re: Unexpected result using floor() function

From
Merlin Moncure
Date:
On Mon, Mar 14, 2016 at 11:54 AM, Pujol Mathieu
<mathieu.pujol@realfusio.com> wrote:
> Le 14/03/2016 15:29, Merlin Moncure a écrit :
>>
>> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
>> <mathieu.pujol@realfusio.com> wrote:
>>>
>>> Hi
>>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++
>>> build
>>> 1600, 64-bit" on Windows 8
>>> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work
>>> as
>>> expected
>>
>> I don't think this is a bug -- just peculiarities of floating point math.
>>
>> merlin
>>
>>
> I think also that it is a float precision issue but the weird thing is that
> both calls without floor return 273. Maybe the display method make a
> rounding ?

Yeah.  One of the trickiest bits about floating point numbers with
postgres (as well as many other implementations) is that the textual
representation does not necessarily equate to the internal one.  This
can lead to some pretty weird situations.  For example, data that was
valid when dumped could fail to restore on duplicate key.  Anyone
doing work with floating point should be aware of this: it's compact
and fast but poor at representing precise numbers.

merlin


Re: Unexpected result using floor() function

From
Chris Mair
Date:
Hi,

maybe this is a late reply, but also note that 4.725 alone already cannot be
represented in floating point exactly (and this has nothing to do with Postgres).

Just sum it up 100 times to "see" the round off error becoming visible:

chris=# select sum(4.725::double precision) from generate_series(1,100);
        sum
------------------
  472.500000000001
(1 row)

vs.

chris=# select sum(4.725::numeric) from generate_series(1,100);
    sum
---------
  472.500
(1 row)

Bye,
Chris.




Re: Unexpected result using floor() function

From
"Frank Millman"
Date:
 
> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
>
> Please can someone explain the anomaly.
 
I think I have a solution to my problem, but I would appreciate a review in case I have missed some corner cases.
 
I understand it better now. Here are some of the things I have learned.
 
1. In Python, 4.725 is assumed to be a float. You need some extra steps to turn it into a Decimal type. PostgreSQL seems to take the opposite approach – it is assumed to be numeric, unless you explicitly cast it to a float.
 
2. As pointed out, there are two forms of the power function.
 
test=> select pg_typeof(power(10, 2));
    pg_typeof
------------------
double precision
 
test=> select pg_typeof(power(10., 2));
pg_typeof
----------
numeric
 
I found that adding a decimal point after the 10 is the easiest way to force it to return a numeric.
 
Putting this together, my solution is -
 
test=> select floor(4.725 * power(10., 2) + 0.5);
floor
-------
   473
 
Can anyone see any problems with this?
 
Thanks
 
Frank
 

Re: Unexpected result using floor() function

From
Francisco Olarte
Date:
Hi Frank:

On Tue, Mar 15, 2016 at 6:57 AM, Frank Millman <frank@chagford.com> wrote:
> 2. As pointed out, there are two forms of the power function.
>
> test=> select pg_typeof(power(10, 2));
>     pg_typeof
> ------------------
> double precision
>
> test=> select pg_typeof(power(10., 2));
> pg_typeof
> ----------
> numeric
>
> I found that adding a decimal point after the 10 is the easiest way to force
> it to return a numeric.
>
> Putting this together, my solution is -
>
> test=> select floor(4.725 * power(10., 2) + 0.5);
> floor
> -------
>    473

> Can anyone see any problems with this?

I see a problem in it relying in interpretation of constants. From my
experience I would recommend explicit casts, it's just a second longer
to type but much clearer. The problems start with 10 being interpreted
as integer, all the other ones as numeric:

s=> select pg_typeof(10.), pg_typeof(10), pg_typeof(10.E0), pg_typeof(10E0);
 pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+-----------
 numeric   | integer   | numeric   | numeric
(1 row)

This may byte you any day, so I wuld recommend doing

s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10.
as numeric), 2) + 0.5)) as aux(v);
  v  | pg_typeof
-----+-----------
 473 | numeric
(1 row)
s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10 as
numeric), 2) + 0.5)) as aux(v);
  v  | pg_typeof
-----+-----------
 473 | numeric
(1 row)

which makes your intention clear.

Francisco Olarte.


Re: Unexpected result using floor() function

From
"Frank Millman"
Date:

On Tue, Mar 15, 2016 at 12:02 PM, Francisco Olarte wrote:
 
> Hi Frank:
>
> This may byte you any day, so I wuld recommend doing
>
> s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10 as
> numeric), 2) + 0.5)) as aux(v);
>   v  | pg_typeof
> -----+-----------
>  473 | numeric
> (1 row)
>
> which makes your intention clear.

Good advice. Thank you, F
rancisco
 
Frank