Function trunc() behaves in unexpected manner with different data types

From: "Nathan M(dot) Davalos" <n(dot)davalos(at)sharedmarketing(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Function trunc() behaves in unexpected manner with different data types
Date: 2011-02-24 19:01:21
Message-ID: 2701CF596B80DC44815FDBFFF5881A1E0104BB63@exchange01.sharedmarketing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I ran into something interesting with using trunc() and different data
types:

The following is a simplified from the statement we're using and
produces the same results:

select trunc( ((cast(2183.68 as numeric) - cast(1 as numeric)) )*100)
/100

yields 2184.68

select trunc(((cast(2183.68 as numeric) - cast(1 as double precision)
))*100) /100

yields 2184.67

select trunc(cast(2184.68 as double precision) *100)

yields 218467 instead of 218468

This only happens on certain ranges of numbers. Doing the same thing
with the number 3183.68 yields the same result in both cases. It only
appears to happen when a number is declared as a double and there is no
number past the last significant digit or the number past the last
significant digit is a zero AND falls within a certain range of numbers.
For instance select trunc(cast(2184.681 as double precision) *100)
yields 218468, but select trunc(cast(2184.680 as double precision) *100)
yields 218467.

I already made sure everything we're using is just defined as numeric to
avoid the issue.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Merlin Moncure 2011-02-24 19:31:22 Re: Function trunc() behaves in unexpected manner with different data types
Previous Message Ross Barrett 2011-02-24 16:36:34 BUG #5899: Memory corruption when running psql