Обсуждение: Unexpected result using floor() function

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

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

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

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

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

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

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

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.

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.