Incorrect results can be obtained for several VARCHAR_FORMAT
usage scenarios. Examples of these scenarios are as follows:
1. Numeric input between -1 and 1 is formatted incorrectly for
several scenarios:
VALUES VARCHAR_FORMAT (DECFLOAT (0.123),'.999');
Current result : '#####'
Expected result: ' .123'
VALUES VARCHAR_FORMAT (DECFLOAT (-0.1), '.99');
Current result : '####'
Expected result: '-.10'
VALUES VARCHAR_FORMAT (DECFLOAT (0.123), '9.999');
Current result : ' 0.123'
Expected result: ' .123'
2. Extra '#' in output, when explicit sign tokens such as 'S',
'MI' or 'PR' are included in the format string, and the input
cannot be formatted due to overflow.
Examples:
VALUES VARCHAR_FORMAT(-321, 'S99');
Current result : '####'
Expected result: '###'
VALUES VARCHAR_FORMAT(-21, 'S99');
Result : '-21' -- Correct result and notice that length is 3
bytes.
VALUES VARCHAR_FORMAT(-321, '99PR');
Current result : '#####'
Expected result: '####'
VALUES VARCHAR_FORMAT(-321,'99MI');
Current result : '####'
Expected result : '###'
VALUES VARCHAR_FORMAT(1, 'S');
Current result : '##'
Expected result: '#'
3. Special values such as positive and negative Infinity, NaN
and sNaN are returned differently when VARCHAR_FORMAT is used
with and without a format string specified. For support across
DB2 family of products, these values should be capitalized (i.e.
behave like the NO_FORMAT case below).
SELECT VARCHAR(VARCHAR_FORMAT(c1),15) AS NO_FORMAT,
VARCHAR(VARCHAR_FORMAT(c1, '99'),15) AS WITH_FORMAT FROM (VALUES
nan,-nan,snan,-snan,infinity,-infinity) AS X(c1)
NO_FORMAT WITH_FORMAT
--------------- ---------------
NAN NaN
-NAN -NaN
SNAN sNaN
-SNAN -sNaN
INFINITY Infinity
-INFINITY -Infinity
6 record(s) selected. |
****************************************************************
* USERS AFFECTED: *
* VARCHAR_FORMAT (TO_CHAR) users *
****************************************************************
* PROBLEM DESCRIPTION: *
* Incorrect results can be obtained for several VARCHAR_FORMAT *
* usage scenarios. Examples of these scenarios are as follows: *
* *
* 1. Numeric input between -1 and 1 is formatted incorrectly *
* for *
* several scenarios: *
* *
* VALUES VARCHAR_FORMAT (DECFLOAT (0.123),'.999'); *
* Current result : '#####' *
* Expected result: ' .123' *
* VALUES VARCHAR_FORMAT (DECFLOAT (-0.1), '.99'); *
* Current result : '####' *
* Expected result: '-.10' *
* VALUES VARCHAR_FORMAT (DECFLOAT (0.123), '9.999'); *
* Current result : ' 0.123' *
* Expected result: ' .123' *
* *
* 2. Extra '#' in output, when explicit sign tokens such as *
* 'S', *
* 'MI' or 'PR' are included in the format string, and the *
* input *
* cannot be formatted due to overflow. *
* *
* Examples: *
* VALUES VARCHAR_FORMAT(-321, 'S99'); *
* Current result : '####' *
* Expected result: '###' *
* VALUES VARCHAR_FORMAT(-21, 'S99'); *
* Result : '-21' -- Correct result and notice that length is *
* 3 *
* bytes. *
* VALUES VARCHAR_FORMAT(-321, '99PR'); *
* Current result : '#####' *
* Expected result: '####' *
* VALUES VARCHAR_FORMAT(-321,'99MI'); *
* Current result : '####' *
* Expected result : '###' *
* VALUES VARCHAR_FORMAT(1, 'S'); *
* Current result : '##' *
* Expected result: '#' *
* *
* 3. Special values such as positive and negative Infinity, *
* NaN *
* and sNaN are returned differently when VARCHAR_FORMAT is *
* used *
* with and without a format string specified. For support *
* across *
* DB2 family of products, these values should be capitalized *
* (i.e. *
* behave like the NO_FORMAT case below). *
* *
* SELECT VARCHAR(VARCHAR_FORMAT(c1),15) AS NO_FORMAT, *
* VARCHAR(VARCHAR_FORMAT(c1, '99'),15) AS WITH_FORMAT FROM *
* (VALUES *
* nan,-nan,snan,-snan,infinity,-infinity) AS X(c1) *
* *
* NO_FORMAT WITH_FORMAT *
* --------------- --------------- *
* NAN NaN *
* -NAN -NaN *
* SNAN sNaN *
* -SNAN -sNaN *
* INFINITY Infinity *
* -INFINITY -Infinity *
* *
* 6 record(s) selected. ?edit *
* Error Description None *
****************************************************************
* RECOMMENDATION: *
* Install this fix *
**************************************************************** |
