|
Problem IC71506
|
Status: Closed |
COALESCE WITH CHAR ARGUMENTS SHOULD RETURN VARCHAR IN VARCHAR2 COMPATIBILITY MODE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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). |
| Problem Summary: |
****************************************************************
* 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 |
| available fix packs: |
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
|
| Solution |
First fixed in DB2 version 9.7 Fix Pack 4 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC73363
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.09.2010
03.05.2011
03.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
