Unexpected result using floor() function

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

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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, Francisco
Frank