|
Problem IC70000
|
Status: Closed |
INCORRECTLY BLANK PADDED OR CASE INSENSITIVE RESULTS ARE RETURNE DWHEN COMPARING COLUMNS OF CERTAIN DATATYPES |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
The problem occurs when performing string comparisons between
two columns and referencing any one or
both of these columns in the output column list. Column
can be of datatypes char, varchar, graphic, vargraphic and any
user defined
types.
eg. To demonstrate the incorrect results:
1. Blank padded results
CREATE TABLE EMPLOYEE ("PER_ID1" CHAR(12) NOT NULL );
CREATE TABLE PERSON ("PER_ID2" CHAR(12) NOT NULL );
insert into employee values '1117592';
insert into person values '1117592 ';
SELECT EMPLOYEE.PER_ID1 || '$' AS "EMPLOYEE.PER_ID1" ,
PERSON.PER_ID2 || '$' AS "PERSON.PER_ID2"
FROM EMPLOYEE
LEFT OUTER JOIN PERSON
ON EMPLOYEE.PER_ID1 = PERSON.PER_ID2
WHERE PERSON.PER_ID2 = '1117592' ;
EMPLOYEE.PER_ID1 PERSON.PER_ID2
---------------- --------------
1117592$ 1117592$
1 record(s) selected.
Note that the second output column PERSON.PER_ID2 should be
'1117592 $' (with a blank character before $.)
2. Case insensitive results
create db testdb COLLATE USING UCA500R1_LEN_S2;
create table tab1(i varchar(5));
create table tab2(j varchar(5));
insert into tab1 values('case'),('CASE');
insert into tab2 values('case'),('CASE');
SELECT i, j FROM (
SELECT ROW_NUMBER() OVER() AS RN, i, j FROM
tab1,tab2
WHERE i = j )
where RN != 0;
I J
----- -----
case case
CASE CASE
case case
CASE CASE
Expected results are:
I J
----- -----
case case
CASE case
case CASE
CASE CASE |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See problem description *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to DB2 v9.5 fix pack 7 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
This problem was first fixed in DB2 v9.5 fix pack 7 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC71916
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.07.2010
14.10.2010
14.10.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
