On 8/29/19 3:14 PM, 'Alan McDonald' alan@... [firebird-support]
wrote:

>  
>
> On 8/29/19 4:45 AM, m.djorov@... [firebird-support] wrote:
> >
> > In binary representation of the values maybe 4.72 is the closest, but
> > we are talking about mathematic.
> > This is from the description of the ROUND function in the documentation:
> > Rounds a number to the nearest integer. If the fractional part is
> > exactly 0.5, rounding is upward for positive numbers and downward for
> > negative numbers.
> > So .725 is all cases is rounded up to .73 as it should be, but in one
> > case it is not.
> >
> The issue is that when you write: cast(2.725 as double precision) you
> aren't dealing with 'mathematics' anymore, or even the value 2.725
>
> The value that you get at that point will be the nearest value to
> 2.725 that is expressible as a double precision floating point number
> which will be slightly different since 2.725 is NOT exactly
> representable as double precision floating point number. The number
> you get is allowed to be either the representable value just below or
> just above, the value, though the preference is the closer one. If the
> number you get is something like 2.72499...xx then round needs to
> round down, if you get something like 2.72500..xx then round needs to
> round up.
>
> If the numbers you had WERE exactly representable, like 2.625 (21/8)
> then the rules on how to round would matter, but since the number you
> have, after being made representable, isn't exactly 0.5 in the
> fractional part, that clause doesn't apply.
>
> --
> Richard Damon
>
> What about:
> select
> round(cast(0.725 as double precision), 2),
> round(cast(1.725 as double precision), 2),
> round(cast(2.725 as double precision), 2),
> round(cast(3.725 as double precision), 2),
> round(cast(4.725 as numeric(18,3)), 2),
> round(cast(5.725 as double precision), 2),
> round(cast(6.725 as double precision), 2),
> round(cast(7.725 as double precision), 2),
> round(cast(8.725 as double precision), 2),
> round(cast(9.725 as double precision), 2),
> round(cast(10.725 as double precision), 2) from
> rdb$database
>

Since numeric is defined by internally scaling up by the power of 10
specified, it should work. (I would probably try to be consistent and
make ALL of them use numeric.)

One question is to make sure that round understands numeric types (I
would think it would).

numeric(18, 3) will use a double for its internal representation, but I
would think it should still work. It might make more sense to use a
width of 9 or less, unless elsewhere you are actually using bigger numbers.

Note that unless you started with numeric, if the value was actually
stored in the database or somehow computed, one danger with this method
is that (assuming casting to numeric rounds) this will cause a double
rounding causing values above x.xx45 to round up.

--
Richard Damon