ROUND funtion misbehaves (sometimes)

Wim · #1 (**permalink**) 02-13-08, 09:03

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.

stolze · #2 (**permalink**) 02-13-08, 09:50

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.

Wim · #3 (**permalink**) 02-14-08, 04:55

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 ?

stolze · #4 (**permalink**) 02-14-08, 11:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech