# Thread: ROUND funtion misbehaves (sometimes)

1. Registered User
Join Date
Nov 2004
Posts
1,428

## Unanswered: ROUND funtion misbehaves (sometimes)

We have found an anomaly using the ROUND function. While developing, when we made a certain calculation in a SELECT we got the right results. If we later wrote the UPDATE statement, using that very same calculation, we found different results written in the table:

We were making calculations based on the number of days 1 or more persons did a certain activity. We divided those days by the number of days in that year to determine yearly activity: less than half the days present in a year = 0, equal or more than half the days in a year = 1, .... For certain numbers of days of activity, we found 0 written in the table, when it should have been 1.

SQL-instructions needed to reproduce the bug:
Code:
```-- make a function to generate a test data set
-- kudos to Greame Birchall's DB2 Cookbook
create function NumList(max_num INTEGER)
RETURNS TABLE (num Integer)
LANGUAGE SQL
RETURN
WITH temp1 (num) AS
(VALUES (0)
UNION ALL
SELECT num + 1
FROM temp1
WHERE num < max_num
)
SELECT num
FROM temp1
;

-- create a test table
drop table test;

CREATE TABLE test (
num         INTEGER     NOT NULL,
res_365     DEC(7)      NOT NULL,
res_365_DEC DEC(7)      NOT NULL,
res_366     DEC(7)      NOT NULL,
res_366_DEC DEC(7)      NOT NULL,
constraint pk_test primary key (num)
);```
Code:
```-- populate the test table with a sequence from 0 to 500
DELETE FROM test;

INSERT INTO test
SELECT T.num,
ROUND(T.num / double(365), 0),
CAST(ROUND(T.num / double(365), 0) as DEC(7)),
ROUND(T.num / double(366), 0),
CAST(ROUND(T.num / double(366), 0) as DEC(7))
from TABLE(NumList(500)) as T
;

-- Compare the data in the table with at runtime calculated values.
-- The "round(T.num / double(365), 0)" generates the correct results,
-- the field "res_365" that was filled using that very same calculation
-- differs from time to time.
-- Because the result is written in a DEC(7) field, I did an explicit CAST
-- to a DECIMAL(7) (the XX_DEC fields) but even then the values differ.
-- They start do differ (from time to time) from num = 186 until num =
-- (days of year  1).
select T.num,
res_365,
res_365_DEC,
round(T.num / double(365), 0) as "365",
res_366,
res_366_DEC,
round(T.num / double(366), 0) as "366"
from test as T
order by 1
;```
You can run this code to see if your system is affected too. I'd love to hear from the DB2 V9.x guys if this flaw is also present in that version.

P.S. I have reported this to IBM.

2. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
I run this on DB2 LUW V9.5 64bit on an openSUSE system and I don't see any differences that you mentioned in the query result, i.e. if I have a "1" from the table, I also have a 1.0E0 from the calculation. But maybe I'm not looking at the right stuff? What do you see that is not correct?

What does not make your comparison 100% fair, however, is that the table holds DEC(7) while the ROUND returns a floating point number. Do you still see the mismatches when you change you query to this:
Code:
```select T.num,
res_365,
CAST(round(T.num / double(365), 0) AS DEC(7)) as "365",
res_366,
CAST(round(T.num / double(366), 0) AS DEC(7)) as "366"
from test as T
order by 1```
This is just the representation of the result and not relevant for the specific calculation, however.

3. Registered User
Join Date
Nov 2004
Posts
1,428
Stolze,
This is an excerpt of the results, using your query. You have pinpointed the underlying reason! There are no longer mismatches, but the results are still wrong .

Any idea how I can get the correct results? From 183 on, the results should all be 1's.
Code:
```num	res_365	365	res_366	366
181	0	0	0	0
182	0	0	0	0
183	1	1	1	1
184	1	1	1	1
185	1	1	1	1
186	0	0	0	0
187	1	1	1	1
188	1	1	1	1
189	1	1	1	1
190	0	0	0	0
191	1	1	1	1
192	1	1	1	1
193	1	1	1	1
194	1	1	0	0
195	0	0	1	1
196	1	1	1	1```
This is an excerpt of the results, using my query, without the explicit DEC(7) CASTing.
Code:
```select T.num,
res_365,
round(T.num / double(365), 0) as "365'",
res_366,
round(T.num / double(366), 0) as "366'"
from test as T
order by 1;```
Code:
```num	res_365	365'	res_366	366'
181	0	0	0	0
182	0	0	0	0
183	1	1	1	1
184	1	1	1	1
185	1	1	1	1
186	0	1	0	1
187	1	1	1	1
188	1	1	1	1
189	1	1	1	1
190	0	1	0	1
191	1	1	1	1
192	1	1	1	1
193	1	1	1	1
194	1	1	0	1
195	0	1	1	1
196	1	1	1	1```
You get other results in DB2 V 9 ?

4. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
I get this result on V9.5:
Code:
```        180        0.   +0.00000000000000E+000        0.   +0.00000000000000E+000
181        0.   +0.00000000000000E+000        0.   +0.00000000000000E+000
182        0.   +0.00000000000000E+000        0.   +0.00000000000000E+000
183        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
184        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
185        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
186        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
187        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
188        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
189        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000
190        1.   +1.00000000000000E+000        1.   +1.00000000000000E+000```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•