Re: Unexpected result using floor() function

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(dot)pujol(at)realfusio(dot)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(dot)klaver(at)aklaver(dot)com