Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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 ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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