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