DB2 FOR LUW / DB2FORLUW / B10 - DB2

DB2 query may produce SQL0901N -"column number out of range"
error, when the following conditions are met:
1) The query has Outer Join
2) Null producing side of Outer Join has Group By
3) There is IN predicate on column from Tuple preserving side.

Example:
drop table t1;
create table t1(c1 int not null primary key);
insert into t1 values(1),(2),(3);
drop table t2;
create table t2(c1 int not null, c2 int not null);

select * from t1 left outer join (select sum(c2), c2 from t2
group by c2)t on t1.c1=t.c2 and t1.c1 in (1,2);

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
****************************************************************

1) Rewrite the query to convert the IN predicate into OR or NOT
IN predicate into AND
2) Use db2set DB2COMPOPT=NO_GBPU (instance recycle is needed)
    or use optguideline:

/* 




*/

not known / see Local fix

forerunner  : IT22197 
follow-up : 

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

30.08.2017
28.01.2020
28.01.2020