Re: [firebird-support] Re: Is this a bug in ROUND function?

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2019-08-30 04:33, Richard Damon rich...@damon-family.org 
[firebird-support] wrote:
> On 8/29/19 3:14 PM, 'Alan McDonald' a...@meta.com.au [firebird-support]
> wrote:
> 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.

NUMERIC(18,3) uses BIGINT for its internal representation in dialect 3. 
Only in dialect 1 does it use DOUBLE PRECISION, and dialect 1 was 
deprecated 20 years ago.

Mark

[firebird-support] Re: Is this a bug in ROUND function?

[pre...@technisoft-online.com [firebird-support]]

Maybe the 'problem' is not in the ROUND() function but the CAST() ?
 

 Peter
 Technisoft
 

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 3:14 PM, 'Alan McDonald' a...@meta.com.au [firebird-support]
wrote:
>  
>
> On 8/29/19 4:45 AM, m.djo...@gmail.com [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

RE: [firebird-support] Re: Is this a bug in ROUND function?

['Alan McDonald' a...@meta.com.au [firebird-support]]

On 8/29/19 4:45 AM, m.djo...@gmail.com [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

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 4:45 AM, m.djo...@gmail.com [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

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Slavomir Skopalik skopa...@elektlabs.cz [firebird-support]]

For exact numbers you have to use exact numeric types.

Double is float point type with all advantages and disadvantages.

Look here 
https://en.wikipedia.org/wiki/Double-precision_floating-point_format


Just for fun, try this:

1e20 + 1 + 1 - 1e20 + 1

Slavek

Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
MASA - Collection and evaluation of data from machines and laboratories
http://eng.elektlabs.cz/products-and-services/masa
-
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:skopa...@elektlabs.cz
http://www.elektlabs.cz

On 29.08.19 13:45, m.djo...@gmail.com [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.



Posted by: m.djo...@gmail.com

Reply via web post 
 
	• 	Reply to sender 
 
	• 	Reply to group 
 
	• 	Start a New Topic 
 
	• 	Messages in this topic 
 
(3)


++

[firebird-support] Re: Is this a bug in ROUND function?

[m.djo...@gmail.com [firebird-support]]

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.