|
Problem IC71506
|
Status: Geschlossen |
COALESCE WITH CHAR ARGUMENTS SHOULD RETURN VARCHAR IN VARCHAR2 COMPATIBILITY MODE |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
With VARCHAR2 compatibility enabled, CHAR arguments to the
COALESCE function should be implicitly cast to VARCHAR.
Consequently, if all arguments are CHAR, then the result
should be VARCHAR. Because of this, unexpected results may
be returned, for example:
create table T ( c1 char(3), c2 char(10) );
insert into T values ( 'abc', 'defghijklm' );
describe select coalesce( c1, c2 ) || ']' from T;
Column Information
Number of columns: 1
SQL type Type length ...
-------------------- ----------- ...
453 CHARACTER 11 ...
select coalesce( c1, c2 ) || ']' from T;
1
-----------
abc ]
1 record(s) selected.
This should instead return:
Column Information
Number of columns: 1
SQL type Type length ...
-------------------- ----------- ...
449 VARCHAR 11 ...
select coalesce( c1, c2 ) || ']' from T;
1
-----------
abc]
1 record(s) selected.
This also applies to the COALESCE synonyms VALUE and NVL as
well as LEAST, GREATEST, MIN (scalar) and MAX (scalar). |
| Problem-Zusammenfassung: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* With VARCHAR2 compatibility enabled, CHAR arguments to the *
* COALESCE function should be implicitly cast to VARCHAR. *
* Consequently, if all arguments are CHAR, then the result *
* should be VARCHAR. Because of this, unexpected results may *
* be returned, for example: *
* *
* create table T ( c1 char(3), c2 char(10) ); *
* insert into T values ( 'abc', 'defghijklm' ); *
* describe select coalesce( c1, c2 ) || ']' from T; *
* *
* Column Information *
* *
* Number of columns: 1 *
* *
* SQL type Type length ... *
* -------------------- ----------- ... *
* 453 CHARACTER 11 ... *
* *
* select coalesce( c1, c2 ) || ']' from T; *
* *
* 1 *
* ----------- *
* abc ] *
* *
* 1 record(s) selected. *
* *
* *
* This should instead return: *
* *
* Column Information *
* *
* Number of columns: 1 *
* *
* SQL type Type length ... *
* -------------------- ----------- ... *
* 449 VARCHAR 11 ... *
* *
* *
* select coalesce( c1, c2 ) || ']' from T; *
* *
* 1 *
* ----------- *
* abc] *
* *
* 1 record(s) selected. *
* *
* *
* This also applies to the COALESCE synonyms VALUE and NVL as *
* well as LEAST, GREATEST, MIN (scalar) and MAX (scalar). *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 Fix Pack 4 *
**************************************************************** |
| Local-Fix: |
Cast any one of the CHAR arguments to a VARCHAR |
| verfügbare FixPacks: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Lösung |
First fixed in DB2 version 9.7 Fix Pack 4 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Bug-Verfolgung |
Vorgänger : APAR is sysrouted TO one or more of the following: IC73363
Nachfolger : |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 27.09.2010
03.05.2011
03.05.2011 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP4
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.4
|
 |
