DB2 - Problembeschreibung
Problem IC83446 | Status: Geschlossen |
INCORRECT RESULTS USING VARCHAR_FORMAT (TO_CHAR) TO CONVERT NUMERIC VALUES TO FORMATTED STRINGS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 | |
Problembeschreibung: | |
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. | |
Problem-Zusammenfassung: | |
**************************************************************** * 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 * **************************************************************** | |
Local-Fix: | |
Lösung | |
Install this fix | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 14.05.2012 13.06.2012 13.06.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version |