DB2 FOR LUW / DB2FORLUW / 970 - DB2

An example that can hit the problem: 
-- ddl 
create table dim (pk int not null primary key, uk int); 
create unique index dimuk on dim(uk); 
create table fact (fk int, uk varchar(4)); 
create unique index factuk on fact(uk); 
ALTER TABLE fact ADD CONSTRAINT ri FOREIGN KEY (fk) REFERENCES 
dim (pk); 
 
-- query 
select fact.uk from fact, dim where fk=pk and fact.uk = 'abc' 
order by dim.uk; 
 
Necessary conditions: 
1. There is primary key table and foreign key table. Query has 
join predicate between primary key and foreign key. 
2. Primary key table has unique key, and the unique key is 
present in query ORDER BY clause. 
3. Foreign key table also has unique key. The unique key column 
type is varchar or vargraphic, and it is involved in a local 
equality predicate. 
4. Query select list has no column from primary key table.

An example that can hit the problem: 
-- ddl 
create table dim (pk int not null primary key, uk int); 
create unique index dimuk on dim(uk); 
create table fact (fk int, uk varchar(4)); 
create unique index factuk on fact(uk); 
ALTER TABLE fact ADD CONSTRAINT ri FOREIGN KEY (fk) REFERENCES 
dim (pk); 
 
-- query 
select fact.uk from fact, dim where fk=pk and fact.uk = 'abc' 
order by dim.uk; 
 
Necessary conditions: 
1. There is primary key table and foreign key table. Query has 
join predicate between primary key and foreign key. 
2. Primary key table has unique key, and the unique key is 
present in query ORDER BY clause. 
3. Foreign key table also has unique key. The unique key column 
type is varchar or vargraphic, and it is involved in a local 
equality predicate. 
4. Query select list has no column from primary key table.

Remove order by clause, because query returns at most one row.

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
 

First fixed in DB2 Version 9.7 Fixpack 5.

Remove order by clause, because query returns at most one row.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.01.2011
07.12.2011
07.12.2011

9.7.FP5