Thread: 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
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
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 allI 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.ThanksFrank 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:
Hi allI 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.ThanksFrank Millman
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
> 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
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.
> 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.
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
>
> 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