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
>>  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